Oracle Dynamic SQL: generic search - crazy case

For the Friday evening I decided to dig out my example from the most recent of my published books (Expert PL/SQL Practices, APress, 2011) - it was a very fun project to work with: first, to come with with the idea of such multi-author book, second, to write a chapter focused on Dynamic SQL.

As one of the examples I decided to illustrate how XMLType can be used as both a collection of data elements and a collection of structural ones. Here is a function that lets any two parameters to be passed as filters (key point - ANY parameter, not only predefined ones!) - it is slightly extended variation of what was in print.

create or replace FUNCTION f_searchXML_ref 
   (i_param_xml XMLTYPE:= 
      XMLTYPE( 
        '<param '||
        'col1_tx="DEPTNO" value1_tx="20" type1_tx="NUMBER" '||
        'col2_tx="JOB" value2_tx="CLERK" type2_tx="VARCHAR2"/>'
         )
    )
RETURN SYS_REFCURSOR
IS
    v_out_ref SYS_REFCURSOR;
    v_sql_tx VARCHAR2(32767);
BEGIN
  SELECT              
   'WITH param AS  ('||chr(10)||
   ' SELECT '||chr(10)||
   case EXTRACTVALUE (i_param_xml, '/param/@type1_tx') 
        when 'NUMBER' then 'TO_NUMBER('
        when 'DATE' then 'TO_DATE('
        else null
   end||
   'EXTRACTVALUE (in_xml, ''/param/@value1_tx'')'||
   case EXTRACTVALUE (i_param_xml, '/param/@type1_tx') 
        when 'NUMBER' then ')'
        when 'DATE' then ',''YYYYMMDD'')'
        else null
   end||' value1, '||chr(10)||    
   case EXTRACTVALUE (i_param_xml, '/param/@type2_tx') 
        when 'NUMBER' then 'TO_NUMBER('
        when 'DATE' then 'TO_DATE('
        else null
   end||
   'EXTRACTVALUE (in_xml, ''/param/@value2_tx'')'||
   case EXTRACTVALUE (i_param_xml, '/param/@type2_tx') 
        when 'NUMBER' then ')'
        when 'DATE' then ',''YYYYMMDD'')'
        else null
   end||' value2 '||chr(10)||
   ' FROM (SELECT :1 in_xml FROM DUAL) '||chr(10)||
   ' ) '||chr(10)||
   ' SELECT empno'||chr(10)||
   ' FROM scott.emp, '||chr(10)||
   '            param '||chr(10)||
   ' WHERE 1=1 '||chr(10)||
   case when EXTRACTVALUE (i_param_xml, '/param/@value1_tx') 
     is not null 
   then
       'and emp.'|| dbms_assert.simple_sql_name(
                    EXTRACTVALUE (i_param_xml, '/param/@col1_tx')
                                  )||'=param.value1 '||chr(10)
       else null
   end||
   case when EXTRACTVALUE (i_param_xml, '/param/@value2_tx') 
       is not null 
   then                                  
       'and emp.'|| dbms_assert.simple_sql_name(
                    EXTRACTVALUE (i_param_xml, '/param/@col2_tx')
                                 )||'=param.value2'
       else null
   end
   INTO v_sql_tx  FROM DUAL;
    
   dbms_output.put_line(v_sql_tx);    

   OPEN v_out_ref FOR v_sql_tx USING i_param_xml;
   RETURN v_out_ref;
END;
/


As you see, the incoming XMLType contains a lot: column names (DEPTNO and ENAME), values (20 and KING). and datatypes (NUMBER and VARCHAR2). These parameters are treated in different ways:

• columns are extracted when I am building SELECT statement. Special call to DBMS_ASSERT.SIMPLE_SQL_NAME makes sure that this we are "code injection"-proof
• since by default XML contains text, if we want to properly identify data-types we need to explicitly add TO_NUMBER or TO_DATE calls (for simplicity I assume that all dates will be in YYYYMMDD-format)
• data is passed as a single input variable into OPEN...FOR... This way we do not need to worry if some parameters are completely missing.

And usage is very simple - the same as in the previous case. It will find EMPNO of all employees from the department 20 that are clerks:

declare

    v_ref SYS_REFCURSOR;

    v_tt id_tt;

begin

    v_ref:=f_searchXML_ref();

   

    fetch v_ref bulk collect into v_tt;

    close v_ref;   

    dbms_output.put_line('Fetched:'||v_tt.count);

end;

If you want just department 20 - also no problem:

declare

    v_ref SYS_REFCURSOR;

    v_tt id_tt;

begin

    v_ref:=f_searchXML_ref(
            XMLTYPE( 
               '<param '||
               'col1_tx="DEPTNO" value1_tx="20" type1_tx="NUMBER"/>'
                 )
              );

   

    fetch v_ref bulk collect into v_tt;

    close v_ref;   

    dbms_output.put_line('Fetched:'||v_tt.count);

end;

Yes, I understand that this syntax is somewhat strange - but it is still worth to know! And on that note - have a good weekend, everybody!

P.s. Decided to change blog template - the original one was too narrow, and as a result code samples looked strange.