This content has been marked as final. Show 5 replies
830933 wrote: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.
How do we refer the anonymous block in PL/SQL?
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:
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.
select e.* from SCOTT.emp e where e.dept_no = :1
This anonymous block will look as follows:
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+.
begin SCOTT.CalculateFoo( id => :1, res => :2 ); end;
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.