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.

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.

Hi, I'm Jorge Rimblas. Father, husband, photographer, Oraclenerd, Oracle APEX expert, Oracle ACE, coffee lover, car guy, gadget addict, etc... I'm an APEX Tech Lead DRW. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.

16 Comments on “12c WITH inline PL/SQL, json_from_sql, Bind Variables and AJAX

  1. Interesting read, Jorge. I wasn’t aware that the invocation of a PL/SQL function in SQL gets treated as a bind variable. That helps a lot in dealing with SQL injection.

    Thanks again for the crisp explanation. I’ll refer to this article in my blog post.

  2. And to help take advantage of performance benefits when migrating in future, you can apply the UDF pragma to the param function in 12c. This will also help with maintainability and reusability of the functions used in WITH clauses.

    • Christian, I didn’t know V could be used to get those values (or if I ever did I forgot). Very handy! I’ve made some updates to the post with your addition. Thank you!

  3. Thank you for sharing excellent information. Your website is very cool. I am impressed by the details that you have on this blog. It reveals how nicely you understand this subject. Bookmarked this website page, will come back for more articles. You ROCK! I found just the info I already searched everywhere and just could not come across. What a perfect site.

Leave a Reply to albertro Cancel reply