5 Replies Latest reply: Jan 26, 2011 10:46 PM by 833936 RSS

    Anonymous Block in PL/SQL

    833936
      How do we refer the anonymous block in PL/SQL? And what is the purpose of anonymous blocks?
        • 1. Re: Anonymous Block in PL/SQL
          Nimish Garg
          this may be helpful
          http://docstore.mik.ua/orelly/oracle/prog2/ch15_03.htm
          • 2. Re: Anonymous Block in PL/SQL
            hm
            An anonymous block has no name and can't be referenced.

            It is just a definition block and a sequence of statements.

            The purpose is to execute these statements.
            • 3. Re: Anonymous Block in PL/SQL
              Billy~Verreynne
              830933 wrote:
              How do we refer the anonymous block in PL/SQL?
              You cannot refer to a code unit by name, that has no name. A stored proc, stored function and stored package all have names. These are not only stored as a named object in the database, but if valid, also compiled and ready to be loaded and executed.

              An anonymous block has no name. So it cannot be stored as a named object in the database. You can store it in a column as a value for example. You can store it on the client as a SQL script file. Or the client can dynamically create anonymous blocks - as is the usual case.

              An anonymous block always need to be parsed and compiled before it can used.
              And what is the purpose of anonymous blocks?
              If your Visual Basic client need to execute a SQL statement, then the "anonymous SQL block" (which we simply call the SQL statement) that will be created and send to Oracle would look something as follows:
              select e.* from SCOTT.emp e where e.dept_no = :1
              Now anonymous PL/SQL blocks are not really different. Clients will typically create dynamic anonymous PL/SQL blocks for making PL/SQL procedural calls. Let's say your Visual Basic client wants to run procedure CalculateFoo( id in number, res out number ) that resides in the SCOTT schema in the database. It needs to create an anonymous PL/SQL block and place the procedure call in it.

              This anonymous block will look as follows:
              begin
                SCOTT.CalculateFoo( id => :1, res => :2 );
              end;
              It then needs to send this to Oracle to be parsed and executed. Oracle will also expect 2 bind variables to be supplied - an input bind variable called +1+ and an output bind variable called +2+.

              So all SQLs that you send are by default "anonymous" SQL blocks of code - that needs to be parsed and executed. Now PL/SQL works in a similar fashion - you will need to send a block of PL/SQL code (and not SQL code) in order to execute PL/SQL procedures and packages.
              • 4. Re: Anonymous Block in PL/SQL
                Hoek
                Some more: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4281341578031
                • 5. Re: Anonymous Block in PL/SQL
                  833936
                  Thanks. The provided link was really worthy.