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:
Best Answer
You can use
dbms_sql
to return implicit results. This was a feature added to Oracle 12 to ease the migration from SQL Server:Note that I replaced the ancient and outdated implicit joins with an explicit
JOIN
operator.