How to pass CLOB argument in EXECUTE IMMEDIATE

clob argument in execute immediate oracle 11gWe 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;

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;

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;

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; 

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...

1 Comment

  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.

Leave a Reply

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