How to pass CLOB argument in EXECUTE IMMEDIATE

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.