WIXLIB

Dynamic SQL in the 11g WorldMichael Rosenblumwww.dulcian.comJune 5, 20121 of 41

Who Am I? – “Misha” OracleACE Co-author of 2 books PL/SQLfor Dummies Expert PL/SQL Practices WonODTUG 2009 Speaker of the Year Known for: SQLand PL/SQL tuning Complex functionality Code generatorsRepository-based development2 of 41

Why am I giving thispresentation? – Because SQL is my favorite toy – and I like totalk about it! Dynamic2007 – Dynamic SQL in Dynamic World ODTUG 2008 – Dynamic SQL: The Sequel Expert PL/SQL Practices, Apress, 2011 – Chapter 2 ODTUG Developersare still uncomfortable withDynamic SQL. Thereare many myths and misunderstandingsfloating around. Majority So,of people are (finally!) using 11git is time to give an 11g-only presentation.3 of 41

Just to make sure that we areon the same page 4 of 41

The Hero DynamicSQL: Makesit possible to build and process complete SQLand PL/SQL statements as strings at runtime.5 of 41

Dynamic SQL Core About90% of Dynamic SQL is covered by a singlecommand (with variations):declarev var varchar2(N) CLOB;beginv var: 'whatever you want';EXECUTE IMMEDIATE v var;end;ORbeginEXECUTE IMMEDIATE 'whatever you want';end;6 of 41

Dynamic Cursors Syntaxdeclarev cur SYS REFCURSOR;v sql varchar2(N) CLOB: .v rec .%rowtype; -- or record typebeginopen v cur for v sql tx;fetch v cur into v rec;close v cur;end; Mostcommon use: Processinglarge datasets with unknown structure7 of 41

DBMS SQL PackagePredecessor of Native Dynamic SQL Pros: Separates PARSE and EXECUTEThe same query can be reused with different bind variables. Workswith unknown number/type ofINPUT/OUTPUT values Cons: Significantlyslower More difficult to use8 of 41

11g-Only Features9 of 41

Agenda Supportof CLOBs in Native Dynamic SQL Cursor transformation No datatype restrictions for DBMS SQL DBMS SQLnow is a complete superset of NativeDynamic SQL DBMS ASSERTis official now. Nice-to-have: Betterintegration On-the-fly wrapper10 of 41

CLOB as Input Goodnews: EXECUTE Badnews: You IMMEDIATE can take CLOB.need to know how to properly use CLOBs.Concatenation is not such a good idea! Ifyou need to build an on-the-fly PL/SQL object 32Kthere may be something wrong with this picture But it may also be OK (I personally have used these!)11 of 41

Building a Query - 1function f BuildQuery CL (in tab tx varchar2)return CLOB isv out cl CLOB;v break tx varchar2(4): ' BR ';v hasErrors yn varchar2(1): 'N';v buffer tx varchar2(32767);procedure p flush isbegindbms lob.writeappend(v out cl,length(v buffer tx), v buffer tx);v buffer tx: null;end;procedure p addToClob (in tx varchar2) isbeginif length(in tx) length(v buffer tx) 32767 thenp flush;end if;v buffer tx: v buffer tx in tx;12 of 41end;

Building a Query - 2Begindbms lob.createtemporary(v out cl,true,dbms lob.Call);p addToClob('create or replace view V ' in tab tx ' as select ROWNUM num rows');for rec rep in (select * from my tab colwhere table name in tab tx)loopp addToClob(',' rec rep.column tx);end loop;.p addToClob(' from ' in tab tx);p flush; -- write leftoversreturn v out cl;end;13 of 41

Dynamic Wrapper DBMS DDLpackage WRAP CREATE WRAPPED Whatdoes it do? Objects (wrap Execute Immediate)are created and wrapped on the fly.This is a really good idea for repository-based systems.This feature was introduced long ago, but I only recentlydiscovered it. Unfortunatelyit does not accept CLOB as input(only Varchar2/Array of Varchar2)14 of 41

Sampledeclarev wrap tx varchar2(32767);v ddl tx varchar2(32767);beginv ddl tx: 'create or replace procedure ' 'p emp is begin null; end;';v wrap tx: dbms ddl.wrap(v ddl tx);execute immediate v wrap tx;ordbms ddl.create wrapped(v ddl tx);end;15 of 41

DBMS SQL and Object Types Startingwith 11g, DBMS SQL can operate withuser-defined object types and collections.BIND ARRAY – no support of userdefined collections Workaround: PL/SQL block with OUT-variable of aneeded type Exception:16 of 41

Object Type Case Setting Thereis a universal value list builder that returns anobject collection. Task Input: SQL query with value list object collection as bindvariableObject collection Output: Detailed description of the queryOpened SQL cursor Usage: Applications with on-the-fly query builders17 of 41

OType Example – 1 (setup)create type lov oty as object(id nr number, display tx varchar2(4000));create type lov nt is table of lov oty;Create function f getlov nt(i table tx varchar2,i id tx varchar2,i display tx varchar2,i order tx varchar2)return lov nt isv out nt lov nt : lov nt();beginexecute immediate'select lov oty(' i id tx ',' i display tx ')' ' from ' i table tx ' order by ' i order txbulk collect into v out nt;return v out nt;end;18 of 41

OType Example - 2PROCEDURE p prepareSQL(i sql tx in varchar2,i lov nt in lov nt,o cur OUT SYS REFCURSOR,o structure tx OUT varchar2)isv cur INTEGER;v result nr integer;v cols nr number : 0;v cols tt dbms sql.desc tab;beginv cur: dbms sql.open cursor;dbms sql.parse(v cur, i sql tx, dbms sql.native);dbms sql.describe columns(v cur, v cols nr, v cols tt);for i in 1 . v cols nr loopo structure tx: o structure tx ' ' v cols tt (i).col name;end loop;dbms sql.bind variable(v cur, 'NT1',i lov nt);v result nr: dbms sql.execute(v cur);o cur: dbms sql.to refcursor(v cur);end;19 of 41

OType Example - 3SQL declare2v ref cur SYS REFCURSOR;3v columnList tx varchar2(32767);4v lov nt lov nt: 5f getlov nt('DEPT','DEPTNO','DNAME','DEPTNO');6v sql tx varchar2(32767): 7'select * ' chr(10) 8'from emp ' chr(10) 9'where deptno in (' chr(10) 10'select id nr' chr(10) 11'from table(cast (:NT1 as lov nt))' chr(10) 12')';13 begin14p prepareSQL(v sql tx,15v lov nt,16v ref cur,17v columnList tx);18dbms output.put line('Columns:' v columnList tx);19if v ref cur%isopen then20dbms output.put line('Valid Cursor!');21end if;22 end;23 /Columns: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNOValid Cursor!PL/SQL procedure successfully completed.SQL 20 of 41

Transformation of Cursors Conversionbetween REF CURSOR andDBMS SQL cursor DBMS SQL.TO CURSOR NUMBER From: REF CURSORTo: DBMS SQL cursorCondition: REF CURSOR must be opened DBMS SQL.TO REFCURSOR From: DBMS SQL cursorTo: REF CURSPRCondition: DBMS SQL cursor must be opened, parsedand executed21 of 41

Operational Details Fetching: Fetching pointer is preserved.It does not matter which side is doing the FETCH. Transformationto REF CURSOR is allowed only ifprevious FETCH returned something. i.e. CUR%NOTFOUND is still TRUE Accessibility: Cursorsthat are being transformed (FROM-side) areautomatically closed during the transformation andno longer accessible afterwards.22 of 41

Explaining REF Cursor (1) Theproblem: Alot of REF Cursors in the system with no clearway of figuring out what exactly they are Thesolution: Genericroutine to describe REF Cursor withminimal impact on the system23 of 41

Explaining REF Cursor (2)procedure p expCursor(io ref cur IN OUT SYS REFCURSOR) isv curinteger;At this pointv cols nr number : 0;IO REF CURv cols tt dbms sql.desc tab;is unusable!beginv cur: dbms sql.to cursor number(io ref cur);DBMS SQL.describe columnsIO REF CUR(v cur, v cols nr, v cols tt);is re-initialized;for i in 1 . v cols nr loopV CUR is closeddbms output.put line('*' v cols tt (i).col name);end loop;io ref cur: dbms sql.to refcursor(v cur);end;24 of 41

Fetching Continuation (1)declarev ref SYS REFCURSOR;v emp rec emp%rowtype;beginopen v ref forselect * from emp order by ename;-- fetch oncefetch v ref into v emp rec;dbms output.put line(v emp rec.empno '-' v emp rec.ename);p expCursor(v ref);-- fetch twicefetch v ref into v emp rec;dbms output.put line(v emp rec.empno '-' v emp rec.ename);close v ref;end;25 of 41

Fetching Continuation (2)SQL DEPTNO7499-ALLENPL/SQL procedure successfully completed.SQL 26 of 41

Fetching – Both Sides (1)PROCEDURE p expCursor(io ref cur IN OUT SYS REFCURSOR) isv curinteger;v cols nr number : 0;v cols tt dbms sql.desc tab;v sample tx varchar2(4000);v fetch nr integer;beginv cur: dbms sql.to cursor number(io ref cur);DBMS SQL.describe columns(v cur, v cols nr, v cols tt);for i in 1 . v cols nr loopdbms output.put line('*' v cols tt (i).col name);end loop;------------ fetch on this side too ------------dbms sql.define column(v cur,2,v sample tx,4000);v fetch nr: dbms sql.fetch rows(v cur);dbms sql.column value(v cur,2,v sample tx);dbms output.put line('Fetch ' v sample tx);io ref cur: dbms sql.to refcursor(v cur);end;27 of 41

Fetching – Both Sides (2)SQL DEPTNOFetch ALLEN7698-BLAKEPL/SQL procedure successfully completed.SQL 28 of 41

Fetching – Lower Boundarydeclarev ref SYS REFCURSOR;v emp rec emp%rowtype;beginopen v ref forselect * from empwhere rownum 1order by ename;NowV REF%notfound TRUEfetch v ref into v emp rec;dbms output.put line(v emp rec.empno '-' v emp rec.ename);p expCursor(v ref);close v ref;end;Will fail with“ORA-01001: invalid cursor”callingdbms sql.to refcursor29 of 41

Fighting Code Injections Oldtechniques: USE Because bind variables are injection-proof Use NewBIND VARIABLES!!!repositories for structural elements.Although, repository-based systems are a bit hard to sellnowadays technique: DBMS ASSERT packageIf you cannot use repositories but still want to make surethat your code is protected30 of 41

DBMS ASSERT Validatingstrings:(string) – checks whether ornot string is a valid object SIMPLE SQL NAME – checks whether or notstring is a valid simple SQL name SCHEMA NAME – validates that passed string is avalid schema ENQUOTE NAME – adds a second quote to everyinstance in the name (and double quotes around) ENQOUTE LITERAL – adds single quotes SQL OBJECT NAME31 of 41

Access any Column with PKfunction F GET col TX(i table tx,i showcol tx,i pk tx,i pkValue nr)return varchar2 isv out tx varchar2(4000);v sql tx varchar2(32000);Beginv sql tx: 'select to char(' dbms assert.simple sql name(i showcol tx) ') from ' dbms assert.simple sql name(i table tx) ' where ' dbms assert.simple sql name(i pk tx) ' :v01';EXECUTE IMMEDIATE v sql tx INTO v out txUSING i pkValue nr;return v out tx;end;32 of 41

More DBMS ASSERT - 1 You can check syntax yourself , but some things youcannot find in docs: If DB-link is specified, only syntax is checked:SQL select DBMS ASSERT.SQL OBJECT NAME('DBMS ASSERT@DUMMY DBLINK') check yn2 from dual;CHECK YN--------------------------DBMS ASSERT@DUMMY DBLINK Schema name is case-sensitive only sometimes!SQL select DBMS ASSERT.SCHEMA NAME('Scott') from dual;select DBMS ASSERT.SCHEMA NAME('Scott') from dual*ERROR at line 1:ORA-44001: invalid schemaORA-06512: at "SYS.DBMS ASSERT", line 266SQL select DBMS ASSERT.SQL OBJECT NAME('Scott.emp') check yn2 from dual;Check yn-------------------Scott.emp33 of 41

More DBMS ASSERT - 2 Otherwise object names are case-sensitive only if wrapped indouble-quotesSQL select DBMS ASSERT.SQL OBJECT NAME('DBMS ASSERT') check yn from dual;CHECK YN-----------------------------DBMS ASSERTSQL select DBMS ASSERT.SQL OBJECT NAME('"DBMS ASSERT"')check yn from dual;CHECK YN-----------------------------"DBMS ASSERT"SQL select DBMS ASSERT.SQL OBJECT NAME('"dbms assert"') check yn from dual;select DBMS ASSERT.SQL OBJECT NAME('"dbms assert"') check yn from dual*ERROR at line 1:ORA-44002: invalid object nameORA-06512: at "SYS.DBMS ASSERT", line 316SQL 34 of 41

Integration with Other Features PL/SQLteam really tries to make Dynamic SQLbetter integrated with other features RESULTCACHE auto-detects objects referencedvia Dynamic SQL call [11.2 only!]35 of 41

Result Cache - 1-- Original functioncreate or replace function f getCount nr(i tab tx varchar2)return numberresult cacheisv sql tx varchar2(256);v out nr number;beginexecute immediate'select count(*) from ' i tab tx into v out nr;return v out nr;end;36 of 41