Oracle Procedure vs SQL Server Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle Procedure vs SQL Server Procedure

  1. #1
    Join Date
    May 2011
    Posts
    2

    Question Oracle Procedure vs SQL Server Procedure

    Hello!

    I am new to Oracle and SQL plus. If anyone could help me understand procedures in oracle and how it works Id greatly appreciate it.
    Actually I have been working for quite time in SQL Server. So in SQL Server you can create simple stored procedure like


    Create proc pr_Orders
    As
    Begin

    Select * From dbo.Order

    End

    Then you execute that procedure like

    Exec pr_ Orders

    And you get output in your query analyzer

    How I can create same simple procedure in Oracle and have output in is-sql ?


    Thank you very much.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Oracle stored procedures work pretty much the same way SQL Server ones do.

    Open SQLPlus then...

    Code:
    -- Create your procedure
    CREATE PROCEDURE pr_orders 
    AS
    BEGIN
    . . . Do your stuff here
    END pr_orders;
    /
    
    
    -- Execute your procedure
    variable my_cursor refcursor;
    exec pr_orders(:my_cursor);
    print my_cursor;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2011
    Posts
    2
    Thank you for your answer. But I have tried to created this simple procedure

    Create Or Replace procedure pr_empFullName
    Is

    BEGIN

    Select Employee_ID, First_Name || ' ' || Last_Name FullName, Salary From Employees;

    END pr_empFullName;
    /

    and I got this error

    [Error] PLS-00428 (9: 6): PLS-00428: an INTO clause is expected in this SELECT statement

    Since this would work just fine in SQL Server then what am I missing here, if, like you said Oracle procedures works pretty much the same as in SQL Server.

    Thank you very much.

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Results are returned into variables. Same way you use variables in T-SQL, but the syntax is a bit different.

    Have you read anything on PL/SQL or do you expect to learn it all in a forum?
    http://www.plsql-tutorial.com/

    How did you learn T-SQL?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by p_ruzic View Post
    Since this would work just fine in SQL Server then what am I missing here, if, like you said Oracle procedures works pretty much the same as in SQL Server.
    Let me re-phrase it.
    Conceptually, Oracle and SQL Server storedprocs work in a pretty similar way.
    Having said that, PL/SQL and T-SQL are two different languages, the same way you cannot expect to be fluent in Portuguese just because you are fluent in Spanish you cannot expect T-SQL syntax to work just fine in PL/SQL.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width