Execute a DYNAMIC SQL statement from a CLOB type

One of major difference between the Oracle 10 g and 11g version and which has been very helpful to me, it was the possibility to execute a statement( using the EXECUTE IMMEDIATE statement) coming from a CLOB data type : either a column of a table or a PL/SQL variable.

Indeed the 11g version includes some improvements in the management of CLOBs, although there are still some gaps, such as the replace that only place in the first 32k, but this will be a subject for another post.

If the purpose it’s to execute a statement coming directlty from a CLOB column of a table I present the following possibilities for both versions.

• Source code for 11g version
  In the example that follows the SQL statement which coming from a CLOB column of a table, it’s executed directly by a EXECUTE IMMEDIATE statement.

  CREATE TABLE LU_SQL (SQL_ID NUMBER, SQL_STATEMENT CLOB);
  INSERT INTO LU_SQL(SQL_ID,SQL_STATEMENT) VALUES(1,'DELETE FROM APPL_LOG WHERE TYPE_ID = [P_TYPE_ID] ');
  
  DECLARE
   ln_type_id NUMBER := 1;
  BEGIN
  
  FOR rec IN ( SELECT * FROM LU_SQL ) LOOP
  
   EXECUTE IMMEDIATE REPLACE(rec.SQL_STATEMENT,' [P_TYPE_ID]',ln_type_id);
  
  END LOOP;
  
  COMMIT;
  END;
  

  Also in the 11g version it’s possible to manipulate a SQL statement within a variable of CLOB type, as shown in snippet of code below:

  DECLARE
  lcl_sql    CLOB;
  ln_type_id NUMBER := 1;
  BEGIN
  
  FOR rec IN ( SELECT * FROM LU_SQL ) LOOP
  
   lcl_sql := rec.SQL_STATEMENT;
   lcl_sql := REPLACE(lcl_sql,' [P_TYPE_ID]',ln_type_id);
   lcl_sql := RTRIM(lcl_sql,';');
  
   EXECUTE IMMEDIATE lcl_sql;
  
  END LOOP;
  
  COMMIT;
  END;
  

• Source code for 10g version
  In this version it is not possible to execute directly in a EXECUTE IMMEDIATE statement a dynamic statement. In order to implement this, it is necessary to choose one of the following possibility:
  1. Use of a VARCHAR2 variable
     If there is certainty that the SQL statement does not exceed the 32767 characters, you can copy the SQL statement from the CLOB column of a table to a VARCHAR2 variable and then to perform the contents of this variable.

     DECLARE
     ls_sql VARCHAR2(32767);
     BEGIN
     
     FOR rec IN ( SELECT * FROM LU_SQL ) LOOP
     
      ls_sql := rec.SQL_STATEMENT;
       EXECUTE IMMEDIATE ls_sql;
     
     END LOOP;
     
     COMMIT;
     END;
     

  2. Use the “DBMS_SQL” package
     In otherwise if the SQL statement will exceeds the the maximum size of a VARCHAR2 data type, then you can use the package “DBMS_SQL” as shown in the following example:

     DECLARE
     lcl_sql   CLOB;
     ln_cursor NUMBER;
     ln_result NUMBER;
     ln_sql_id NUMBER := 1;
     BEGIN
     
      ln_cursor := DBMS_SQL.OPEN_CURSOR;
     FOR rec IN (SELECT *
                   FROM LU_SQL
                 ) LOOP
     
      lcl_sql := rec.SQL_STATEMENT;
      lcl_sql := REPLACE(UPPER(lcl_sql), '[P_SQL_ID]',':P_SQL_ID');
     
      DBMS_SQL.PARSE(ln_cursor, lcl_sql, DBMS_SQL.NATIVE);
     
      IF INSTR(lcl_sql, ':P_SQL_ID') > 0 THEN
       DBMS_SQL.BIND_VARIABLE(ln_cursor,':P_SQL_ID',ln_sql_id);
      END IF;
     
      ln_result := DBMS_SQL.EXECUTE(ln_cursor);
     
     END LOOP;
     DBMS_SQL.CLOSE_CURSOR(ln_cursor);
     
     END;
     

Segment,
“Sometimes a few words at the right time can inspire you, pick you up and point you toward a future worth living.”