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

16

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 <br />procedure abs_date_diff<br />( p_date1 in date<br />, p_date2 in date<br />, p_years out number<br />, p_months out number<br />, p_days out number<br />) is<br />  l_months number;<br />  l_date   date;<br />begin<br />  l_months:= months_between( p_date1, p_date2);<br />  p_years := trunc(abs(l_months)/12);<br />  p_days:= (abs(l_months) - trunc(abs(l_months)))*31;<br />  p_months := trunc(abs(l_months) - (12*p_years));<br />end abs_date_diff;<br />/<br />&nbsp;

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<br />function fun_abs_date_diff<br />( p_date1 in date<br />, p_date2 in date<br />) return number<br />is<br /> l_years number;<br /> l_months number;<br /> l_days number;<br />begin<br />  abs_date_diff<br />  ( p_date1<br />  , p_date2<br />  , l_years<br />  , l_months<br />  , l_days<br />  );<br />  proc_results.set_years(l_years);<br />  proc_results.set_months(l_months);<br />  proc_results.set_days(l_days);<br />  return 0;<br />end fun_abs_date_diff;<br />/ <br />

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<br />,      proc_results.get_months<br />,      proc_results.get_days<br />from   dual<br />where  fun_abs_date_diff( to_date('30-10-2000','DD-MM-YYYY'), sysdate ) = 0<br />/<br /><br /> GET_YEARS GET_MONTHS   GET_DAYS<br />---------- ---------- ----------<br />         5          2         24<br />&nbsp;

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 = &quot;30-10-2000&quot;;<br /> Query q = myEntityManager.createNativeQuery(&quot;select proc_results.get_years\n&quot; + <br />              &quot;,      proc_results.get_months\n&quot; + <br />              &quot;,      proc_results.get_days\n&quot; + <br />              &quot;from   dual\n&quot; + <br />              &quot;where  fun_abs_date_diff( to_date('&quot;+ fromDate +&quot;','DD-MM-YYYY'), sysdate ) = 0\n&quot;<br />              );<br /> Vector theResult = (Vector)q.getSingleResult();<br /> System.out.println(&quot;Tobias is now exactly &quot;+theResult.get(0)+&quot; years, &quot;+theResult.get(1)+ &quot; months and &quot;+ theResult.get(2)+&quot; days old.&quot;);<br /><br /><p>...<br />Tobias is now exactly 5 years, 2 months and 24 days old.&nbsp;</p>

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.*<br />from   table<br />       ( tblfun_abs_date_diff<br />         ( to_date('30-10-2000','DD-MM-YYYY')<br />         , sysdate <br />         )<br />       ) abs_date_diff<br />/<br /><br />     YEARS     MONTHS       DAYS<br />---------- ---------- ----------<br />         5          2         24<br /><br /><p>&nbsp;</p>

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<br />function tblfun_abs_date_diff<br />( p_date1 in date<br />, p_date2 in date<br />) return abs_date_diff_tbl<br />is<br /> l_years number;<br /> l_months number;<br /> l_days number;<br /> l_abs_date_diff_tbl abs_date_diff_tbl := abs_date_diff_tbl();<br />begin<br />  abs_date_diff<br />  ( p_date1<br />  , p_date2<br />  , l_years<br />  , l_months<br />  , l_days<br />  );<br />  l_abs_date_diff_tbl.extend();<br />  l_abs_date_diff_tbl(l_abs_date_diff_tbl.last):= abs_date_diff_type(l_years, l_months, l_days);<br />  return l_abs_date_diff_tbl; <br />end tblfun_abs_date_diff;<br />/<br />&nbsp;

The two type definitions are pretty straightforward:

&lt; <code>&gt;create or replace <br />type abs_date_diff_type<br />is object<br />( years  number(4)<br />, months number(2)<br />, days   number(2)<br />)<br />/<br /><br />create or replace<br />type abs_date_diff_tbl<br />is<br />table of abs_date_diff_type<br />/<br />&nbsp;&gt;</code>

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 = &quot;30-10-2000&quot;;<br /> Query q = libraryClient.getEntityManager().createNativeQuery(&quot;select abs_date_diff.*\n&quot; + <br /> &quot;from   table( tblfun_abs_date_diff( to_date('&quot;+fromDate+&quot;','DD-MM-YYYY'), sysdate )) abs_date_diff\n&quot; <br />              );<br /> Vector theResult = (Vector)q.getSingleResult();<br /> System.out.println(&quot;Tobias is now exactly &quot;+theResult.get(0)+&quot; years, &quot;+theResult.get(1)+ &quot; months and &quot;+ theResult.get(2)+&quot; days old.&quot;);<br />&nbsp;

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<br />( procedure_name varchar2(100)<br />, in1 varchar2(100)<br />, in2 varchar2(100)<br />, in3 varchar2(100)<br />)<br />/<br /><br />insert into dul<br />values<br />( 'X','X','X','X')<br />/<br />

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 <br />view procedure_call<br />as<br />select procedure_name<br />,      in1<br />,      in2<br />,      in3<br />,      proc_results.get_years<br />,      proc_results.get_months<br />,      proc_results.get_days<br />from   dul<br />/<br /><br />create or replace<br />TRIGGER ioT_procedure_call<br />INSTEAD OF UPDATE OR INSERT<br />ON procedure_call<br />FOR EACH ROW<br />declare<br /> l_years number;<br /> l_months number;<br /> l_days number;<br />begin<br />  abs_date_diff<br />  ( to_date(:new.in1)<br />  , to_date(:new.in2)<br />  , l_years<br />  , l_months<br />  , l_days<br />  );<br />  proc_results.set_years(l_years);<br />  proc_results.set_months(l_months);<br />  proc_results.set_days(l_days);<br />END ioT_procedure_call;<br />/<br /><br />

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

update procedure_call<br />set    procedure_name = 'ABS_DATE_DIFF'<br />,      in1 = to_date('30-10-2000','DD-MM-YYYY')<br />,      in2 = sysdate<br />/<br /><br />insert into procedure_call<br />(procedure_name, in1, in2)<br />values<br />('ABS_DATE_DIFF'<br />, to_date('30-10-2000','DD-MM-YYYY')<br />, sysdate<br />)<br />/<br />

The results are the same in both instances:

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

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<br />,      audit_handler_get_out2<br />from   dual<br />where  'in1=first_value'<br />and    'in2=second_value'<br />/<br /><br />

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

create or replace <br />package AUDIT_HANDLER<br />is<br /><br />function get_call_time<br />return timestamp<br />;<br /><br />PROCEDURE HANDLE_PROCEDURE_CALL<br />( object_schema VARCHAR2<br />, object_name VARCHAR2<br />, policy_name VARCHAR2 <br />);<br />end;<br />/<br /><br />create or replace <br />package body AUDIT_HANDLER<br />is<br /><br />g_timestamp timestamp;<br /><br />function get_call_time<br />return timestamp<br />is<br />begin<br />  return g_timestamp;<br />end;<br /><br /><br />PROCEDURE HANDLE_PROCEDURE_CALL<br />( object_schema VARCHAR2<br />, object_name VARCHAR2<br />, policy_name VARCHAR2 <br />) is<br />  PRAGMA AUTONOMOUS_TRANSACTION;<br />begin<br />  -- find current SQL in sys_context('userenv','current_sql')<br />  g_timestamp:= systimestamp;<br />end HANDLE_PROCEDURE_CALL;<br />end;<br />/<br /><br />

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 <br />  dbms_fga.add_policy <br />  ( object_schema=&gt;'SCOTT'<br />  , object_name=&gt;'PROCEDURE_CALL' <br />  , policy_name=&gt;'CALL_BROKER'<br />  , handler_schema =&gt; 'SCOTT'<br />  , handler_module =&gt; 'AUDIT_HANDLER.HANDLE_PROCEDURE_CALL' <br />  );<br />end;<br />/<br /><br />

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

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

and attempted a query:

select * <br />from   procedure_call<br />/<br />P I I I O O O<br />- - - - - - -<br />GET_CALL_TIME<br />---------------------------------------------------------------------------<br /><br /><br />1 row selected.<br />

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;
/
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

16 Comments

  1. writing functions are good
    they are using with SQL
    but if u give how to establishing using hibernate Query language

  2. i’m newbie in Oracle so can you help me about my stuck ?
    In a java servlet
    i want insert a row to table A(ID,Name) and ID is sequence number so i want to get ID of inserted row to a String variable in Java
    Here is my function :

    FUNCTION createPerson(varNAME VARCHAR2)
    RETURN VARCHAR2
    IS
    varID VARCHAR2(25);
    BEGIN
    INSERT INTO Person(ID,NAME) VALUES (TO_CHAR(person_seq.nextval),varNAME)
    RETURNING ID INTO varID;
    RETURN varID;
    END;

    In Java :
    String personID = “”;
    but i don’t know how to get the return value of createPerson to personID

    Thanks for ASAP your answer

  3. Hi all,

    I would like to map an Oracle Stored Procedure that return, as an output parameter, one ResultSet, how can i do it? I’m using Hibernate and or iBatis.
    And what about procedures that return multiple ResultSets?

    Thanks.

  4. i want to know that how can we call function in sql prompt(sql*plus) having out parameter.

    thanks

  5. hi all
    I am a kinaa newbie with Hibernate. I have got everything else working
    with hibernate pretty neatly. But now I need to call a stored
    procedure for something :-( I tried to read this howto, but it is
    still not very clear on how I can do this. Would really appreciate if
    someone can help. I dont want to write JDBC and DB access code just
    for this stored procedure

  6. Dear Manikantha,

    Yes, you can. You need to add a semi-colon to the end of the insert-statement:
    Begin

    insert into emp(empno,ename,deptno) select empno,ename,deptno from emp1;
    end;

    and you have a correct PL/SQL block.

    Lucas

  7. Manikantha varma on

    Hi,
    i have one question

    can i write a statement in pl/sql stored pocedure in executable block

    Begin

    insert into emp(empno,ename,deptno) select empno,ename,deptno from emp1
    end;
    /

  8. Broderick Ellis on

    Lucas,

    This solution really helps me with an integration project that I’m currently working on,
    but I’m no PL/SQL expert. Can you provide me with the proc_results package.

    Thanks,
    Broderick

  9. Emmanuel, I am not primarily interested in database portability – yuou;re right that having iomplemented tons of functionality in PL/SQL it is not a logical step to go to SQL Server. However, I am interested in ORM framework portability. For me, one of the benefits of EJB 3.0 Persistence would be the generic API that allows me to code without knowledge of the actual Persistency technologie.

    If I have to use vendor specific tricks to access the stored procedures, I sacrifice that portability that I was hoping to get from EJB 3.0 Persistence. Unless of course all vendors use the same tricks.

  10. It’s not that bad:
    – this is available through a hint which is a standard way to interact with you persistence provider, no need to call a proprietary API
    – you’re using a proprietary technology underneath (your stored procs), so it’s likely that you don’t care that much about 100% full portability

  11. I had read something about this in the Hibernate docs. Of course, this is proprietary Hibernate behavior. I am looking for an approach that is portable across ORM imlementations of EJB 3.0 Persistence. I am quite surethat Oracle Toplink will have similar but different support for Callable Statements.

  12. I haven’t tested it, but Hibernate EntityManager supports Callable statements
    and then stored procedures through the org.hibernate.callable hint, using
    plain EJB3 APIs.