I have an Oracle stored procedure that gets 2 parameters and returns 2 parameters (status and message).
I am making changes to this legacy application capable of only executing select statements,
My question is it possible to wrap the stored procedure with some function or other stored procedure or a view, or any other object I might not no about, so can I execute the stored procedure using simple a select statement?
Correct execution code will be something like:
DECLARE
PRINTER_ID VARCHAR2(200);
O_STATUS VARCHAR2(200);
O_MESSAGE VARCHAR2(200);
BEGIN
PRINTER_ID := '551555115';
IMPL_XEROX_PRINTER_CHECK( PRINTER_ID => PRINTER_ID, O_STATUS => O_STATUS, O_MESSAGE => O_MESSAGE );
DBMS_OUTPUT.PUT_LINE('O_STATUS = ' || O_STATUS);
DBMS_OUTPUT.PUT_LINE('O_MESSAGE = ' || O_MESSAGE);
END;
What I am trying to get is something like:
Select O_STATUS,O_MESSAGE from IMPL_XEROX_PRINTER_CHECk_WRAPPER where PRINTER_ID = '551555115';
The thing is that the SP is inserting some data to a temporary table... this is the table:
CREATE TABLE "TEST_PRNT_DATA" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE), "COLUMN3" VARCHAR2(20 BYTE) )
/
This is the stored procedure:
CREATE OR REPLACE PROCEDURE IMPL_XEROX_PRINTER_CHECK
(
PRINTER_ID IN VARCHAR2
, O_STATUS OUT VARCHAR2
, O_MESSAGE OUT VARCHAR2
) AS
PROC_STATUS VARCHAR2(10);
PROC_ERROR_MESSAGE VARCHAR2(4000);
rand_num number;
BEGIN
dbms_output.put_line('IMPL_XEROX_PRINTER_CHECK ');
select round(dbms_random.value(1,10)) into rand_num from dual;
insert into TEST_PRNT_DATA values(1,2,3);
IF rand_num < 5 THEN
PROC_STATUS := 'TRUE';
O_STATUS:= 'TRUE';
PROC_ERROR_MESSAGE := 'ALL IS GOOD';
O_MESSAGE:= 'ALL IS GOOD';
ELSE
PROC_STATUS := 'FALSE';
O_STATUS:= 'FALSE';
PROC_ERROR_MESSAGE := 'SOMTHING WENT WRONG!!! ';
O_MESSAGE:= 'SOMTHING WENT WRONG!!! ';
END IF;
END IMPL_XEROX_PRINTER_CHECK;
It depends what your application can handle. You could have a wrapper function that returns a ref cursor:
create or replace function impl_xerox_printer_check_wrap (
printer_id in varchar2
)
return sys_refcursor as
o_status varchar2(200);
o_message varchar2(200);
o_refcursor sys_refcursor;
begin
impl_xerox_printer_check(printer_id => printer_id, o_status => o_status, o_message => o_message);
open o_refcursor for select o_status as status, o_message as message from dual;
return o_refcursor;
end;
/
select impl_xerox_printer_check_wrap('551555115') from dual;
IMPL_XEROX_PRINTER_C
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
STATUS MESSAGE
---------- ------------------------------
TRUE ALL IS GOOD
(output as shown by SQL Developer, run as a script). But your application might not know what to do with that.
You could use a collection or object type but unless you define your own at schema level it's a bit of a pain to interpret:
create or replace function impl_xerox_printer_check_wrap (
printer_id in varchar2
)
return sys.odcivarchar2list as
o_result sys.odcivarchar2list;
begin
o_result := new sys.odcivarchar2list();
o_result.extend(2);
impl_xerox_printer_check(printer_id => printer_id, o_status => o_result(1), o_message => o_result(2));
return o_result;
end;
/
select * from table (impl_xerox_printer_check_wrap('551555115'));
Result Sequence
------------------------------------------------
TRUE
ALL IS GOOD
Or you could go via XML, which sounds odd but gives a nice result:
create or replace function impl_xerox_printer_check_wrap (
printer_id in varchar2
)
return xmltype as
o_status varchar2(200);
o_message varchar2(200);
o_refcursor sys_refcursor;
begin
impl_xerox_printer_check(printer_id => printer_id, o_status => o_status, o_message => o_message);
open o_refcursor for select o_status as status, o_message as message from dual;
return xmltype(o_refcursor);
end;
/
select impl_xerox_printer_check_wrap('551555115') from dual;
IMPL_XEROX_PRINTER_CHECK_WRAP('551555115')
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<STATUS>FALSE</STATUS>
<MESSAGE>SOMTHING WENT WRONG!!! </MESSAGE>
</ROW>
</ROWSET>
OK, that doesn't look very helpful... but then you extract the values:
select status, message
from xmltable(
'/ROWSET/ROW'
passing impl_xerox_printer_check_wrap('551555115')
columns status varchar2(200) path 'STATUS',
message varchar2(200) path 'MESSAGE'
);
STATUS MESSAGE
---------- ------------------------------
FALSE SOMTHING WENT WRONG!!!
Your application can run that query - passing the printer ID as a bind variable of course - and will get back a simple result set.
As you're on 12c you can use the PL/SQL capabilities added to subquery factoring, so you don't need to create a permanent function at all (though you may still prefer to):
drop function IMPL_XEROX_PRINTER_CHECK_WRAP;
with
function impl_xerox_printer_check_wrap (
printer_id in varchar2
)
return xmltype as
o_status varchar2(200);
o_message varchar2(200);
o_refcursor sys_refcursor;
begin
impl_xerox_printer_check(printer_id => printer_id, o_status => o_status, o_message => o_message);
open o_refcursor for select o_status as status, o_message as message from dual;
return xmltype(o_refcursor);
end;
select impl_xerox_printer_check_wrap('551555115')
from dual
/
if you want XML (as per comment), or with XMLTable if you don't:
IMPL_XEROX_PRINTER_CHECK_WRAP('551555115')
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<STATUS>TRUE</STATUS>
<MESSAGE>ALL IS GOOD</MESSAGE>
</ROW>
</ROWSET>
with
function impl_xerox_printer_check_wrap (
printer_id in varchar2
)
return xmltype as
o_status varchar2(200);
o_message varchar2(200);
o_refcursor sys_refcursor;
begin
impl_xerox_printer_check(printer_id => printer_id, o_status => o_status, o_message => o_message);
open o_refcursor for select o_status as status, o_message as message from dual;
return xmltype(o_refcursor);
end;
select status, message
from xmltable(
'/ROWSET/ROW'
passing impl_xerox_printer_check_wrap('551555115')
columns status varchar2(200) path 'STATUS',
message varchar2(200) path 'MESSAGE'
)
/
STATUS MESSAGE
---------- ------------------------------
FALSE SOMTHING WENT WRONG!!!
The thing is that the SP is inserting some data to a temporary table
That's a fairly crucial omission. You can't perform an insert or update in a function call from a select. The documentation lists restrictions on functions called from SQL, and goes into more detail in this warning:
Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked by a SQL statement will run—even if the function is written in PL/SQL, an imperative language.
If the function was allowed to do DML then you would have no control over how many times that DML was performed. If it was doing an insert, for instance, it might try to insert the same row twice and either duplicate data or get a constraint violation.
You could, technically, declare the function with pragma autonomous_transaction, as in this modified db<>fiddle, but that's a horrible hack and one that will probably ultimately cause more problems than it solves, for the reasons shown above. You might get away with it if you only ever make single-row calls as in your example, but even then it isn't guaranteed to work; and even if it works now it might break in the future.