Ora 01461 ошибка

I have a solution for Java/JPA/eclipselink/oracle when insert a long xml string (>4000) into a XMLTYPE column at Insert XML with more than 4000 characters into a Oracle XMLTYPE column. For clarity, include the same contents here in case the link not working

You need to convert xml string for more than 4000 charcaters into SQLXML type first.

Environment: jpa 2.1.0, eclipselink 2.5.2, oracle db 11gr2

SQL:

CREATE TABLE "XMLTEST"
( "ID" NUMBER(10,0) NOT NULL ENABLE, 
  "DESCRIPTION" VARCHAR2(50 CHAR) NOT NULL ENABLE, 
  "XML_TXT" "XMLTYPE" NOT NULL ENABLE
);

INSERT INTO XMLTEST (ID, DESCRIPTION, XML_TXT) VALUES (101, 'XML DATA', '<data>TEST</data>');
COMMIT;

DROP TABLE "XMLTEST";

Java Code

String sql = "INSERT INTO XMLTEST (ID, DESCRIPTION, XML_TXT) VALUES (?, ?, ?)";
String xmlDataStr = "<data>test...</data>"; // a long xml string with length > 4000 characters
Connection con = getEntityManager().unwrap(Connection.class);
SQLXML sqlXml = con.createSQLXML();
sqlXml.setString(xmlDataStr);

Java code — use PreparedStatement

PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setLong(1, 201);
pstmt.setLong(2, "Long XML Data");
pstmt.setSQLXML(3, sqlXml);
pstmt.execute();

Java code — use native query instead of PreparedStatement

Query query = getEntityManager().createNativeQuery(sql);
query.setParameter(1, 301);
query.setParameter(2, "Long XML Data");
query.setParameter(3, sqlXml);
query.executeUpdate();

Источник

I have a solution for Java/JPA/eclipselink/oracle when insert a long xml string (>4000) into a XMLTYPE column at Insert XML with more than 4000 characters into a Oracle XMLTYPE column. For clarity, include the same contents here in case the link not working

You need to convert xml string for more than 4000 charcaters into SQLXML type first.

Environment: jpa 2.1.0, eclipselink 2.5.2, oracle db 11gr2

SQL:

CREATE TABLE "XMLTEST"
( "ID" NUMBER(10,0) NOT NULL ENABLE, 
  "DESCRIPTION" VARCHAR2(50 CHAR) NOT NULL ENABLE, 
  "XML_TXT" "XMLTYPE" NOT NULL ENABLE
);

INSERT INTO XMLTEST (ID, DESCRIPTION, XML_TXT) VALUES (101, 'XML DATA', '<data>TEST</data>');
COMMIT;

DROP TABLE "XMLTEST";

Java Code

String sql = "INSERT INTO XMLTEST (ID, DESCRIPTION, XML_TXT) VALUES (?, ?, ?)";
String xmlDataStr = "<data>test...</data>"; // a long xml string with length > 4000 characters
Connection con = getEntityManager().unwrap(Connection.class);
SQLXML sqlXml = con.createSQLXML();
sqlXml.setString(xmlDataStr);

Java code — use PreparedStatement

PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setLong(1, 201);
pstmt.setLong(2, "Long XML Data");
pstmt.setSQLXML(3, sqlXml);
pstmt.execute();

Java code — use native query instead of PreparedStatement

Query query = getEntityManager().createNativeQuery(sql);
query.setParameter(1, 301);
query.setParameter(2, "Long XML Data");
query.setParameter(3, sqlXml);
query.executeUpdate();

Источник

Content

Sometimes when inserting data into an Oracle cache, or after switching a Denodo Virtual DataPort database cache to ‘Use data types supporting UTF-8‘ you might receive the following error:

ORA-01461: can bind a LONG value only for insert into a LONG column

This is due to text data being inserted into the Oracle cache that is too long for the LONG field that has been generated for the view in the Oracle cache by the Virtual DataPort Server to support. Usually, these text fields are CLOB, NCLOB, BLOB, or VARCHAR with long character limit data in the underlying data source. By default, Denodo caches text values as VARCHAR(4000), unless the subtype of the field is longer, and some base views, from delimited files for example, may not have a subtype for text fields that reflect the size of all of the entries.

The solution to this problem is to define the subtype of the text field causing the error in the base view it is coming from. This will propagate the changes to all views built on top of that field. The subtype should be set to be a CLOB, NCLOB, BLOB, if that is the type in the underlying source, or simply VARCHAR with the length that it is in the underlying source from the Virtual DataPort Administration Tool.

Here we are changing the subtype of text field job_id to be a VARCHAR with length 8000:

Once you have changed the subtype of the field, you will need to reload the cache for the view that is raising the ORA-01461 error. This is done by opening the view and going to Options > Cache mode: Off saving the view and then changing the cache mode back to the previous configuration and saving the view again to confirm the changes. The table in the Oracle cache database will be automatically recreated with a field matching the subtype which you have just set in the view.

Источник

The ORA-01461 error is a common issue that developers may encounter when working with Oracle databases. This error occurs when you try to insert a value that is too large for a specific column type, such as a LONG column. In this comprehensive guide, we’ll walk you through the process of solving the ORA-01461 error and provide a step-by-step solution to help you bind a long value for inserting into a long column.

Table of Contents

  1. Understanding the ORA-01461 Error
  2. Step-by-Step Solution
  3. FAQs

Understanding the ORA-01461 Error

The ORA-01461 error occurs when you attempt to insert a value that is longer than the maximum allowed size for a column data type. In Oracle, the LONG data type can store variable-length character strings with a maximum length of 2 GB.

Here’s an example of the ORA-01461 error message:

ORA-01461: can bind a LONG value only for insert into a LONG column

This error can be caused by several factors, including:

  1. Binding a value that exceeds the maximum allowed length for a LONG column.
  2. Using an incorrect data type for the target column.
  3. Inserting a value with a data type that is not compatible with the target column.

To resolve the ORA-01461 error, you need to ensure that the value you are trying to insert does not exceed the maximum allowed length, and that you are using the correct data type for the target column.

Step-by-Step Solution

Follow these steps to bind a long value for inserting into a long column and resolve the ORA-01461 error:

Verify the target column data type: Check the data type of the target column in your database to ensure it is a LONG column. You can use the following query to retrieve information about the column:

SELECT column_name, data_type
FROM all_tab_columns
WHERE table_name = 'YOUR_TABLE_NAME' AND column_name = 'YOUR_COLUMN_NAME';

Check the length of the value being inserted: Make sure that the value you want to insert does not exceed the maximum length allowed for a LONG column (2 GB). You can use the LENGTH function in Oracle to determine the length of a value:

SELECT LENGTH(your_column_value) FROM your_table;

Use PL/SQL to insert the value: If you are working with a large value that needs to be inserted into a LONG column, consider using PL/SQL to perform the operation. Here’s an example of how to use PL/SQL to insert a long value into a LONG column:

DECLARE
    long_value LONG;
BEGIN
    long_value := 'your_long_value_here';
    INSERT INTO your_table (long_column) VALUES (long_value);
END;
/
  1. Consider using a different data type: If you consistently encounter the ORA-01461 error when working with large values, consider using a different data type, such as a CLOB (Character Large Object) or BLOB (Binary Large Object), which can store up to 4 GB of data.

FAQs

1. What is the maximum length of a LONG column in Oracle?

The maximum length of a LONG column in Oracle is 2 GB.

2. Can I use a LONG column in a WHERE clause?

Yes, you can use a LONG column in a WHERE clause. However, it is generally not recommended due to performance issues when working with large data sets.

3. What is the difference between a LONG and a CLOB data type?

A LONG data type can store variable-length character strings with a maximum length of 2 GB, while a CLOB (Character Large Object) can store up to 4 GB of data. CLOBs also provide better performance and more flexibility for working with large character data.

4. Can I use the LENGTH function to check the length of a LONG column value?

Yes, you can use the LENGTH function to check the length of a LONG column value. However, keep in mind that the LENGTH function returns the length in characters, not bytes.

5. Can I use a LONG column as a primary key?

No, you cannot use a LONG column as a primary key in Oracle. Instead, you should use a data type that is more suitable for primary key constraints, such as NUMBER or VARCHAR2.

  • Oracle Data Types
  • Oracle PL/SQL Tutorial
  • Working with Large Objects in Oracle
Источник

Problem

Error: ORA-01461: can bind a LONG value only for insert into a LONG column thrown when creating the Reporting Schema

Cause

When an administrator ‘Creates’ or ‘Recreates’ the OpenPages reporting schema, several things occur. On the ‘Create’ command, the ‘RT’ database tables are created and populated with data. On the ‘Recreate’ command, the ‘RT’ database tables are dropped, recreated, and repopulated with data.

In order to perform these database transaction, dynamic SQL is executed. The dynamic SQL can be different from system to system. The dynamic SQL is constructed based on how the object types are related; how many fields are on the object type; and the name of the fields on the object. The combination of these items can cause the length of the dynamic SQL to grow. In order to execute the dynamic SQL for the reporting schema, a PL/SQL package is used. The SQL that gets generated for the RT tables has an upper maximum of 32k characters due to an oracle limitation.

Resolving The Problem

The workaround is to exclude the object type, individual field group, and/or individual field(s) from the reporting schema altogether.

Removing individual fields will shorten the overall length of the SQL that gets generated.

To remove these fields, an administrator can perform the following steps:

    1. Log onto the application server.
    2. Open a command prompt window (or SSH shell).
    3. Log into the database with the OPENPAGES database schema username using a database tool (i.e.: SQL*Plus, clpplus, etc).
    4. Run the following SQL to verify what entries are in the rps_prop_auto_exclude table:
      5. SELECT bundlename, name FROM propertydefs WHERE propertydefid IN (SELECT propertydef_id FROM rps_prop_auto_exclude);
    5. Run the following SQL where you replace <field-group> and <field-name> with the appropriate values:
      6. Syntax:INSERT INTO rps_prop_auto_exclude (PROPERTYDEF_ID)
        SELECT PROPERTYDEFID FROM propertydefs WHERE bundlename = ‘<field-group>’
        AND name = ‘<field-name>’;
      COMMIT;
      Sample:INSERT INTO rps_prop_auto_exclude (PROPERTYDEF_ID)
        SELECT PROPERTYDEFID FROM propertydefs WHERE bundlename = ‘CompanyABC-LossEvent’
        AND name = ‘Is this Loss Event Related to Multiple Issues’;
      COMMIT;
    6. Repeat the INSERT statements for any additional fields.
    7. Once the INSERTS statements have been executed, verify that the rps_prop_auto_exclude has been updated by running the SELECT statement again.
    8. Restart the OpenPages services.

Once the services have been restarted, the OpenPages administrator should try to regenerate the reporting schema.

For OpenPages 6.x environments and higher, the following administrator steps need to also be executed:

    1. Open a supported browser.
    2. Go to the OpenPages URL.
    3. Login with an administrator id.
    4. Using the navigation menubar, go to: Administration -> Object Types.
    5. Select the Object Type that you are having the issue with from the logs.
    6. Scroll down to the ‘Field Exclusions’ area.
    7. Select the ‘Exclude’ button.
    8. Select the property in the ‘Select Field’ section that you want to exclude, and then select «Reporting Framework» in the ‘Select Subsystem’ area.
    9. When you are finished, select the ‘Exclude’ button.
    10. Once the fields have been excluded, the OpenPages administrator should kickoff the framework generation.

When following the above TechNote, OpenPages administrators should note that the excluded fields will no longer be available for reporting. This may impact custom deliverables utilize these fields.

Related Information

[{«Product»:{«code»:»SSFUEU»,»label»:»IBM OpenPages with Watson»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w\/o TPS»},»Component»:»Not Applicable»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»7.0;6.2.1;6.2;6.1;6.0.1;6.0;5.5.3;5.5.2;5.5″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Historical Number

00001101

Источник

Понравилась статья? Поделить с друзьями:
  • Osstatus ошибка 99999
  • Osstatus ошибка 5342
  • Ora 01438 ошибка
  • Ora 01430 ошибка
  • Ora 01041 внутренняя ошибка hostdef расширение не существует