1

This is my SQL Server stored procedure which is below:

CREATE PROCEDURE flight_details
AS
BEGIN 
SELECT Airline.Airline_No, Airline_Name, Airplane.Airplane_No, Airplane_Name, Flight.Flight_No, Flight_Type, City_Departure, City_Transit, City_Arrival, Departure_Date_Time, Transit_Date_Time, Arrival_Date_Time, Flight_Duration_in_hours, Transit_Duration_in_hours, Flight_Status
FROM Airline,Airplane, Flight
WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No
END

EXECUTE flight_details

The above SQL Server stored procedure worked successfully.

Next, I tried to translate it into Oracle stored procedure code which is shown below:

CREATE OR REPLACE PROCEDURE flight_details
(p_flight_details OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN p_flight_details FOR
SELECT Airline.Airline_No, Airline_Name, Airplane.Airplane_No, Airplane_Name, Flight.Flight_No, Flight_Type, City_Departure, City_Transit, City_Arrival, Departure_Date_Time, Transit_Date_Time, Arrival_Date_Time, Flight_Duration_in_hours, Transit_Duration_in_hours, Flight_Status
FROM Airline,Airplane, Flight
WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No
END flight_details;

SET SERVEROUTPUT ON;

EXECUTE flight_details;

I tried creating this procedure in Oracle SQL Developer but it was showing the following error:

Error(5,1): PL/SQL: SQL Statement ignored

Error(7,133): PL/SQL: ORA-00933: SQL command not properly ended

Error(8,19): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge json_exists json_value json_query    json_object json_array 
2

FYI, you can access the following link and convert your block code from SQL to Oracle.

http://sqlines.com/online

1

You're missing a colon at the end of your cursor's select statement.

WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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