Question: I've run into a snag
where I am executing an insert statement that has a particularly
large field requiring some dynamic SQL. I've declared my string
variable being executed as a VARCHAR2(32767). I've done quite a bit
of searching on the web and there sure are a lot of people
suggesting fixes to this that don't work.
I've tried a few
things including using a bind variable for my very large value. The
last suggested fix I haven't tried is breaking down the large value
into a varchar2s and passing it into DBMS_SQL.PARSE, which seems
very dated and unnecessary. The basic problem is the
following:
DECLARE
v_query VARCHAR2(32767);
BEGIN
FOR ... LOOP
--populate v_query with necessary data
END LOOP;
EXECUTE IMMEDIATE v_query;
END;
This code returns the error
"ORA-01704: string literal too long".
I have tentatively solved
this problem by issuing an update statement and simply updating my
field in the table (it is a CLOB) with the data necessary after the
EXECUTE IMMEDIATE and leaving it null in the initial insert. However,
I would like to try and keep this all within my insert statement if
possible. The code above works just fine so long as v_query < 4000
characters (I know this based on the fact that the largest values
being inserted are 3998 characters).
Answer: You may want to
use a CLOB datatype for this.
String literals in SQL are limited to 4000
characters. Your dynamic SQL is trying to concatenate the CLOB as part
of the SQL statement, turning it into a string literal. If dynamic SQL
were required, you'd need to use bind variables.
See here for complete details on
converting a long datatype to a CLOB.
EXECUTE IMMEDIATE 'INSERT INTO some_table( clob_column ) VALUES( :1 )'
USING p_clob_parameter
and include the URL for the page.
