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.

Collaborate'13 Summary. Part 2. MySQL.. New David?

After making a number of purely technical posts I decided today to switch back and continue describing Collab'13. The first post was about the leadership, which was a somewhat untrivial topic for what was normally known as heavily technical conference. Now it's time to get more technical!

2. MySQL

As you might noticed from my original conference schedule, I've been really interested in learning a lot of completely new stuff. Good news - I've got a lot of things to review, bad news - some answers were not what I was expecting and now I am struggling to fit them in the picture of a sane universe.

Let me start from MySQL. A lot of thanks to George Trujillo and Dave Stokes for providing tons of really deep insights on the role of this database in the contemporary IT environment. It was a big surprise for me to realize how much influence MySQL got in the corporate world - for example, as far as I understood, if you use Expedia.com, the search is done via MySQL while ordering is done via Oracle.

It seems that this dual implementation slowly but steadily becomes "a new norm" - there is one major database, implemented using high-end solution + there are other side projects done via MySQL. Reasons are usually split into two groups:

• money: even big corporations think a lot about costs of Oracle licences. As a result I've heard from a number of people the following corporate policy: "We do it in MySQL, unless you can prove that it cannot be done there"
• read optimization: let's keep the picture clear - the main purpose of MySQL from the very beginning was to  quickly bring the data up. Everything else is "nice to have", the MySQL development team stays very focused on that vision (I've talked with them!)
• flexibility: another big surprise for me was that MySQL is really a container of different possible storage engines. And there are dozens of them - each tunes in its own way. Yes, InnoDB is the closest to "real" RDBMS with ACID compliance, memory processes etc - but in a lot of cases you just don't care about all that overhead! Of course, you pay the price for it - from the questions of the audience I understood that a significant percentage of performance issues is usually related with the selection of a wrong storage engine. Sorry, that's what a reverse side of "flexibility" coin...

Summary: yes, MySQL has its limitations and issues - and it is not designed to replace Oracle RDBMS, but for smaller/side projects it becomes a solid alternative. As a result for anybody involved with Oracle databases sooner than later it will become a necessity to have a good idea about what MySQL is all about. Back to books, ladies and gentlemen!

P.s. I am aware that Facebook runs on MySQL - and that MySQL can technically support big implementations. But in "the real corporate world" there are terabytes of data managed by RDBMS - and thousands of specialists trained to do it well. For them MySQL can augment their tech stack, but cannot replace it.

Oracle Dynamic SQL: generic search - REF CURSOR

This post is a continuation of a topic, I've raised previously - dynamic implementation of generic searches. As  I mentioned in that post, there are circumstances, when it is much more convenient to return a pointer to a row-set instead of that row-set. Or in terms of Oracle - REF CURSOR instead of a collection.

Here is a variation of my search procedure that uses REF CURSOR:

create or replace function f_search_ref
    (i_limit_nr number:=null,
     i_param_empno_nr   number:=null,
     i_param_ename_tx varchar2:=null,
     i_param_job_tx varchar2:=null)
return SYS_REFCURSOR
is
    v_sql_tx varchar2(32767);
    v_ref sys_refcursor;
begin
    -- opening
    v_sql_tx:=
     'declare '||chr(10)||
     '  lv_count_nr constant number:=:1;'||chr(10)||        
     '  lv_empno_nr constant number:=:2;'||chr(10)||
     '  lv_ename_tx constant varchar2(50):=:3;'||chr(10)||
     '  lv_job_tx  constant varchar2(50):=:4;'||chr(10)||
     'begin '||chr(10)||
    'open :5 for select empno from emp '||chr(10)||
        ' where rownum <=lv_count_nr ';

    -- i_param_empno
    if i_param_empno_nr is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and empno = lv_empno_nr ';
    end if;

    if i_param_ename_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and ename like ''%''||lv_ename_tx||''%'' ';
    end if;

    if i_param_job_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and job = lv_job_tx ';
    end if;

    -- closing
    v_sql_tx:=v_sql_tx||';'||chr(10)||
              'end;';

    dbms_output.put_line(v_sql_tx);

    execute immediate v_sql_tx
        using nvl(i_limit_nr,50),
              i_param_empno_nr,
              i_param_ename_tx,
              i_param_job_tx,
              v_ref;
              
    return v_ref;
end;

Key changes are highlighted:

• Function should return SYS_REFCURSOR - it's a "weak" REF CURSOR that can point to any type of a row-set, exactly as needed
• To open that REF CURSOR I am using "OPEN...FOR..." statement
• Please, notice that even we "think" that REF CURSOR is an output of out Dynamic SQL, for Oracle it is still IN-parameter.

And here is an example of how that REF CURSOR can be used.

declare
    v_ref SYS_REFCURSOR;
    v_tt id_tt;
begin
    v_ref:=f_search_ref(10,null,null,null);
   
    fetch v_ref bulk collect into v_tt;
    close v_ref;
   
    dbms_output.put_line('Fetched:'||v_tt.count);
end;

The reason I've included it is clear - PLEASE, do not forget to close REF CURSORS when you finished using it! Resources are limited, so let's not waste it unnecessarily!

Oracle Dynamic SQL: generic search - simple case

It is a very common problem - how do you implement generic search over the table? By generic search I mean that users are provided with tons of different options that could be used in all possible permutations.

It leads to a standard problem - how do you make sure that all of those searches are reasonably optimized. There are multiple schools of thoughts about it - a lot of contemporary database experts claim that CBO became so good that it can figure out everything. To be fair, I belong to the other group of people who are a bit more skeptical and suggest that we should help Oracle at least somewhat.

Of course, there is always DBMS_SQL package that can handle anything you can imaging, but a lot of developers (especially envisioning future maintenance) prefer to stay with Native Dynamic SQL. It is definitely understandable, and sometimes ago I came up with the trick to generic search without DBMS_SQL.

In the example below I wrote a function that would do a search on SCOTT.EMP table:

• three possible conditions (yes, I understand that a number of conditions may be unknown - I have a bit crazy example with passing XMLType as a list of parameters, but that is a bit more advanced. Let's start with the most direct case):
• EMPNO - direct ID check
• JOB - direct match
• ENAME - like condition
• Return limit is defaulted to 50, but can be overwritten
• Please, keep in mind that it is just a sample - if you need to do real pagination, I would rather recommend using the same approach, but return REF CURSOR as output. I will show this approach also as a separate post.
• Of course, keep in mind that loading thousands of objects into the memory may of may not be a good thing :-)
• Output with the list of primary keys
• Because I wanted to have that function usable in both SQL and PL/SQL, I decided to implement this output as a collection of numbers.

From the conceptual standpoint, the idea is very simple (but a bit strange :-) ):

• As the initial stage, I am trying generate not SQL, but PL/SQL block where ALL possible parameters become local constants. Each constant gets default value that will be passed in as a bind variable.
• At the same time I (in this example) am building SELECT and FROM clauses of the query. Sometimes this step also has to be conditional, because depending on parameters you may or may not need to do extra joins. To simplify the case for now we have a single-table search, so there is no need to do any checks here.
• Also I initialize WHERE clause (so later I can just do AND without worrying about the syntax) with mandatory condition.
• The second step involves spinning through all passed parameters and building extra conditions as needed. Here is where I have a main trick - instead of referencing real bind variables I reference my local variables that were created at the initial stage.
• The last step is to close PL/SQL block and fire EXECUTE IMMEDIATE. Please, notice, that I am passing ALL parameters - but in the SQL statement only SOME local variables will be used.

Overall described approach has a number of merits to be considered ;-) : 

1. - you don’t have to learn DBMS_SQL  :-)  
2. - you still will be using bind variables with all possible permutations
3. - you can directly see what you are trying to execute (very important for complex queries!)

create type id_tt is table of number;
/
-- search function
create or replace function f_search_tt 
    (i_limit_nr number:=null,
     i_param_empno_nr   number:=null,
     i_param_ename_tx varchar2:=null,
     i_param_job_tx varchar2:=null)
return id_tt
is
    v_sql_tx varchar2(32767);
    v_out_tt id_tt;
begin
    -- opening
    v_sql_tx:=
        'declare '||chr(10)||
        '  lv_count_nr constant number:=:1;'||chr(10)||        
        '  lv_empno_nr constant number:=:2;'||chr(10)||
        '  lv_ename_tx constant varchar2(50):=:3;'||chr(10)||
        '  lv_job_tx  constant varchar2(50):=:4;'||chr(10)||
        'begin '||chr(10)||
        ' select empno bulk collect into :5 from emp '||chr(10)||
        ' where rownum <=lv_count_nr ';

    -- i_param_empno
    if i_param_empno_nr is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and empno = lv_empno_nr ';
    end if;

    if i_param_ename_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and ename like ''%''||lv_ename_tx||''%'' ';
    end if;

    if i_param_job_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and job = lv_job_tx ';
    end if;

    -- closing
    v_sql_tx:=v_sql_tx||';'||chr(10)||
              'end;';

    dbms_output.put_line(v_sql_tx);

    execute immediate v_sql_tx
        using nvl(i_limit_nr,50),
              i_param_empno_nr,
              i_param_ename_tx,
              i_param_job_tx,
              out v_out_tt;

    dbms_output.put_line('Total rows found:'||v_out_tt.count);

    return v_out_tt;
end;
/

And now I can use this function in whatever pattern I want - exactly as specified!

-- get first 10 rows

select * from table(f_search_tt(10,null,null,null))
-- check ID
select * from table(f_search_tt(null,7566,null,null))
-- get only name search
select * from table(f_search_tt(null,null,'A',null))
-- get only job search
select * from table(f_search_tt(null,null,null,'MANAGER'))
-- get two conditions together
select * from table(f_search_tt(null,null,'A','MANAGER'))

Hope, it helps! And thanks a lot to my Collab'13 friends who constantly keep me thinking about better ways of solving already well-known problems!

Collaborate'13 Summary. Part 1. IOUG Strategic Leadership Program

I really hope that it was the last time I've seen SNOW this spring :-) But other than that - it was a great conference! A lot of interesting conversations, a lot of interesting events - and a lot of thoughts afterwards (part two, part three, part four). Since in addition to purely technical part of the conference I've been participating in IOUG Strategic Leadership Program, it's also fair to cover it separately

1. Leadership issues

It is now pretty official - IOUG experts have grown up and crossed boundaries of being "just-a-good-DBA". There are now significantly higher numbers of experts eventually moved into C-level roles. As a result, an addition of the whole separate program covering that crossing of the line expert/leader was a great success. I felt for both speakers and the audience the whole conversation process became really interesting and challenging. Considering that majority of talks in the program were more discussion panels than pure presentations - it was a lot of strain on speakers to be able to frankly and openly answer questions that were raised by listeners. And majority of the answers were based on the real-life cases and learned lessons.

Personally, one of the most important discussed issues was a difference in the thinking patterns between "a techie" and "a leader". For example, it became clear that "a techie" can allow him/herself to be more cautious,  while being a leader always means taking higher risks and making decisions in the classical "fog-of-war" environment. I had to re-think a lot of my decision-making patters from that angle... Let's be fair, for DBAs a mild level of paranoia is more-or-less a job requirement - otherwise you just cannot protect your data and your people using this data. But if you are not a pure DBA - let's say you are involved in the development/strategy as a Senior DBA should be, the whole game is different: if you always play "safe" the whole team will eventually just ignore you, because even your approach will be "correct" it will constantly stop  the floating of ideas around the table. Yes, DBAs need to learn how to take risks. 

The same idea is about working with the high uncertainty - a leader is always "sure" about something, there is no room for if's and "when's. Decisions have to be made now - and more than that, your team should be confident that the leader knows what's going on. That projection of certainty is a very important skill to work on. Otherwise even the best expert could shake a team by questioning every fact and logical link.

Summary: great event! Thanks a lot to IOUG committee for setting up this kind of completely new (at least for me) area of learning.

Off to Denver. Collaborate'13 Preview

Final preparations before heading off to Denver: Collaborate'13 is awaiting! Presentations - checked, tickets/hotel reservations - checked, session schedule - che-e-e-e-e-e-e....

If you go to a good conference, some schedule conflicts are normal - there are too many good speakers and there is always not enough time slots to go everywhere. But this year for me was indeed a pretty tough time to balance all of my wishes, because in addition to my regular "split personality" (DBA/Development), I've got a new hobby ("Big Data") and more explicit C-level role (I've got invited to attend IOUG Strategic Leadership Program). So, I had even more choices to pick from.

As promised earlier, here is my plan (of course, with some variations):

1. Sunday

• #517 Gary Gordhamer: "Hacking, Cracking, Attacking" - to be able to protect my DB, I'd better know it vulnerabilities! Should be definitely useful!

2. Monday

•  #872 Annette Baldenegro "Introduction to Apache Hadoop" - I am still trying to figure out how Hadoop solutions could be integrated with RDBMS.
• #464 George Trujillo "Demystifying MySQL for Oracle DBAs and Developers" - new DB environments like new languages, more you know - more you understand.
  ---or  ---
• #166 Mike Abbey "Pluggable Databases - An Intro" - considering that in the development environment the most time is always spent on setting up extra configurations, it will be interesting to see what Oracle is planning to do in 12c. And listening to Mike is fun anyways, irrelevant of the topic  ;-)
• #162 Michael Corey "Trends in Database Administration" - another great speaker. And the topic is also great - currently DBAs are much more involved in the overall business cycle than it was even couple of years ago.
  ---  or ---
• #13104 William Hardie, IOUG Oracle Rep "What's next to Oracle database" - one of the key presentations from Oracle employees. Not sure how to select
• #402 my own talk :-) Top 5 Issues that cannot be resolved by DBAs

3. Tuesday

• #737 Tom Deutsch "How to get started with your first BigData project" - really good question, isn't it?
• #118 Michael Conklin "Database Shrinking - Advanced" - yes, we can buy more storage space, but saving some money would not be a bad idea too
•  #386 Carl Dudley "Oracle Join Techniques" - there is something stylish in the good presentation spiced with good British humor. 

4. Wednesday

• #103360 Sambasivam Sampathnathan "Why mask Big Data" - security problems+Big data = big security problems? Let's see what other people are doing here.
• #403 Maxym Kharchenko "SQL Pagination Patterns" - classical problem of getting TopN has more and more solutions with newer Oracle releases. Will be interesting to see a specialized talk on this
• #399 my second talk "Data Tracking: On the hunt for information about your database"
• #225 David Chen "Demystify Global Temporary Tables" - one of my favorite features for years (if properly used). Looking forward to see what else can I learn about it
• #935 Dipti Brokar "Why every NoSQL Deployment should be paired with Hadoop" - hm-m-m-m-m, should it? I am not 100% sure, but it will be worthwhile to listen to arguments.

5. Thursday

• #119 Bjoern Rost "The ins and outs of Oracle Total Recall" - I always wanted to learn a bit more about this feature. Good opportunity just before my flight home.

My next promise - I will try to do full-scale daily reporting (but, sorry, only post-factum! No real-time journalism for now :-). See you in Denver!

Books are forever!

One of my friends just sent me a link to Amazon's chart of Best Sellers (category: "Oracle Databases")...

7-year-old  "PL/SQL for Dummies" is #2!  Here is a proof:

The most interesting question - does it mean that IT specialists finally started to learn PL/SQL? :-)

Being a bit more serious, I am a also concerned that these sale numbers also indicate a simple truth: for an average developer there is more than enough features in 9i/10g (the book is published in 2006!), especially at the beginning. But we all know that from one version to another the best practices are constantly changing... Especially if you want to work with any meaningful data volume!

Not sure - maybe I am asking too much from newbies... One thing for sure - it may be the right time for a new edition :-)