3

Oracle stored procedure has OUT parameter and returns result set, e.g.

create or replace procedure foo(empId IN NUMBER, maxSalary OUT NUMBER) AS BEGIN
    select * from Employee e where e.id >=empId;
    select max(salary) into maxSalary from Employee;
END;

ERROR:

PLS-00428: an INTO clause is expected in this SELECT statement

Mysql stored procedure can return both result sets and out parameters. How to do it for oracle db?

eastwater
  • 4,624
  • 9
  • 49
  • 118

1 Answers1

8

In Oracle, you cannot run direct select statements without an INTO clause.

If you are using Oracle 12c and above, you may use a REF CURSOR and DBMS_SQL.RETURN_RESULT 

create or replace procedure foo(empId IN NUMBER, maxSalary OUT NUMBER) AS
q SYS_REFCURSOR;
 BEGIN
    OPEN q FOR select * from Employee e where e.id >=empId;
     DBMS_SQL.return_result (q); -- This will display the result
    select max(salary) into maxSalary from Employee;
END;

For previous versions ( 11g,10g) , You could pass a REF CURSOR as an OUT parameter and print it from sqlplus or TOAD by running as script.

create or replace procedure foo(empId IN NUMBER, maxSalary OUT NUMBER,
   q OUT SYS_REFCURSOR) AS

     BEGIN
        OPEN q FOR select * from Employee e where e.id >=empId;
        select max(salary) into maxSalary from Employee;
    END;

Define bind variables before calling the procedure.

VARIABLE v_empID NUMBER
VARIABLE v_maxsalary NUMBER
VARIABLE v_q REFCURSOR

EXEC :v_empID := 101
EXEC foo(:v_empID,:v_maxsalary,:v_q ) 
PRINT v_q -- This will display the result from the query.
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Can the same be achieved in SQL Server 2008, tell me? – Will Marcouiller Feb 08 '19 at 19:18
  • @WillMarcouiller, yes. Ms Sql allows you to return one or more data sets from the stored procedure. And please avoid using cursor, which is slow for Ms Sql (e.g. just return tables). See details here: https://stackoverflow.com/questions/22963939/sql-server-stored-procedure-return-a-table/22964394 – Manushin Igor Apr 26 '19 at 15:47