How to translate SQL Server stored procedure into Oracle stored procedure?












0















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









share|improve this question














bumped to the homepage by Community 2 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.




















    0















    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









    share|improve this question














    bumped to the homepage by Community 2 mins ago


    This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.


















      0












      0








      0








      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









      share|improve this question














      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






      sql-server plsql oracle-sql-developer






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Jul 30 '17 at 20:33









      KamalKamal

      1




      1





      bumped to the homepage by Community 2 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







      bumped to the homepage by Community 2 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
























          1 Answer
          1






          active

          oldest

          votes


















          0














          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;





          share|improve this answer























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "182"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f182217%2fhow-to-translate-sql-server-stored-procedure-into-oracle-stored-procedure%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            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;





            share|improve this answer




























              0














              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;





              share|improve this answer


























                0












                0








                0







                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;





                share|improve this answer













                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;






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jul 30 '17 at 22:11









                codedawg82codedawg82

                41725




                41725






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f182217%2fhow-to-translate-sql-server-stored-procedure-into-oracle-stored-procedure%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Liste der Baudenkmale in Friedland (Mecklenburg)

                    Single-Malt-Whisky

                    Czorneboh