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;

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 code="">    loop    vara := vara || chr(10) || '--comment';    end loop;         varb                          := ' b number(10))';    -- to make length  32 k    while length(varb) <32000 code="" 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 code="">    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 code="">    loop    vara := vara || chr(10) || '--comment';    end loop;         dbms_output.put_line (length(vara));    EXECUTE IMMEDIATE vara;      END;