oracle-databasestored-proceduresoracle-sqldeveloperoutput-parameter

How to select stored procedure output parameters as a table in oracle sql developer


I have a procedure

create or replace PROCEDURE proc
(
  p1 IN varchar2,
  p2 IN varchar2,
  p3 OUT varchar2
) AS
BEGIN
 p3:= p1 || ' ' || p2
END proc

I call it by

Declare
  P3 varchar(50);
Begin
 proc('foo', 'bar', P3)
END;

I can print out the value of P3 using

Dbms_Output.Put_line('P3: ' || P3)

But I need to get the result as a table I can select from. Best way I can think of is

Declare
  P3 varchar(50);
Begin
 proc('foo', 'bar', P3)
END;
SELECT &&P3 from dual;

But that gives me an error

Error report -
ORA-06550: line 5, column 12:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 5, column 5:
PL/SQL: SQL statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilatiopn error.
*Action:

Is there some way of selecting the value of a parameter as a column/row value?

I need this all done inside the single sql query because I need to executed it from another server via linked server.

I don't have permission to create any other stored procedures, functions or tables on the database.


Solution

  • To me, the most straightforward option is to create a function - it is supposed to return a value:

    create or replace function proc (p1 IN varchar2, p2 IN varchar2)
      return varchar2
    AS
    BEGIN
      return p1 || ' ' || p2;
    END proc;
    

    Then you'd call it as e.g.

    select proc('a', 'b') from dual;
    

    Saying that you "don't have permission to create ... functions" - well, if you are granted to create a PROCEDURE, then you are also granted to create a FUNCTION. It is the same privilege.


    If you can only use procedures that are already created, then:

    SQL> create or replace PROCEDURE proc
      2  (
      3    p1 IN varchar2,
      4    p2 IN varchar2,
      5    p3 OUT varchar2
      6  ) AS
      7  BEGIN
      8    p3:= p1 || ' ' || p2;
      9  END ;
     10  /
    
    Procedure created.
    
    SQL> var result varchar2(20)
    SQL>
    SQL> exec proc('a', 'b', :result);
    
    PL/SQL procedure successfully completed.
    
    SQL> print :result
    
    RESULT
    --------------------------------
    a b
        
    SQL> select :result from dual;
    
    :RESULT
    --------------------------------
    a b
    
    SQL>