Call Oracle stored procedure from Select statement?

Question

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;

Answer 1

Make a new Sql trigger which watches a table Table_legacyInputOutput. Insert your input in table with Printer id PRINTER_ID = '551555115' Then trigger will call stored procedure and update table for O_STATUS and O_MESSAGE . I think your legacy application can d o insert and select at least. It just cannot call SP and check return parameters

Table_legacyInputOutput structure.
PRINTER         O_STATUS   O_MESSAGE  

Answer 2

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!!!        

db<>fiddle

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.

Answer 3

Based on a combination of a couple of Alex's answers (sys.odcivarchar2list collections and with functions) here are a couple variations on the themes:

The first one returns a single row as with most examples by using a pivot in the last query:

with function wrap(printer_id in varchar2) return sys.odcivarchar2list as
  status sys.odcivarchar2list;
begin
  status := new sys.odcivarchar2list();
  status.extend(2);
  impl_xerox_printer_check(printer_id, status(1), status(2));
  return status;
end;
t1 as (
select rownum r, column_value
  from wrap('551555115')
)
select * 
  from t1
  pivot (max(column_value) 
         for r in ( 1 as status
                  , 2 as message));
/

Sample Output:

STATUS   MESSAGE                  
-------- -------------------------
FALSE    SOMTHING WENT WRONG!!!   

This second example demonstrates using CROSS APPLY to get the status of multiple printers at one time:

with function wrap(printer_id in varchar2) return sys.odcivarchar2list as
  status sys.odcivarchar2list;
begin
  status := new sys.odcivarchar2list();
  status.extend(2);
  impl_xerox_printer_check(printer_id, status(1), status(2));
  return status;
end;
printers as (
  select dbms_random.string('X',10) printer from dual connect by level <=5
), t1 as (
select printer, mod(rownum-1,2) r,  w.*
  from printers
  cross apply wrap(printers.printer) w
)
select * 
  from t1
  pivot (max(column_value) for r in (0 as status, 1 as message));
/

Sample Output:

PRINTER    STATUS   MESSAGE                  
---------- -------- -------------------------
M6N6MZ5NG6 TRUE     ALL IS GOOD              
4H2WKK52V7 TRUE     ALL IS GOOD              
6MB7B9FRWV TRUE     ALL IS GOOD              
389KALS4U9 FALSE    SOMTHING WENT WRONG!!!   
6Y1ACVUHY6 TRUE     ALL IS GOOD              

Answer 4

You could create a package with a pipelined table function:

CREATE OR REPLACE 
PACKAGE PACKAGE1 
AS 
  type status_t is record ( o_status varchar2(10)
                          , o_message varchar2(4000));
  type status_tt is table of status_t;

  function impl_xerox_printer_check_w(printer_id varchar2) RETURN status_tt PIPELINED;

END PACKAGE1;
/

With the following implementation:

CREATE OR REPLACE
PACKAGE BODY PACKAGE1 AS

  function impl_xerox_printer_check_w(printer_id varchar2) RETURN status_tt PIPELINED AS
    status status_t;
  BEGIN
    impl_xerox_printer_check(printer_id, status.o_status, status.o_message);

    PIPE ROW (status);
    RETURN;
  END impl_xerox_printer_check_w;

END PACKAGE1;
/

and use it like this:

with printers as (
  select dbms_random.string('X',10) printer from dual connect by level <=5
)
select * 
  from printers
 cross apply table(package1.impl_xerox_printer_check_w(printers.printer));

Example output or check out the db<>fiddle:

PRINTER         O_STATUS   O_MESSAGE                     
--------------- ---------- ------------------------------
55FBCMHYOS      TRUE       ALL IS GOOD                   
0Z37VPOSLK      TRUE       ALL IS GOOD                   
XK1QKTZ8X2      FALSE      SOMTHING WENT WRONG!!!        
K0Y6TN9YTR      FALSE      SOMTHING WENT WRONG!!!        
8D0505711L      TRUE       ALL IS GOOD