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:

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

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):

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.

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.

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.

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 a Senior APEX Consultant with Insum Solutions, a consulting firm specialized in Oracle databases and the APEX development tool. 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. Excellent use example of the inline PL/SQL function. Thanks for sharing!

  3. Very nice post Jorge.

  4. Scott says:

    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.

  5. Juergen Schuster says:

    Great post, just the thing I needed on the first day after my holiday :-)

  6. Hi,

    to access apex_application.g_xNN, you can also use the v function, e.g. v(‘APP_AJAX_X01’).

    • 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!

  7. roberthoot says:

    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.

  8. albertro says:

    Excellent information. thanks for sharing

  9. Greetings! I am so excited to be provided with such an essential part! This write-up is so descriptive, and I must admit that you made my day by adding this entry.

2 Pings/Trackbacks for "12c WITH inline PL/SQL, json_from_sql, Bind Variables and AJAX"
  1. […] Watch out for SQL injection in the Remote Data Process. Jorge Rimblas wrote an excellent article on how to avoid SQL injection vulnerability and improve query performance. Read more about it here. […]

  2. […] Jorge Rimblas – trivia: definitely knows how to pronounce APEX. Suggested post: 12c WITH inline PL/SQL, json_from_sql, Bind Variables and AJAX […]

I love comments, write me a line