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

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