Is there any way using generic sql to insert a string longer than 4000 chars into a CLOB? I can't use PLSQL it has to be generic sql using a quoted string. But I can do any specific Oracle code before loading the file containing the sql.
<Oracle specific code>
insert into test (col1) values ('<string longer than 4000 chars');
You can't do it with a literal as part of the sql statement.
You'll need to use a bind variable instead, and send the data in chunks, the same as you would if it were LONG datatype, and that code is not specific to Oracle.
Hope it helps,
I need to do the insert using sql script (no bind parameters). The same sql script is loaded by the app for Oracle and many other databases and the script files have multiple/many sql statements that populate the database. I only encountered the sql literal limit of 4000 chars in the case of Oracle.
If you are using ODBC API you don't need to do anything special for long data just bind parameter as usual and the ODBC driver will update the database whether the data length is 4 chars or 2 GB long (you would need to bind your parameter appropriately for long data, ie SQL_LONGVARCHAR instead of SQL_VARCHAR, etc).
If you want to retrieve/send data in chunks you would use SQLGetData and SQLPutData. Usually only SQLGetData is used with long data, since you probably have all the long data in one block of memory you don't need to use SQLPutData rather just bind a parameter to the memory location as usual with bind parameters.
The issue I am having is that my data is static loaded from a file as sql statements so using bind parameters is not an option in this case.
OK I found a workaround that you can use to do the insert using generic SQL (no PL/SQL is required and it works for all databases).
1 Do the insert as an insert with first part is the sql literal up to 4000 chars
2 Do the additional parts as an update concatenating the previous parts with the next part where the next part is up to 4000 chars
3 Repeat step 2 until all the large sql literal is updated.
test_large_literal (col1, col2)
(<key val>, <first part of large sql literal);
col2 = col2 || <second part large sql literal>
col1 = <key val>;
col2 = col2 || <last part large sql literal>
col1 = <key val>;
or if you know it is Oracle you can use
insert into .. values (to_clob('...') || to_clob('...')..)
Edited by: Farid_Z on Nov 10, 2011 11:04 AM
Searching the Web for this issue I found your workaround. Let me propose a far simpler solution with no need for temporary tables. Find the full script example at this post: http://blog.ozzie.eu/2011/11/ora-01704-string-literal-too-long.html