DevOps, CI/CD and Automation

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,
Greg

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.

I'm having the same problem as Farid. Are there any resources online for how to send data in chunks using bind variables and the ODBC C API?

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.

Farid, thanks very much for your help: it turned out that I was using SQL_VARBINARY instead of SQL_LONGVARCHAR. Switching it did the trick.

Best of luck getting your own issue resolved.

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.

Example,

Insert into
test_large_literal (col1, col2)
values
(<key val>, <first part of large sql literal);

update
test_large_literal
set
col2 = col2 || <second part large sql literal>
where
col1 = <key val>;
...
...
update
test_large_literal
set
col2 = col2 || <last part large sql literal>
where
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