Calling Stored Procedures using plain SQL – For when SQL is allowed but calls to Stored Procedures are not

While investigating the relatively new EJB 3.0 Persistence API, I came under the impression that the specification does not cater for Stored Procedures. In general, there seem to be more instances when plain SQL – ordinary SELECT statements against tables and views – are supported but calls to Stored Procedures, written for example in PL/SQL for Oracle Databases, are not. All Object Relational Mapping frameworks for Java/RDBMS bridging for example support plain SQL, but are limited for use of PL/SQL or other stored languages. At any rate, being able to invoke a stored procedure through ordinary select statements is usually much easier to implement.

In this article, I will look at creating wrappers for PL/SQL procedures that allow them to be invoked from external environments, for example Java Applications using EJB 3.0 Persistence or other ORM frameworks that do not readily cater for CallableStatements to stored procedures. It turns out to be relatively simple to implement – do not expect anything spectacular. Yet the apporach discussed can be useful in certain situations.

Note: I am still trying to find out whether the EJB 3.0 Persistence API does indeed exclude CallableStatements and Stored Procedures; for the time being – after having carefully read the specs – that has to be my assumption. The EntityManager interface does also not include a getConnection() method that would allow us to create a JDBC CallableStatement directly on the JDBC connection that the EntityManager itself is also using.

We will work with a very, somewhat unnecessary, PL/SQL procedure abs_date_diff. It takes two dates as input parameters and returns the difference in years, months and days between the two dates in three numeric out parameters:

create or replace 
procedure abs_date_diff
( p_date1 in date
, p_date2 in date
, p_years out number
, p_months out number
, p_days out number
) is
  l_months number;
  l_date   date;
begin
  l_months:= months_between( p_date1, p_date2);
  p_years := trunc(abs(l_months)/12);
  p_days:= (abs(l_months) - trunc(abs(l_months)))*31;
  p_months := trunc(abs(l_months) - (12*p_years));
end abs_date_diff;
/

If we want to call this procedure, clearly we cannot do so using a simple SELECT statement: we can only call PL/SQL functions in select statements, and we can not deal with out-parameters if it was a function we were dealing with.

 

Wrapper Function

I create a wrapper function, called FUN_ABS_DATE_DIFF. This function takes the same input parameters as the procedure it wraps. It returns a numeric value – the result, typically 0. It also stores the values of the out-parameters in a package called proc_results:

create or replace
function fun_abs_date_diff
( p_date1 in date
, p_date2 in date
) return number
is
 l_years number;
 l_months number;
 l_days number;
begin
  abs_date_diff
  ( p_date1
  , p_date2
  , l_years
  , l_months
  , l_days
  );
  proc_results.set_years(l_years);
  proc_results.set_months(l_months);
  proc_results.set_days(l_days);
  return 0;
end fun_abs_date_diff;
/

This package looks somewhat like a JavaBean with three properties and corresponding getters and setters. The package is called from the wrapper function to set the three properties. Later on, it can be called to retrieve the results of the original procedure call. This query would look like:

select proc_results.get_years
,      proc_results.get_months
,      proc_results.get_days
from   dual
where  fun_abs_date_diff( to_date('30-10-2000','DD-MM-YYYY'), sysdate ) = 0
/

 GET_YEARS GET_MONTHS   GET_DAYS
---------- ---------- ----------
         5          2         24

Now we have created a situation where executing a simple SQL SELECT statement returns the result of a PL/SQL Procedure call! This same select statement can of course be used inside an Oracle ADF BC ViewObject definition – using one or two bind-parameters for the values that we want to pass to the PL/SQL procedure. We can also use this Select Statement as NativeQuery in EJB 3.0 Persistence:

 String fromDate = "30-10-2000";
 Query q = myEntityManager.createNativeQuery("select proc_results.get_years\n" + 
              ",      proc_results.get_months\n" + 
              ",      proc_results.get_days\n" + 
              "from   dual\n" + 
              "where  fun_abs_date_diff( to_date('"+ fromDate +"','DD-MM-YYYY'), sysdate ) = 0\n"
              );
 Vector theResult = (Vector)q.getSingleResult();
 System.out.println("Tobias is now exactly "+theResult.get(0)+" years, "+theResult.get(1)+ " months and "+ theResult.get(2)+" days old.");

…
Tobias is now exactly 5 years, 2 months and 24 days old.

Note that Named Parameters are not supported for Native Queries in the specifications. With regard to positional parameters, the specification states: “The use of named parameters is not defined for native queries. Only positional parameter binding for SQL queries may be used by portable applications.” In my current build of the GlassFish Reference Implementation, I could not get the positional parameters to work either, hence the hard-coded values.

Of course if we were to define an Entity with properties Years, Months, Days, then we could even call the PL/SQL procedure through a normal EJB QL query. That seems like abuse though.

Alternative approach using Table Functions

There is another way of wrapping the PL/SQL procedure. It takes about the same effort, so I cannot really recommend one approach over the other. Using the Table Functions is perhaps a bit more elegant, but I am not yet sure of that.

With Table Functions, we are moving the function call from the Where Clause – which is not particularly the appropriate spot – to the From Clause:

select abs_date_diff.*
from   table
       ( tblfun_abs_date_diff
         ( to_date('30-10-2000','DD-MM-YYYY')
         , sysdate 
         )
       ) abs_date_diff
/

     YEARS     MONTHS       DAYS
---------- ---------- ----------
         5          2         24

 

In order to get this to work, we need to set up the Table Function itself and two Type definitions in the Oracle Database. We do not need the package proc_results, which is good thing. The table function tblfun_abs_date_diff looks as follows:

create or replace
function tblfun_abs_date_diff
( p_date1 in date
, p_date2 in date
) return abs_date_diff_tbl
is
 l_years number;
 l_months number;
 l_days number;
 l_abs_date_diff_tbl abs_date_diff_tbl := abs_date_diff_tbl();
begin
  abs_date_diff
  ( p_date1
  , p_date2
  , l_years
  , l_months
  , l_days
  );
  l_abs_date_diff_tbl.extend();
  l_abs_date_diff_tbl(l_abs_date_diff_tbl.last):= abs_date_diff_type(l_years, l_months, l_days);
  return l_abs_date_diff_tbl; 
end tblfun_abs_date_diff;
/

The two type definitions are pretty straightforward:

< >create or replace 
type abs_date_diff_type
is object
( years  number(4)
, months number(2)
, days   number(2)
)
/

create or replace
type abs_date_diff_tbl
is
table of abs_date_diff_type
/
 >

We can invoke the Table Function in the same way from Java as we called the Wrapper FU
N_
ABS_DATE_DIFF function:

String fromDate = "30-10-2000";
 Query q = libraryClient.getEntityManager().createNativeQuery("select abs_date_diff.*\n" + 
 "from   table( tblfun_abs_date_diff( to_date('"+fromDate+"','DD-MM-YYYY'), sysdate )) abs_date_diff\n" 
              );
 Vector theResult = (Vector)q.getSingleResult();
 System.out.println("Tobias is now exactly "+theResult.get(0)+" years, "+theResult.get(1)+ " months and "+ theResult.get(2)+" days old.");

Yet another approach… Instead Of triggers with INSERT or UPDATE statements

At the risk of getting too exotic on this issue… The INSTEAD-OF triggers that Oracle allows us to define for Views are another vehicle for translating SQL statement – DML operations like INSERT and UPDATE in the case of IOTs – into PL/SQL calls. The values we set for the columns in the insert or update statement – something like update iot_abs_date_diff set date1=to_date(’30-10-2000′,’DD-MM-YYYY’), date2=sysdate – can be read inside the IOT and used to make any PL/SQL call. However, we would then find a way to return the values to the caller. The route via the PROC_RESULTS package seems to be the only available one: we cannot set :new.years to a certain value inside the IOT and read that value using the returning YEARS into :years. The SQL engine does not allow that construction. So that UPDATE statement that triggers the IOT into calling a PL/SQL stored procedure needs to be followed by a separate SELECT statement that returns the values set in the package.

In code this would look something like the following (note: we can make this code far more generic):

First we create a dummy table as Oracle does not allow the update or insert of virtual columns (create view x as select ” in1 from dual followed by update x set in1 = value results in ORA-01733: virtual column not allowed here):

create table dul
( procedure_name varchar2(100)
, in1 varchar2(100)
, in2 varchar2(100)
, in3 varchar2(100)
)
/

insert into dul
values
( 'X','X','X','X')
/

Next we create a view on top of this table. Note that we make use of the proc_results package that we created earlier in this article. Again, we could make this more generic, using functions like get_out(1) or get_number_result(1) instead of get_years. We also create the Instead of Trigger for this view:

create or replace 
view procedure_call
as
select procedure_name
,      in1
,      in2
,      in3
,      proc_results.get_years
,      proc_results.get_months
,      proc_results.get_days
from   dul
/

create or replace
TRIGGER ioT_procedure_call
INSTEAD OF UPDATE OR INSERT
ON procedure_call
FOR EACH ROW
declare
 l_years number;
 l_months number;
 l_days number;
begin
  abs_date_diff
  ( to_date(:new.in1)
  , to_date(:new.in2)
  , l_years
  , l_months
  , l_days
  );
  proc_results.set_years(l_years);
  proc_results.set_months(l_months);
  proc_results.set_days(l_days);
END ioT_procedure_call;
/

Now we can make the procedure call by either inserting into or updating view PROCEDURE_CALL:

update procedure_call
set    procedure_name = 'ABS_DATE_DIFF'
,      in1 = to_date('30-10-2000','DD-MM-YYYY')
,      in2 = sysdate
/

insert into procedure_call
(procedure_name, in1, in2)
values
('ABS_DATE_DIFF'
, to_date('30-10-2000','DD-MM-YYYY')
, sysdate
)
/

The results are the same in both instances:

select *
from   procedure_call
/
 PROCEDURE_NAME
----------------------------------------
IN1
----------------------------------------
IN2
----------------------------------------
IN3
----------------------------------------
 GET_YEARS GET_MONTHS   GET_DAYS
---------- ---------- ----------
X
X
X
   5          2         24

And another… with Fine Grained Auditing

< >>


For some hopeful moments I considered another scenario: a select-statement triggers a Fine Grained Auditing trigger (also see Select Trigger in Oracle Database – introducing Fine Grained Auditing); this trigger reads the input values from the where clause, makes the PL/SQL call and sets the return results in package variables. In code that would be something like:

select audit_handler.get_out1
,      audit_handler_get_out2
from   dual
where  'in1=first_value'
and    'in2=second_value'
/

We would define an audit policy on DUAL that invokes a audit policy function in the audit_handler package. Something like:

create or replace 
package AUDIT_HANDLER
is

function get_call_time
return timestamp
;

PROCEDURE HANDLE_PROCEDURE_CALL
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2 
);
end;
/

create or replace 
package body AUDIT_HANDLER
is

g_timestamp timestamp;

function get_call_time
return timestamp
is
begin
  return g_timestamp;
end;


PROCEDURE HANDLE_PROCEDURE_CALL
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2 
) is
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  -- find current SQL in sys_context('userenv','current_sql')
  g_timestamp:= systimestamp;
end HANDLE_PROCEDURE_CALL;
end;
/

In this example we have not yet bothered with the in# en out# values; I just wanted to see whether anything the select policy function does can actually influence the result of the query. I activated the audit policy:

begin 
  dbms_fga.add_policy 
  ( object_schema=>'SCOTT'
  , object_name=>'PROCEDURE_CALL' 
  , policy_name=>'CALL_BROKER'
  , handler_schema => 'SCOTT'
  , handler_module => 'AUDIT_HANDLER.HANDLE_PROCEDURE_CALL' 
  );
end;
/

instead of DUAL we have used another object: VIEW PROCEDURE_CALL:

create or replace 
view procedure_call
as
select '' procedure_name
,      '' in1
,      '' in2
,      '' in3
,      '' out1
,      '' out2
,      '' out3
,      AUDIT_HANDLER.get_call_time
from   dual
/

and attempted a query:

select * 
from   procedure_call
/
P I I I O O O
- - - - - - -
GET_CALL_TIME
---------------------------------------------------------------------------


1 row selected.

The result was disappointing: no value was returned. So it seems that the Audit Policy Function executes very much on its own. No connection, not even through session settings such as global package variables, with the query execution environment. We can discard this idea.

Summary

If for some reason you want to call a PL/SQL procedure, that takes multiple input parameters and returns one or more out-parameters, and you have to make this call through an interface or framework that does not (easily) allow calls to stored procedures, you can easily create wrappers that make it possible to execute a simple SELECT statement that indirectly makes the PL/SQL call. ORM Frameworks and technologies such as EJB 3.0 Persistence and Oracle ADF Business Components (ViewObjects) are examples where executing queryies is (much) easier that calling PL/SQL procedures

 

Resources

Here is the code for the proc_results package:

create or replace package proc_results
is

  function get_years
  return number
  ;
  procedure set_years
  ( p_years in number
  );
  function get_months
  return number
  ;
  procedure set_months
  ( p_months in number
  );
  function get_days
  return number
  ;
  procedure set_days
  ( p_days in number
  );
end proc_results;
/

create or replace package body proc_results
is
  g_years number(4);
  g_months number(2);
  g_days number(2);

  function get_years
  return number
  is
  begin
    return g_years;
  end get_years;

  procedure set_years
  ( p_years in number
  ) is
  begin
    g_years := p_years;
  end set_years;


  function get_months
  return number
  is
  begin
    return g_months;
  end get_months;


  procedure set_months
  ( p_months in number
  ) is
  begin
    g_months := p_months;
  end set_months;

  function get_days
  return number
  is
  begin
    return g_days;
  end get_days;

  procedure set_days
  ( p_days in number
  ) is
  begin
    g_days := p_days;
  end set_days;

end proc_results;
/