Multiple bind parameters

A recent otn forum post (https://community.oracle.com/message/13592114#13592114) started a discussion how to write correctly binded dynamic sql.

Problem

If there a several parameters which influence the WHERE condition of a dynamic SQL statement, then it is not so easy to consider all of them with bind variables. A typical scenario is that, if some of the parameters are NULL then they are not in the where clause. However this changes the number (and the order) of the binded values.

Solution

A simple workaround is to keep track of the number of parameters and have several execute immediate statements prepared with a different number of parameters.

Code template

create or replace
procedure doSQL (p1 in number default null
               , p2 in number default null
               , p3 in number default null
               )
is
  type params_t is table of number;  

  v_sql varchar2(4000);
  v_no_of_binds binary_integer := 0;
  v_bind_params params_t ;   

  procedure addParam(p_sql in out nocopy varchar2,p_no_of_binds in out nocopy binary_integer) is
  begin
    p_no_of_binds := p_no_of_binds +1;
    if  p_no_of_binds = 1 then
       p_sql := p_sql || ' where ';
    else
       p_sql := p_sql || ' and ';
    end if;
  end addParam;
begin
  -- BUild the SQL
  v_sql := 'select * from emp';  

  -- Add parameters if they are not null
  if p1 is not null then
      addParam(v_sql,v_no_of_binds);
      v_sql := v_sql || ' deptno = :'||to_char(v_no_of_binds);
      v_bind_params(v_no_of_binds) := p1;
  end if;  

  if p2 is not null then
    addParam(v_sql,v_no_of_binds);
    v_sql := v_sql || ' empno = :'||to_char(v_no_of_binds);
    v_bind_params(v_no_of_binds) := p2;
  end if;  

  if p3 is not null then
    addParam(v_sql,v_no_of_binds);
    v_sql := v_sql || ' sal >= :'||to_char(v_no_of_binds);
    v_bind_params(v_no_of_binds) := p3;
  end if;  

  --- do the statement
  if v_no_of_binds = 0 then
    execute immediate v_sql;
  elsif v_no_of_binds = 1 then
    execute immediate v_sql using v_bind_params(1);
  elsif v_no_of_binds = 2 then
    execute immediate v_sql using v_bind_params(1), v_bind_params(2);
  elsif v_no_of_binds = 3 then
    execute immediate v_sql using v_bind_params(1), v_bind_params(2), v_bind_params(3);
  end if;
end doSQL;
/

Feel free to reuse this as a template for your own version of dynamic sql with binds.

Discussion

For such a simple example it is better to have three different versions of the SQL availaible and use that in a non-dynamic way. But as the number over parameters increases, then the number of SQL statements increases in an exponential way (all permutations for all parameters).

Datatypes are also difficult to consider. It might make sense to build groups of parameters that all have the same datatype. Or to somehow map the paramters to varchar and bin the varchar value. This can create new issues (nls problems, code injects, etc.).

Apex 5 – Region with syntax highlighting

Apex 5 uses syntax highlighting inside the builder application in various parts. Here is how to use syntax highlighting in your own apex application.

Intro

The task was to show plsql code directly from the database on some apex page. In older Apex version I used code mirror to do this. After the upgrade to Apex 5 this didn’t work anymore. The reason was simple: Apex 5 now has a newer version of code mirror included. The path had changed, but also some implementation details were changed. Apex5 has code mirror version 4.4 included. Previous versions only had code mirror 0.66 included. I later added version 3.6 myself.

Code Mirror is installed already in the library folder inside the image path. It has a lib and a mode subfolder, which are both needed later on.

...\images\libraries\codemirror\4.4\lib
...\images\libraries\codemirror\4.4\mode

Assuming we have a text area that holds “code” and now the task is to make this code better readable by adding syntax highlighting. This is where code mirror can be used. Code mirror also has a full blown editor, but how to enable that one is not shown here.

Example

text input:

Result after adding codemirror plsql syntax highlighting

How to do it?

Step 1 – add codemirror lib to the page

We just need to add a little Javascript and css to make this work:

On the page level we need the add the codemirror library. It is a simple javascript and css reference.

Page: Javascript File Urls

#IMAGE_PREFIX#libraries/codemirror/4.4/lib/codemirror.js
#IMAGE_PREFIX#libraries/codemirror/4.4/mode/sql/sql.js
// #IMAGE_PREFIX#libraries/codemirror/4.4/mode/javascript/javascript.js

 

And also the css
#IMAGE_PREFIX#libraries/codemirror/4.4/codemirror-custom.min.css

Step 2 – add dynamic action

Additionally we add a small function that will do the change.

doSyntaxHighlighting(this.affectedElements[0],1);

This function is triggered via a dynamic action call on page load.

It refers to the affected elements section of the dynamic action event. This affected element is the text area that holds the code.

The function doSyntaxHighlighting is declared on page level as a custom function.

function doSyntaxHighlighting(sqltextareaobject,startLineNo) {
  var lEditor = CodeMirror.fromTextArea(sqltextareaobject, {
    autofocus:false,
    lineNumbers:false,
    firstLineNumber:startLineNo,
    readOnly:true,
    lineWrapping:true
  });
  return true;
};

 

Step 3 – choose syntax mode

Code mirror allows syntax highlighting for many many different languages. Each language is called a “mode”. The list of modes can be looked up in the library path under:  “#IMAGE_PREFIX#libraries/codemirror/4.4/mode/…”.

Some examples from other languages (javascript, sql, erlang):

To install a different language the appropriate language.js file needs to be referenced.

For example if you want javascript syntax, then you need to load the javascript.js or the javascript.min.js from the mode/javascript folder:

#IMAGE_PREFIX#libraries/codemirror/4.4/mode/javascript/javascript.min.js

Not all languages have a minified script version. Some languages, like SQL, have different dialects installed. These sub-languages are identified by their mime type.

Check http://codemirror.net/mode/sql/index.html to see what sql dialects are available.

The code mirror function .fromTextArea can be called with such a mime-type as the mode parameter. To use plsql syntax highlightling we need to load the sql mode and also call the method using the text/x-plsql mime type.

function doSyntaxHighlighting(sqltextareaobject,startLineNo) {
  var lEditor = CodeMirror.fromTextArea(sqltextareaobject, {
    autofocus:false,
    lineNumbers:false,
    firstLineNumber:startLineNo,
    readOnly:true,
    lineWrapping:true,
    mode: "text/x-plsql"
  });
  return true;
};

The plsql syntax is based upon some enhancements made by Peter Raganitsch, that made it into the newer code mirror version. However depending on your database version not all keywords might be included.

Step 4 – optimizing output view

The text area is usally too small. This small inline css on page level helps to get a larger text area. The settings on the original text area do not help, because code mirror adds its own div and hides the original html item.

.CodeMirror {
height: auto;
}

Summary

It is extremly easy to add some kind of syntax highlighting. Code mirror also allows to add some sophisticated features, such as line numbers. It is worth to investigate this in some more detail.

How to fetch plsql code from the database is a different topic.

For the moment we just take it for granted that there is a on load process which puts text into a page item of type text area.