How to pass CLOB argument in EXECUTE IMMEDIATE

clob argument in execute immediate oracle 11g

We know that Oracle EXECUTE IMMEDIATE statement implements Dynamic SQL in Oracle. It provides end-to-end support when executing a dynamic SQL statement or an anonymous PL/SQL block. Before Oracle 11g, EXECUTE IMMEDIATE supported SQL string statements up to 32K in length.

Oracle 11g allows the usage of CLOB datatypes as an argument which eradicates the constraint we faced on the length of strings when passed as an argument to Execute immediate.

Lets take an example to show how execute immediate failed for strings of size > 32K

Example 1:

DECLARE var VARCHAR2 (32767); BEGIN var := 'create table temp_a(a number(10))'; -- to make a string of length > 32767 WHILE (LENGTH (var) < 33000) LOOP var := var || CHR (10) || '--comment'; END LOOP; DBMS_OUTPUT.put_line (LENGTH (var)); EXECUTE IMMEDIATE var; END;
Code language: SQL (Structured Query Language) (sql)

It will throw an obvious error : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Lets start with how these scenarios were handled prior to introduction of CLOB argument in Execute Immediate.

DBMS_SQL was used with its inbuilt functions to take care of Dynamic SQL. Its inbuilt function PARSE was used to take care of dynamic SQL of 64k size.

But it had certain drawbacks:

  • DBMS_SQL.PARSE() could not handle CLOB argument
  • A REF CURSOR can not be converted to a DBMS_SQL cursor and vice versa to support interoperability
  • DBMS_SQL did not supports the full range of data types (including collections and object types)
  • DBMS_SQL did not allows bulk binds using user-define (sic) collection types

A Simple example to show how DBMS_SQL was used to take care of long strings :

Example 2:

DECLARE vara VARCHAR2 (32767); varb VARCHAR2 (32767); ln_cursor NUMBER; ln_result NUMBER; ln_sql_id NUMBER := 1; BEGIN ln_cursor := DBMS_SQL.open_cursor; vara := 'create table testa( a number(10),'; -- to make length 32 k while length(vara) <32000 loop vara := vara || chr(10) || '--comment'; end loop; varb := ' b number(10))'; -- to make length 32 k while length(varb) <32000 loop varb := varb || chr(10) || '--comment'; end loop; dbms_output.put_line (length(vara)||'and'||length(varb)); DBMS_SQL.parse (ln_cursor, vara ||chr(10)|| varb, DBMS_SQL.native); ln_result := DBMS_SQL.EXECUTE (ln_cursor); DBMS_SQL.close_cursor (ln_cursor); END;
Code language: SQL (Structured Query Language) (sql)

CLOB argument in Oracle 11g

Oracle Database 11g removes DBMS_SQL limitations restrictions to make the support of dynamic SQL from PL/SQL functionally complete.

Lets see it through an Example.

Example 3: The only difference in Example 3 as compared to Example 2 is Use of CLOB for the declaration of vara variable.

DECLARE vara CLOB; ln_cursor NUMBER; ln_result NUMBER; ln_sql_id NUMBER := 1; BEGIN ln_cursor := DBMS_SQL.open_cursor; vara := 'create table testa( a number(10))'; -- to make length 32 k while length(vara) <70000 loop vara := vara || chr(10) || '--comment'; end loop; dbms_output.put_line (length(vara)); DBMS_SQL.parse (ln_cursor, vara, DBMS_SQL.native); ln_result := DBMS_SQL.EXECUTE (ln_cursor); DBMS_SQL.close_cursor (ln_cursor); END;
Code language: SQL (Structured Query Language) (sql)

Now Both Native Dynamic SQL and DBMS_SQL support SQL strings stored in CLOBs. But using DBMS_SQL has an overload of PARSE that accepts a collection of SQL string fragments.

This is not ideal and the CLOB implementation in EXECUTE IMMEDIATE solves any issues we might have had with the previous alternatives.

Example 4:

DECLARE vara CLOB; BEGIN vara := 'create table testa( a number(10))'; -- to make length 64k k while length(vara) <70000 loop vara := vara || chr(10) || '--comment'; end loop; dbms_output.put_line (length(vara)); EXECUTE IMMEDIATE vara; END;
Code language: PHP (php)

So now don’t worry about the size of strings, Oracle 11g has solved the limitations we had for Dynamic SQL executions.

Get our Articles via Email. Enter your email address.

You may also like...

2 Comments

  1. Natan says:

    Wrong example. It only works if the SQL statement is less than 4000 characters. Placing chr (10) does not reproduce the real problem and leads to a misconception.

  2. Gosia says:

    it doesn’t work at all. There are 3 major errors, that’s why I think that you didn’t even run your example.
    1. Initialize clob dbms_lob.createTemporary(vara, TRUE);
    2. Use DBMS_LOB.WRITEAPPEND
    3.replace clob EXECUTE IMMEDIATE REPLACE(vara,

Leave a Reply

Your email address will not be published. Required fields are marked *