Home » SQL & PL/SQL » SQL & PL/SQL » Calling procedure from application that only supports SQL
Calling procedure from application that only supports SQL [message #284423] Thu, 29 November 2007 11:14 Go to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hello,

I have just found a solution to a problem I faced, and since I haven't found that way of doing it anywhere I thought I post it here for reference.

Problem was :

We have an legacy application which only can execute SQL statements against the database, there is no way of executing procedures directly. But we needed it to execute complex procedures at specific points.

Take for example this simple procedure with one in and one out parameter :

CREATE OR REPLACE PROCEDURE test_proc 
  (p_in IN VARCHAR2, p_out OUT VARCHAR2)
IS
  
BEGIN
   p_out := Lower(p_in);
END;
/


To call that over an SQL only driver I have created an global temporary table with the IN and OUT parameters as columns, and a trigger that executes the procedure on insert :

CREATE GLOBAL TEMPORARY TABLE gt_tab (
  in_param  VARCHAR2(10),
  out_param VARCHAR2(10)
)
ON COMMIT delete ROWS;


CREATE OR REPLACE TRIGGER trig_gt_tab before
insert ON gt_tab
FOR EACH ROW
BEGIN
    test_proc(:NEW.in_param, :NEW.out_param);
END;
/


Now the procedure can be called with an insert, and the result selected with an select :

SQL> INSERT INTO gt_tab VALUES('YADDA',NULL);

1 row created.

SQL> SELECT out_param FROM gt_tab;

OUT_PARAM
----------
yadda

SQL>


Regards,

Thomas

Re: Calling procedure from application that only supports SQL [message #284452 is a reply to message #284423] Thu, 29 November 2007 13:21 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Nice.

An alternative that comes to mind is to select (function) from dual, where function returns a complex type.
Re: Calling procedure from application that only supports SQL [message #284455 is a reply to message #284452] Thu, 29 November 2007 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I also thought avout this but only works with many limitations:
- must return a single value (ok, multiple values is possible with types but harder to manage in SQL)
- restrictions on operations that can be done inside the function (no DML and so on, unless you use autonomous transaction but then danger).

Storing the result in temp table is nice.

Regards
Michel

Edit: Ooops, didn't see the last part of your sentence "where function returns a complex type".

[Updated on: Thu, 29 November 2007 13:46]

Report message to a moderator

Re: Calling procedure from application that only supports SQL [message #284459 is a reply to message #284455] Thu, 29 November 2007 14:37 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Good point about the DML-restriction.

In my opinion there is no difference in complexity between using a SQL-type or using the GTT. In both cases you have to explicitly define what you expect to be returned.
Previous Topic: Oracle dbms_job.submit
Next Topic: Column Addition and Update on Big table
Goto Forum:
  


Current Time: Sun Nov 03 05:58:46 CST 2024