Calling procedure from application that only supports SQL [message #284423] |
Thu, 29 November 2007 11:14 |
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 #284455 is a reply to message #284452] |
Thu, 29 November 2007 13:45 |
|
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
|
|
|
|