Inserting values > 4000 characters into CXC_* tables

-
Question Recieved: We are encountering the following Error:
Error encountered when trying to build a particular SQL statement. The output of the error message is as follows:

SQLException in com.oracle.ugbu.cc.run.builder.SqlBuildStatementRow
Message: ORA-01461: can bind a LONG value only for insert into a LONG column

SQLState: 72000
ErrorCode: 1461
Statement causing error:
INSERT INTO CXC_SQL_BUILD_STMT ( CXC_SQL_BUILD_ID ,CXC_STEP_SEQ ,CXC_TOP_DOWN_SEQ ,CXC_STATEMENT_SEQ ,STMT_EXEC_CLASS ,CXC_SQL_TEXT ,ROW_VERSION ) VALUES (?,?,?,?,?,?,?)
Answer: This error occurs if the SQL value being inserted into CXC_SQL_BUILD_STMT exceeds 4000 characters, consider using pre-compiled PL/SQL functions or Temporary staging tables to ensure that individual SQL statements are not overly complex.

Comments

Post a Comment

Popular posts from this blog

Bundle Corrections.

-
We have identified an issue with the use of bundling and UI Maps, whereby the resulting Bundle was wrapping the UI Map in a set of CDATA tags, but only after it had already removed all formatting from the source records, resulting in corruption of the UI Map HTML fragments. This has now been corrected by implementing Single Fix 8228025 on FW/CC&B 2.2.0. If you are using the Bundling subsystem in your implementation, I strongly recommend that you implement this patch.
Read more

Database Upgrades and their impact on Conversion

-
Hint: It appears that the current strategy in relation to application of Single Fixes and Service Packs no longer supports application of these scripts against a staging schema (at least as part of CC&B 2.x.x). As a result it is recommended that you drop and rebuild the Conversion schema from the Production instance once this has been upgraded with the latest patch-sets. We have also noted that application of these single fixes may require a redelivery of all custom java code from the development environment, since the java compilation is not performed as part of the patch install process (unlike the Cobol implementation process).
Read more

Threading...

-
A common question that I get asked is.. how do we force multi-threaded conversion jobs to split the key ranges evenly across each thread instance? Unfortunately I don't have an easy answer. The base conversion jobs seem to take a simplistic approach of using the low and high override values (if supplied, otherwise they use values of 0000000000 and 9999999999) and then divide these by the number of threads to determine the ranges for each instance. But.. We have found that we can force a predefined range of values and simulate threading but having separate batch controls defined for each thread, with predefined high/low values and these can then be run concurrently through standard batch submission processes.. e.g.. if we want to run VAL-ACCT in 4 threads and ensure that a known range of source keys between 0000000001 and 0000100000 is split between the 4 threads, we clone the existing batch control into VAL-ACCT1, with OVRD-LOW-ID = 0000000001 and OVRD-HIGH-ID = 0000025000 V
Read more