Wednesday 22 February 2012

Executing CLOBs as DDL

How to kill your session

11g> declare
  2  v_clob clob :=  EMPTY_CLOB(); -- initialize clob
  3  begin
  4  execute immediate v_clob;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8540
Session ID: 196 Serial number: 364

How to execute it properly

11g> declare
  2  v_clob clob :=  EMPTY_CLOB();
  3  begin
  4  v_clob := to_clob('begin null; end;'); -- assign ddl value to clob
  5  execute immediate v_clob;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Something you couldn't do in 10g

10g> declare
  2  v_clob clob :=  EMPTY_CLOB();
  3  begin
  4  v_clob := to_clob('begin null; end;');
  5  execute immediate v_clob;
  6  end;
  7  /
execute immediate v_clob;
                  *
ERROR at line 5:
ORA-06550: line 5, column 19:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

Related OTN post.

Documentation here.

Related to an idea regarding lifecycle management.

No comments: