You may be thinking I made a typo in the posts heading, however it’s intentional. Yesterday evening was an eye opener for me and the discovery of being able to nest “EXECUTE IMMEDIATE” statements and return variables up the stack was the highlight.

It all started when we had a requirement in one of our applications to enable cascading LOV support for a popup LOV in our Ext integration kit. With a little tweaking it wasn’t to hard to implement based on our existing design, however for the popup LOV, as we used pagination in it, we had our own PLSQL handler routine to execute the query. Problem was that it didn’t handle binds, well not entirely true, part of it did as we used:

apex_util.json_from_sql(sqlq=>v_sql_exec,p_sub=> 'Y');

However we needed to grab the total row count to enable pagination so we simply did the following:

EXECUTE IMMEDIATE 'begin select count(*) INTO :b1 FROM ('||v_sql_count||'); end;' USING OUT v_totalRows;

Which didn’t support binds. I liked the idea of still using EXECUTE IMMEDIATE and wasn’t too keen on using DBMS_SQL as all I really wanted was the return of a single value. So I started looking at APEX API’s and Patrick Wolf’s APEXLIB framework for inspiration, and it was within Patrick’s code that I found my answer, well part of it:

  PROCEDURE executePlSqlCode
    ( pPlSqlCode IN VARCHAR2
    )
  IS
      vBindVariables VARCHAR2(2000);
  BEGIN
      ----------------------------------------------------------------------------
      -- Get bind variables.
      ----------------------------------------------------------------------------
      vBindVariables := WWV_Flow_Utilities.get_using_clause(pPlSqlCode);
      ----------------------------------------------------------------------------
      -- Execute pl/sql code.
      ----------------------------------------------------------------------------
      debug('...execute = '||pPlSqlCode);
      --
      IF vBindVariables IS NOT NULL
      THEN
          EXECUTE IMMEDIATE
              'BEGIN' || CR ||
              '    EXECUTE IMMEDIATE ''' ||
              REPLACE(REPLACE(pPlSqlCode, CHR(13), CHR(10)), '''', '''''') || ''''|| CR ||
              '    ' || vBindVariables || ';'                                     || CR ||
              'END;';
      ELSE
          EXECUTE IMMEDIATE REPLACE(pPlSqlCode, CHR(13), CHR(10));
      END IF;
      --
  END executePlSqlCode;

I never realized that you could nest EXECUTE IMMEDIATE statements, but should have since you can nest PLSQL blocks it’s simply just another Oracle statement (just highlighting my inexperience). So this revelation got me thinking…… I looked a bit further in APEXLIB and saw that Patrick did a BULK collect e.g. “BULK COLLECT INTO WWV_Flow_Utilities.g_display, WWV_Flow_Utilities.g_value” however I was simply wanting to use an out variable into my current code block and I didn’t like the idea of using any PLSQL types or other package variables to hold it.

So after a bit of trial and error I came up with the following which allows you to nest EXECUTE IMMEDIATE statements and return variables up the stack:

v_totalRows PLS_INTEGER;

BEGIN
  ----------------------------------------------------------------------------
  -- Get bind variables.
  ----------------------------------------------------------------------------
  vBindVariables := regexp_replace(WWV_Flow_Utilities.get_using_clause(v_sql_count), 'using','',1,0,'i');

  apex_util.json_from_sql(sqlq=>v_sql_exec,p_sub=> 'Y');

  IF vBindVariables IS NOT NULL THEN
    EXECUTE IMMEDIATE
        'DECLARE v_totalRows PLS_INTEGER; BEGIN' ||
        '    EXECUTE IMMEDIATE ''BEGIN select count(*) INTO :b1 FROM (' || REPLACE(regexp_replace(v_sql_count, '('||CHR(13)||'|'||CHR(10)||')',' ',1,0), '''', '''''') ||'); END;'''||
        '    USING OUT v_totalRows, ' || vBindVariables || ';'||
        ' SELECT v_totalRows INTO :b1 FROM DUAL; END;' USING IN OUT v_totalRows;

  ELSE
    EXECUTE IMMEDIATE 'BEGIN select count(*) INTO :b1 FROM ('||v_sql_count||'); END;' USING OUT v_totalRows;
  END IF;
  htp.p(',"totalRows":'||v_totalRows||'}');
EXCEPTION
  WHEN VALUE_ERROR
  THEN
    htp.p('"row":[{"DISPLAY_VALUE":"No Data Found!","RETURN_VALUE":"No Data Found!"}],"totalRows":1}');
  WHEN OTHERS
  THEN
    htp.p(SQLERRM);
    --htp.p('"row":[{"DISPLAY_VALUE":"No Data Found!","RETURN_VALUE":"No Data Found!"}],"totalRows":1}');
END;

What I needed to do was build up my “USING” clause with the aid of “WWV_Flow_Utilities.get_using_clause” and concatenate it with my OUT variable v_totalRows, hence the reason why I needed to declare it inside and then do a “SELECT INTO :b1 FROM DUAL” to return it to the outer block. It does get confusing when you nest statements especially with quotes, I’m going to change it to use the new 10g “q” feature to make it more readable, and also don’t get confused by the fact that I’m using the same variable name, as they are in different scopes. Anyway I just thought I’d share this as I thought it was a very powerful way to do dynamic/dynamic SQL to return variables rather than using DBMS_SQL.