e.g. if I have a file w/ fields
"a", "b", "c"" def"
our parser should be able to import this correctly.
ADS parser seems to parse everything correctly; on Options tab, if you select 'Preview INSERT statements' from the 'Destination' field before clicking the 'Next' button, then you will see that the INSERT statement is generated correctly.
The exception (included below) is thrown from Oracle which seems to have a length of 4000 chars limitation on a string literal when executing an SQL statement. The last column of the test data (only one row) contains about 4100 characters which seems to be the root cause of the problem. In the test data, the last column contains 21 items, if you remove the 21st item from the test data (about 140 characters) and then try again, import would succeed.
Searched around, it does not seem to have a way to work around this problem if it is executed as an plain SQL statement.
java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1033)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1838)
...
Link below describes a workaround for resolving this problem:
https://forums.oracle.com/forums/thread.jspa?messageID=4332630
Idea is to break the long literal string into pieces.
Assuming original SQL is:
INSERT INTO table(co1_1, col_2, col_3) VALUES(val_1, val_2, val_3)
and
the length of val_3 exceeds 4000
then, break val_3 into pieces, say N pieces, and construct the INSERT statement as:
INSERT INTO table(co1_1, col_2, col_3) VALUES(val_1, val_2, TO_CLOB(val_3_1) || TO_CLOB(val_3_2) || ... || TO_CLOB(val_3_n))
where the length of each piece of val_3_x is less than 4K.
I tried above workaround using our test data by breaking the last column into two pieces as:
TO_COLB('SPECIAL TOOLS...') || TO_CLOB('21. Dispose of...')
and it did work.
Perhaps we can apply this to ADS Import and \sqlimport if all of the followings are true:
(1) request is to import data into database,
(2) database server is Oracle,
(3) column type is CLOB,
(4) length of column value is greater than 4K.
This is the only workaround I found without the need of using a bind variable. Please review this workaround and see if it is acceptable.
Link below describes a workaround for resolving this problem:
https://forums.oracle.com/forums/thread.jspa?messageID=4332630
Idea is to break the long literal string into pieces.
Assuming original SQL is:
INSERT INTO table(co1_1, col_2, col_3) VALUES(val_1, val_2, val_3)
and
the length of val_3 exceeds 4000
then, break val_3 into pieces, say N pieces, and construct the INSERT statement as:
INSERT INTO table(co1_1, col_2, col_3) VALUES(val_1, val_2, TO_CLOB(val_3_1) || TO_CLOB(val_3_2) || ... || TO_CLOB(val_3_n))
where the length of each piece of val_3_x is less than 4K.
I tried above workaround using our test data by breaking the last column into two pieces as:
TO_COLB('SPECIAL TOOLS...') || TO_CLOB('21. Dispose of...')
and it did work.
Perhaps we can apply this to ADS Import and \sqlimport if all of the followings are true:
(1) request is to import data into database,
(2) database server is Oracle,
(3) column type is CLOB,
(4) length of column value is greater than 4K.
This is the only workaround I found without the need of using a bind variable. Please review this workaround and see if it is acceptable.
The requested enhancement is supported as:
SVN r30952 - 12.0 branch
SVN r30954 - 13.0 branch
SVN r30955 - trunk
Note that in the Import Data dialog, the "TO_CLOB('...') || TO_CLOB('...')" conversion is performed as long as the database is Oracle and data type is 'CLOB' and length of value is greater than 4K, the setting of the Destination field does not matter (which can be 'Import into Database' or 'Save INSERT statements into file' or 'Preview INSERT statements').
FS \sqlimport command and Schema Script Generator have been modified as well.
Only tested this fix against Oracle 11g and MySQL 5.0 in the development environment.
The requested enhancement is supported as:
SVN r30952 - 12.0 branch
SVN r30954 - 13.0 branch
SVN r30955 - trunk
Note that in the Import Data dialog, the "TO_CLOB('...') || TO_CLOB('...')" conversion is performed as long as the database is Oracle and data type is 'CLOB' and length of value is greater than 4K, the setting of the Destination field does not matter (which can be 'Import into Database' or 'Save INSERT statements into file' or 'Preview INSERT statements').
FS \sqlimport command and Schema Script Generator have been modified as well.
Only tested this fix against Oracle 11g and MySQL 5.0 in the development environment.
Issue #8179 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build 12.0/r30952, 13.0/r30954, trunk/r30955 |
No time estimate |
ADS parser seems to parse everything correctly; on Options tab, if you select 'Preview INSERT statements' from the 'Destination' field before clicking the 'Next' button, then you will see that the INSERT statement is generated correctly.
The exception (included below) is thrown from Oracle which seems to have a length of 4000 chars limitation on a string literal when executing an SQL statement. The last column of the test data (only one row) contains about 4100 characters which seems to be the root cause of the problem. In the test data, the last column contains 21 items, if you remove the 21st item from the test data (about 140 characters) and then try again, import would succeed.
Searched around, it does not seem to have a way to work around this problem if it is executed as an plain SQL statement.
java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1033)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1838)
...