1 2 Previous Next 20 Replies Latest reply: Jul 31, 2013 4:18 PM by GTS (DBA) Go to original post RSS
      • 15. Re: Procedure Vs Stored Procedure
        GTS (DBA)

        @ FrankKulash  , It's my last question in this thread and last  doubt also.

        Please sort me out.

        Every object that is stored is the  database has a name

        A Procedure is an object; it is stored in the database and has a name.

        - I agree   FrankKulash

         

         

         

        If  so ,  HELLO is  a  name of the  procedure ,  but  it 's not stored in the  database - WHY  ??  Please do  little  explanation.          

        This is also not a  anonymous  block because  "HELLO " is  referenced in another block.


        SQL> DECLARE  PROCEDURE  HELLO  AS

          2  BEGIN

          3  DBMS_OUTPUT.PUT_LINE(' THIS IS PLSQL - WELCOME ');

          4  END;

          5  BEGIN

          6  DBMS_OUTPUT.PUT_LINE(' THIS IS PROCEDURE');

          7  HELLO;

          8  END;

          9  /

        THIS IS PROCEDURE

        THIS IS PLSQL - WELCOME

         

        PL/SQL procedure successfully completed.

         

        SQL> EXECUTE   HELLO;

        BEGIN HELLO; END;

        *  ERROR at line 1:

        ORA-06550: line 1, column 7:

        PLS-00201: identifier 'HELLO' must be declared

        ORA-06550: line 1, column 7:

        PL/SQL: Statement ignored

        • 16. Re: Procedure Vs Stored Procedure
          John Spencer

          What you have there is an anonymous block that defines a procedure (hello) within itself.  The procedure hello is only visible inside the anonymous block, so your call within the block succeeds, but once the block has completed, the procedure hello no longer exists.

           

          Essentially the database compiles and runs an anonymous block in a single pass, then throws it away.  Consider the difference between your version:

          SQL> DECLARE   -- Begin anonymous block
            2     PROCEDURE  HELLO  AS --Define procedure in block
            3        BEGIN
            4           DBMS_OUTPUT.PUT_LINE(' THIS IS PLSQL - WELCOME ');
            5     END;  -- End of procedure definition
            6
            7  BEGIN  -- Begin code section of anonymous block
            8     DBMS_OUTPUT.PUT_LINE(' THIS IS PROCEDURE');
            9     HELLO;   -- Call previously defined procedure
          10  END;   -- End of anonymous block
          11  /
          THIS IS PROCEDURE
          THIS IS PLSQL - WELCOME

          PL/SQL procedure successfully completed.

           

          and a named procedure version of the same code:

          SQL> create procedure my_procedure as -- Begin procedure definition
            2     PROCEDURE  HELLO  A   --Define procedure in procedure
            3        BEGIN
            4           DBMS_OUTPUT.PUT_LINE(' THIS IS PLSQL - WELCOME ');
            5     END;   -- End of inner procedure definition
            6  BEGIN  -- Begin code section of stored procedure
            7     DBMS_OUTPUT.PUT_LINE(' THIS IS PROCEDURE');
            8     HELLO; -- Call previously defined procedure
            9  END;   -- End of stored procedure definition
          10  /

          Procedure created.

          Note that in your anonymous block version the reults were returned immediately while in the stored procedure version there were no results.  The CREATE PROCEDURE command just compiles the code and stores it in the database, it does not actually run it.  In order to get results, you need to actually run the procedure:

          SQL> exec my_procedure;
          THIS IS PROCEDURE
          THIS IS PLSQL - WELCOME

          PL/SQL procedure successfully completed.

          The procedure hello is still not visible outside of the stored procedure because it is not an independent database object with a name of its own.

           

          John

          • 17. Re: Procedure Vs Stored Procedure
            Frank Kulash

            Hi,

             

            Sorry I don't understand.  If your question wasn't answered yesterday, then I don't know what your question is.

             

             

            That "hello" procedure is an example of the kind of procedure I described yesterday, in reply #2, where I said

             

             

            "It is possible (though not very common) to define a procedure locally within another PL/SQL unit, that is, inside a function, another procedure, or an anonymous block.  These locally-defined procedures are always named.  If they are defined inside a function or another procedure, then they are stored  in the database with that procedure.  If they are defined inside an anonymous block, then they are not stored in the database."

            • 18. Re: Procedure Vs Stored Procedure
              GTS (DBA)

              Ok  Frankkulash

               

              Ok, Thanks for all help. Even i get many replies  " i have some knowledge gap"  on procedures.

              Do NOT mistaken me. Your replies all  are good. but i cannot take all questions here.

              i am mixing many things into  "one place"   - So , i am getting confused.

               

              I know you are expert from stored  procedure concept in oracle. Definitely you  have some private link ,

              please provide it.  Please don't post oracle official doc link. i have it.

               

              If you have any private link  , please paste  here.

                         thanks.  Thanks all;

              • 19. Re: Procedure Vs Stored Procedure
                Chris Hunt

                Please say which  is valid one ?

                 

                Procedure is  a block of PL/SQL code , it  never stored in database.

                FALSE - procedures can be stored within the database.

                 

                Procedure is  a block of PL/SQL code , it  is named  and  stored within  the database.

                FALSE - it is possible, if unusual, to write PL/SQL procedures that aren't stored in the database. Anonymous blocks are one place, inside a Forms application (or whatever the modern equivalent is) would be another.

                 

                Stored procedure  is  block of PL/SQL code  it  is named  and  stored within  the database.

                TRUE - that's what PL/SQL stored procedures are. Note that there are other types of "block of PL/SQL code that is named and stored within the database" which are not stored procedures.

                • 20. Re: Procedure Vs Stored Procedure
                  GTS (DBA)

                  What FrankKulash   mentioned ;

                   

                  Procedure is  a block of PL/SQL code , it  never stored in database.

                  FALSE - procedures can be stored within the database. 


                  FrankKulash says

                   

                  1)  if  procedure defined in anonymous  block , it cannot be stored. because it's one  way once executed  we cannot revert back.

                  2) if  they are defined inside a  function or  another procedure ,  then they are stored  in the database.  

                            - this is called stored procedure.

                   

                  Thanks  ChrisHunt

                  1 2 Previous Next