IBM Support

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

Troubleshooting


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.

reporting_schema_error.zip - reporting_schema_error.zip

[{"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

Document Information

Modified date:
15 June 2018

UID

swg21513620

Page Feedback