Mittwoch, 26. November 2008

dynamic SQL with DMLs

Once again, Christian Antognini is the reason for this entry (beside my curiosity of course).
In Chapter 8 - Parsing of his book Troubleshooting Oracle Performance he showed the advantage of explicit cursor handling. The package dbms_sql shows the most flexibility. Unfortunately he only showed SELECT statements, no DML (which I'm interrested for some reasons). So I had to do the tests myselve:

CREATE TABLE dyn_sql (id number, val varchar2(32));

DECLARE   v_cur_hdl         INTEGER;
  v_stmt_str        VARCHAR2(200);
  v_rows_processed  BINARY_INTEGER;
  gid               INTEGER;
  gval              VARCHAR2(32);
BEGIN   v_stmt_str := 'INSERT INTO dyn_sql VALUES (:gid, :gval)';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR(2); -- to avoid ORA-29471 in 11g  
  DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  1);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V01C01');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  2);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V02C01');
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  COMMIT;

  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  3);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V03C02');

  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  4);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V04C02');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  ROLLBACK;
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  5);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V05C03');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
 /

SQL> select * from dyn_sql;
        ID VAL
---------- ------
         1 V01C01
         2 V02C01
         5 V05C03
blubb
what are my findings?
  1. DBMS_SQL works fine also for DMLs.
  2. commit/rollback within an open cursor works fine.
  3. closing a cursor implicite commits (direct sql-trace sequence: EXEC(insert) - CLOSE(insert) - EXEC(pl/sql) - CLOSE (pl/sql)Deleted after Chris commentar
There is an explicite warning in the documentation: Native Dynamic SQL is faster than DBMS_SQL. I did not test this. But be warned. (and test it yourselve, If you fear performance!)
Kommentar veröffentlichen