1 2 Previous Next 17 Replies Latest reply: Oct 21, 2010 5:20 PM by 523861 RSS

    Stored Procedure in Oracle

    433191
      Guys,

      I am new to Oracle I am trying to change a stored procedure in sql server so that I can use it against the schema in oracle, for some reason I cannot get it work due the differences in syntax between SQL Server and Oracle. I believe the errors are related the select statements in the oracle for some reason I dont think I can use it in the SP.

      SQL SERVER

      CREATE PROCEDURE P_DEL AS

      SELECT ID FROM EMPLOYEE WHERE CORP = 'Y'
      UNION ALL
      SELECT EMPID FROM EMPLOYEE_BATCH WHERE LAST_UPDATED = 'Y'

      UPDATE P_LOG
      SET TIMESTAMP = GETDATE() WHERE OBJ_NAME = 'P_DEL'

      GO

      ORACLE

      CREATE OR REPLACE PROCEDURE P_DEL AS

      BEGIN

      SELECT ID FROM EMPLOYEE WHERE CORP = 'Y';
      UNION ALL
      SELECT EMPID FROM EMPLOYEE_BATCH WHERE LAST_UPDATED = 'Y';

      UPDATE P_LOG
      SET TIMESTAMP = GETDATE() WHERE OBJ_NAME = 'P_DEL'

      END P_DEL;

      Any suggestions or inputs would be helpful

      Thanks
        • 1. Re: Stored Procedure in Oracle
          21205
          The thing to remember that Oracle is not SQL Server and vice versa. The way you do things in Oracle are different than what you're used to. Try to forget all that.... we'd have the same problem when going to SQL Server :)

          What is this procedure supposed to do?
          As far as I can tell it this would suffice:
          CREATE OR REPLACE PROCEDURE P_DEL AS 
          BEGIN
             UPDATE P_LOG
                SET TIMESTAMP = sysdate
              WHERE OBJ_NAME = 'P_DEL'
             ;
          END P_DEL;
          'cause I don't know why you are selecting from the EMP tables...
          btw: TIMESTAMP is a reserved word


          Look into creating packages as opposed to standalone procedures
          • 2. Re: Stored Procedure in Oracle
            650063
            I Oracle you have to "select into variable" something, or, open cursor over a select query.
            You cannot just run select statement as in sql-server you seems to do.

            I oracle you would do:
            open MYCursor for
            SELECT ID FROM EMPLOYEE WHERE CORP = 'Y'
            UNION ALL
            SELECT EMPID FROM EMPLOYEE_BATCH WHERE LAST_UPDATED = 'Y';
            
            /*MYCursor is out-parameter of the procedure and caller can loop through that cursor now.*/
            Edited by: CharlesRoos on 21.10.2010 17:36
            • 3. Re: Stored Procedure in Oracle
              Frank Kulash
              Hi,

              One of the differences between SQL Server and Oracle is the nature of a stored procedure.

              In Oracle, a stored procedure is something that runs in the database (that is, the back-end), without any user interaction. The UPDATE statement you posted is something that could appropriately done in a stored procedure, but the query is probably something that you want to look at, which is a form of user interaction, and therefore not suitable for a stored procedure. Queries are typically something you do in the front end.

              What front end tool are you using to interact with the Oracle database?
              If it's SQL*Plus, then you can put the statemnents in a Script file, and run the script from the SQL*Plus command prompt.
              For example, if you put this into a file called fubar.sql
              --     Fubar.sql - (1) Display employee ids and then
              --              (2) Change tmstmps in p_log table
              
              
              --              (1) Display employee ids
              
              SELECT  id 
              FROM      employee 
              WHERE      corp      = 'Y'
                   --
                  UNION ALL
                   --
              SELECT      empid 
              FROM      employee_batch
              WHERE     last_updated     = 'Y'
              ;
              
              
              --              (2) Change tmstmps in p_log table
              
              UPDATE     p_log
              SET     tmstmp       = SYSDATE 
              WHERE      obj_name  = 'P_DEL'
              ;
              The you can execute it by saying
              @fubar
              at the SQL> prompt. Depending on where you saved the file, you may have to give its complete path name, for example:
              @c:\foo\common_scripts\fubar
              The actual SQL code is identical, except that the Oracle function for getting the current date and time from the database serrver is SYSDATE.
              TIMESTAMP is the name of a data type; it's not a good column name. If possible, call your column isomething else, like tmstmp or modify_date, n Oracle.
              • 4. Re: Stored Procedure in Oracle
                ZA
                You can use a SELECT statement in PL/SQL procedure body if it returns SINGLE row and you specify corresponding variables to hold the returned values. If you need to return multiple rows then you'll need to declare a cursor in declaration section and will open the cursor, loop through all records and process each returned row one by one.

                See this link for Oracle Cursors.

                http://plsql-tutorial.com/plsql-explicit-cursors.htm

                In your case, the select statement is most likely to return many rows because of union... so you will need to use the explicit cursor. And it would be something similar to following:
                CREATE OR REPLACE PROCEDURE P_DEL AS
                   v_empid   NUMBER;
                
                   CURSOR c_get_employee_id IS
                      SELECT id AS empid
                        FROM employee
                       WHERE corp = 'Y'
                      UNION ALL
                      SELECT empid
                        FROM employee_batch
                       WHERE last_updated = 'Y';
                BEGIN
                   OPEN c_get_employee_id;
                   FETCH c_get_employee_id INTO v_empid;
                   WHILE c_get_employee%FOUND LOOP
                      DBMS_OUTPUT.PUT_LINE ('Employee ID: ' || v_empid);
                      FETCH c_get_employee_id INTO v_empid;
                   END LOOP;
                   CLOSE c_get_employee_id;
                
                   UPDATE P_LOG
                      SET TIMESTAMP = SYSDATE
                    WHERE OBJ_NAME = 'P_DEL';
                
                   DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' records updated!');
                END P_DEL;
                • 5. Re: Stored Procedure in Oracle
                  433191
                  I have modified and tried to recompile I am still getting the following errors, not sure if oracle allow selects in the SP.

                  Any suggestions or inputs would help

                  SQL> execute jeff_test2.p_del;
                  BEGIN jeff_test2.p_del; END;

                  *
                  ERROR at line 1:
                  ORA-06550: line 1, column 18:
                  PLS-00905: object JEFF_TEST2.P_DEL is invalid
                  ORA-06550: line 1, column 7:
                  PL/SQL: Statement ignored

                  CREATE OR REPLACE PROCEDURE "TEST2".P_DEL AS

                  BEGIN

                  SELECT ID FROM "TEST2".EMPLOYEE WHERE CORP = 'Y';
                  UNION ALL
                  SELECT EMP_ID FROM "TEST2".EMPLOYEE_BATCH WHERE LAST_UPDATED = 'Y';

                  UPDATE "TEST2".P_LOG
                  SET TIMESTAMP1 = SYSDATE;

                  END; P_DEL;
                  • 6. Re: Stored Procedure in Oracle
                    650063
                    Try this way:
                    CREATE OR REPLACE PROCEDURE "TEST2".P_DEL AS
                    v_cursor   sys_refcursor;
                    BEGIN
                    
                    open v_cursor  for
                    SELECT ID FROM "TEST2".EMPLOYEE WHERE CORP = 'Y';
                    UNION ALL
                    SELECT EMP_ID FROM "TEST2".EMPLOYEE_BATCH WHERE LAST_UPDATED = 'Y';
                    
                    UPDATE "TEST2".P_LOG
                    SET TIMESTAMP1 = SYSDATE;
                    
                    END; P_DEL;
                    • 7. Re: Stored Procedure in Oracle
                      789895
                      Hi,

                      Kindly check the name of the SP. I guess the name of the SP is TEST2.P_DEL and not JEFF_TEST2.P_DEL. Also use cursor in order to select data as simple sqls may not be helpful.

                      cheers

                      VT
                      • 8. Re: Stored Procedure in Oracle
                        Frank Kulash
                        Hi,

                        Why do you need to do this in a stored procedure rather than a script?
                        sc**** wrote:
                        I have modified and tried to recompile I am still getting the following errors, not sure if oracle allow selects in the SP.

                        Any suggestions or inputs would help

                        SQL> execute jeff_test2.p_del;
                        BEGIN jeff_test2.p_del; END;

                        *
                        ERROR at line 1:
                        ORA-06550: line 1, column 18:
                        PLS-00905: object JEFF_TEST2.P_DEL is invalid
                        ORA-06550: line 1, column 7:
                        PL/SQL: Statement ignored
                        Create the procedure (and make sure it compiles without any errors) before you try to execute it.
                        CREATE OR REPLACE PROCEDURE "TEST2".P_DEL AS

                        BEGIN

                        SELECT ID FROM "TEST2".EMPLOYEE WHERE CORP = 'Y';
                        UNION ALL
                        SELECT EMP_ID FROM "TEST2".EMPLOYEE_BATCH WHERE LAST_UPDATED = 'Y';

                        UPDATE "TEST2".P_LOG
                        SET TIMESTAMP1 = SYSDATE;

                        END; P_DEL;
                        Don't put a semicolon between END and P_DEL.
                        END     p_del;
                        is what you want.

                        For now, comment out (or delete) the query; just do the UPDATE in the stored procedure. Make sure that works before going any farther. Take baby steps.

                        What are you planning to do with the ids returned from the query? When you do a SELECT inside PL/SQL, you have to catch the results in some variable or data structure. Exactly how to do this depend on how you plan to use the data after you get it.
                        It's much easier to use SELECT in a front-end tool, like SQL*Plus, and not in PL/SQL.
                        • 9. Re: Stored Procedure in Oracle
                          433191
                          This is latest version I still get at "union all", is it not supported in oracle. Any suggestions or inputs would help

                          Line # = 7 Column # = 1 Error Text = PLS-00103: Encountered the symbol "UNION" 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> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe

                          CREATE OR REPLACE PROCEDURE "TEST2"."P_DEL" AS
                          v_cursor sys_refcursor;
                          BEGIN

                          open v_cursor for
                          SELECT ID FROM "TEST2".EMPLOYEE WHERE CORP = 'Y';
                          UNION all
                          SELECT EMP_ID FROM "TEST2".EMPLOYEE_BATCH WHERE LAST_UPDATED = 'Y';

                          UPDATE "TEST2".P_LOG
                          SET TIMESTAMP1 = SYSDATE;

                          END; P_DEL;
                          • 10. Re: Stored Procedure in Oracle
                            789895
                            Hi,

                            >
                            open v_cursor for
                            SELECT ID FROM "TEST2".EMPLOYEE WHERE CORP = 'Y'; - There should be no semi colon here
                            UNION all
                            SELECT EMP_ID FROM "TEST2".EMPLOYEE_BATCH WHERE LAST_UPDATED = 'Y';
                            >

                            The select union all select should be a single statement. In your case you have actually terminated the sql which should not be done. Please read the post by Frank as he has clearly suggested use only the update as part of your exercise of getting familiar with Oracle instead of creating the cursor without using it.

                            cheers

                            VT
                            • 11. Re: Stored Procedure in Oracle
                              433191
                              If the cursor returns multiple rows, can it be consumed as an input to the application code or do we need declare array as output variable.

                              Thanks
                              • 12. Re: Stored Procedure in Oracle
                                789895
                                Hi,

                                Basically why do you need that cursor? What are you going to do with the cursor? If you can provide some information on those lines someone on the forum can help you in providing a better response which would be useful to you.

                                cheers

                                VT
                                • 13. Re: Stored Procedure in Oracle
                                  ZA
                                  Sure, you can use the fetched data as an input for your application or whatever purposes. You can use a WHILE loop or cursor FOR loop to travers through all records and perform actions on them... checking the data for validation, updation, deletion... anything.

                                  Another way of getting data from tables is to use BULK COLLECT with collections (sort of arrays) which is faster than conventional cursors.

                                  But I would recommed you to go through the documentation for Oracle Cursors... learn different techniques for using them and then jump to advanced concepts including using BULK COLLECT to get whole set of records into Oracle collections in one shot.

                                  e.g. below code will read through cursor and create an entry in myAuditTable if employee id is other than 7369.
                                  Hope it helps!
                                  CREATE OR REPLACE PROCEDURE P_DEL AS
                                     v_empid   NUMBER;
                                  
                                     CURSOR c_get_employee_id IS
                                        SELECT id AS empid
                                          FROM employee
                                         WHERE corp = 'Y'
                                        UNION ALL
                                        SELECT empid
                                          FROM employee_batch
                                         WHERE last_updated = 'Y';
                                  BEGIN
                                     OPEN c_get_employee_id;
                                     FETCH c_get_employee_id INTO v_empid;
                                     WHILE c_get_employee%FOUND LOOP
                                        IF v_empid != 7369 THEN
                                           INSERT INTO myAuditTable (empid) VALUES (v_empid);
                                           DBMS_OUTPUT.PUT_LINE ('Employee ID: ' || v_empid||' stored in myAuditTable');
                                       END IF;
                                       FETCH c_get_employee_id INTO v_empid;
                                     END LOOP;
                                     CLOSE c_get_employee_id;
                                  
                                     UPDATE P_LOG
                                        SET TIMESTAMP = SYSDATE
                                      WHERE OBJ_NAME = 'P_DEL';
                                  
                                     DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' records updated!');
                                  END P_DEL;
                                  Edited by: Zaafran Ahmed on Oct 21, 2010 11:23 AM
                                  • 14. Re: Stored Procedure in Oracle
                                    433191
                                    I am able to execute the sp successfully from sql plus, however one question though I dont see the output unless I set serveroutput on. How will the application code get the output when it executes the stored procedure without set serveroutptu on when it calls the SP from the application

                                    Thanks

                                    SQL> execute p_del;

                                    PL/SQL procedure successfully completed.

                                    SQL> set serveroutput on
                                    SQL> execute p_del;
                                    2
                                    1

                                    PL/SQL procedure successfully completed.
                                    1 2 Previous Next