8

This is my SQL Server Stored Procedure which is below:

CREATE PROCEDURE passenger_details
AS
BEGIN
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, Airline, Class
WHERE Passenger.Passenger_No=Ticket.Passenger_No AND Airline.Airline_No=Ticket.Airline_No AND Class.Class_No=Ticket.Class_No
END

EXECUTE passenger_details

The above stored procedure in SQL Server works successfully.

Then I tried to execute the same stored procedure in Oracle PL/SQL which is shown below:

CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN p_passenger_details FOR
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, Airline, Class
WHERE Passenger.Passenger_No=Ticket.Passenger_No AND Airline.Airline_No=Ticket.Airline_No AND Class.Class_No=Ticket.Class_No;
END passenger_details;

The above stored procedure in Oracle PL/SQL is compiled successfully.

Then I tried to execute it which is shown below:

SET SERVEROUTPUT ON;
EXECUTE passenger_details;

While trying to execute the stored procedure, I'm getting the following error message which is shown below:

Error starting at line : 12 in command -
BEGIN passenger_details; END;
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PASSENGER_DETAILS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
0

2 Answers 2

5

You can use dbms_sql to return implicit results. This was a feature added to Oracle 12 to ease the migration from SQL Server:

CREATE PROCEDURE passenger_details
AS
  c1 SYS_REFCURSOR;  
BEGIN

  open c1 for
  SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
  FROM Passenger
    JOIN Ticket on Passenger.Passenger_No = Ticket.Passenger_No
    JOIN Airline ON Airline.Airline_No = Ticket.Airline_No
    JOIN Class ON Class.Class_No = Ticket.Class_No;

  DBMS_SQL.RETURN_RESULT(c1);
END;
/

Note that I replaced the ancient and outdated implicit joins with an explicit JOIN operator.

5

You need to pass in a REFCURSOR for the procedure to use as its output (OUT) parameter.

Quick procedure to test with:

CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN p_passenger_details FOR
SELECT 'test test' as Full_Name, 10 as Age, 'alien' as Nationality, 'foo' as Category, 
'Name' as Airline_Name, 'wobble' as Class_Type
FROM dual;
END passenger_details;
/

Test in SQL*Plus:

SQL> variable mycursor refcursor;
SQL> exec passenger_details ( :mycursor );

PL/SQL procedure successfully completed.

SQL> print mycursor;

FULL_NAME    AGE NATIO CAT AIRL CLASS_
--------- ---------- ----- --- ---- ------
test test     10 alien foo Name wobble

SQL>
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.