12c WITH inline PL/SQL, json_from_sql, Bind Variables and AJAX

I recently got to use a new cool feature in Oracle 12c R1, the WITH clause with inline PL/SQL (read more at oracle-base). Then Scott Wesley blogged about it and I made a comment on twitter. But don’t stop reading just yet just because you’re not using 12c.

. @swesley_perth just today I wrote a WITH function to return apex_application.g_x01 on a SQL passed to apex_util.json_from_sql Cool stuff

— Jorge Rimblas (@rimblas) December 30, 2014

Then, Nick Buytaert blogged about the new “Lazy Loading” feature on his APEX Select2 plugin (using apex_util.json_from_sql) and I made some comments on his blog post. By this point I figure it was time to do a proper blog post linking all these things together.

The WITH clause with a PL/SQL Function feature is important, heck I think it’s fantastic. However, just because you’re not on 12c yet you should dismiss the technique outlined here. So read on. First, lets define a scenario to use the functionality.  Lets say you are using the Select2 plugin and have an On-Demand process that will generate a JSON string with your matching values.  You would need something that looks like this:

{
  "row": [
    {
      "D": "KING",
      "R": 7839
    },
    {
      "D": "BLAKE",
      "R": 7698
    }
  ]
}

This is easily generated by apex_util.json_from_sql. The SQL to generate this result would like something like this:

select ename d, empno r from emp

The Select2 plugin will pass a search term to the On-Demand process and we need to use it in our SQL statement. The search term will be passed to our On-Demand Process in apex_application.g_x01. Unfortunately, we cannot (yet) use apex_application.g_x01 directly in our SQL like this (because it’s a global package variable outside of our scope):

// This is invalid!!
select ename d, empno r
  from emp
 where instr (upper(ename), upper(apex_application.g_x01)) > 0

// WARNING: This will NOT work!
l_sql := 'select ename d, empno r
  from emp
 where instr (upper(ename), upper(apex_application.g_x01)) > 0';
apex_util.json_from_sql(l_sql);

But SQL could use a PL/SQL function, so we need to wrap apex_application.g_x01 with a PL/SQL function so that we can pass it to apex_util.json_from_sql. In 12c this can now be done right in the SQL statement! Plus it comes with a performance benefit.

declare
  l_sql varchar2(4000);
begin
  l_sql := 'with function param return varchar2 is 
      begin return apex_application.g_x01; end;
    select ename d, empno r
      from emp
     where instr(upper(ename), upper(param())) > 0
  ';
  
  apex_util.json_from_sql(l_sql);
end;

But if you’re not in 12c yet, you would need to create a function (preferably inside a package) and then use in your SQL. Or, as Christian Neumueller noted in the comments, if you want to save a step just use the very handy V function with v('APP_AJAX_X01') to retrieve the value of apex_application.g_x01.

declare
  l_sql varchar2(4000);
begin
  l_sql := '
    select ename d, empno r
      from emp
     where instr(upper(ename), upper(v(''APP_AJAX_X01''))) > 0
  ';
  
  apex_util.json_from_sql(l_sql);
end;

But, please, please, whatever you do, do not concatenate the value as a string right into the SQL as that opens the door to SQL Injection. I’m not even going to write that code option here as I wouldn’t want anyone to copy paste it. :)

Borrowing a test harness from Tim Hall of oracle-base fame. Here’s a quick test of the performance difference between the new WITH function clause, a regular PL/SQL function and the V function.

SQL> create table t1 as select object_name from all_objects;

Table created.

SQL> create function param2
  return varchar2
is 
begin
  return apex_application.g_x01;
end;
/

Function created.

SQL> SET SERVEROUTPUT ON
DECLARE
  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;
  
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  
  TYPE t_tab IS TABLE OF VARCHAR2(4000);
  l_tab t_tab;
BEGIN
  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'with function param return varchar2 is 
            begin return apex_application.g_x01; end;
          select object_name
            from t1
           where instr(upper(object_name), upper(param())) > 0';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('WITH_FUNCTION   : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'select object_name
            from t1
           where instr(upper(object_name), upper(param2())) > 0';

  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('REGULAR FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');


  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'select object_name
            from t1
           where instr(upper(object_name), upper(v(''APP_AJAX_X01''))) > 0';

  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('V       FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');


END;
/
WITH_FUNCTION   : Time=5 hsecs CPU Time=6 hsecs
REGULAR FUNCTION: Time=33 hsecs CPU Time=31 hsecs
V       FUNCTION: Time=118 hsecs CPU Time=118 hsecs

PL/SQL procedure successfully completed.

Of course, your millage may vary, but I find the results very interesting. Always do your own testing and validations.

Go ahead, share...

Facebook

Twitter

Linkedin

Google

Tumblr

Buffer

Stumbleupon

Email