PL/SQL Enhancements

The following are the new features related to PL/SQL that were introduced in Oracle8i.

Native Dynamic SQL

Though dynamic sql is not new to Oracle, allowing programmers use dynamic sql as part of PL/SQL is new in Oracle8i. First, let us understand what is Dynamic SQL.

What is Dynamic SQL?

Generally the SQL statements that are to be executed are known to programmer at the time of writing program. However, in some cases the programmer may not precisely know the command that is to be executed at the time of writing program. Instead the command may vary from execution to execution and it is to be constructed dynamically (at runtime). An SQL statement that is constructed on the fly is called as dynamic SQL statement.

An Example of Dynamic SQL

Let's look at an example. Assume we have to drop a table. But the name of the table that is to be dropped is not known to us. The name of the table is formed using the word sales and the current year. For example, it the current year is 2001 then table name would be sales2000. That

That means depending upon the current year the name of the table changes. So the drop table command should drop the table of the current year (whatever may be the current year) and the current year is to be taken from the system at runtime.

If you give the following command, it would be valid only during year 2001 and it will not be valid once we move into 2002. So the command should be constructed in such a way that it works in all years.


   drop table  sales2000

The solution to this problem is to construct Drop Table command as given below.


     cmd :=   ‘drop table  sales’ || to_char(sysdate,’yyyy’);

The above statement will put the required command into a char variable. The word sales is concatenated with four digits year. Once the command is placed in a variable, then you can execute the command, which is in the variable.

Execute Immediate Statement

This statement is used to execute a command that is in a char variable. In the above example we put the required command in a variable called cmd. Now, we can execute the drop table command as follows:

  Execute immediate cmd;

Now let us examine the complete syntax of Execute Immediate statement.


EXECUTE IMMEDIATE dynamic_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
    [, [IN | OUT | IN OUT] bind_argument]...];

Dynamic_string is the string that contains the command to be executed.
Variable | record Is a variable into which values retrieved by SELECT command are to be copied. If a record is given then complete row is copied into the record. Record must be a variable of user-defined data type or %ROWTYPE% record.
Bind_argument Is the value that is to be passed to SQL statement that is being executed. This value will replace a placeholder in the command (more on this later).

Why do we need Dynamic SQL?

After having seen an example of dynamic sql, let us now understand the need to use dynamic sql. The following are the situations where you may want to use Dynamic SQL:

You want to execute an SQL DDL or DCL command that cannot be executed statically. That means the command is not allowed in the PL/SQL block.
For example, you cannot execute DROP TABLE command from a PL/SQL block.
You want to access a table or a view but do not know the name of the object until you run the program.

In fact, the example we have seen is satisfying both the needs.

Note: Dynamic SQL was available even prior to Oracle8i. DBMS_SQL was the package used to create and execute dynamic SQL statements. But native dynamic SQL introduced in Oracle8i is comparatively faster than DBMS_SQL and more powerful.

More Examples

We will look at a few more examples to understand the need for dynamic sql.

First, assume we have to delete all the rows from sales table of the previous year.

As we have already seen the name of the table has to be formed dynamically, we have to use dynamic sql to form DELETE command. The following PL/SQL block will achieve this.

delcare
      cmd  varchar2(100);
begin
      cmd := ‘delete from  sales’ ||  to_char( sysdate,’yyyy’) – 1;
      execute immediate  cmd;
      commit;
end;

Second, we have to delete all the rows related to a particular product from the current year’s sales table.

Apart from creating the name of the table dynamically, we also have to pass a value for product number using USING clause as follows:

Delcare
      Cmd  varchar2(100);
Begin
      Cmd := ‘delete from  sales’ ||  to_char( sysdate,’yyyy’) || ‘ where  prodid = :1 ‘;
     -- execute the command by sending product id 1001
      Execute immediate  cmd using  1001;  
      Commit;
End;

Using clause is used to send a value that is to replace the placeholder used in the SQL command (:1).

Note: The number of placeholders and the number of values passed using USING clause must be equal.

Execute a Query Dynamically

So far whatever we have seen is related to non-query SQL statements. Now, let us see how to execute an SQL query dynamically. The difference between executing a non-query and a query is; a query returns one or more rows. In the beginning we will confine our examples to executing single-row query statements.

When a query in executed dynamically using EXECUTE IMMEDIATE command then we have to supply INTO caluse to catch the values retrieved by the query.

The following example shows how to use SELECT command with dynamic sql to retrieve highest and lowest prices at which the given product is sold in the current year.


Delcare
      Cmd  varchar2(100);
      Prodid  number(5)  :=  1001;
      Lprice  number(5);
      Hprice  number(5);
Begin
      Cmd := 'select  min(price) , max(price) from sales'
      cmd := cmd  ||  to_char(sysdate,'yyyy') || ' where prodid = :1';

      -- execute the command by sending product id 1001 
      -- and copy result to variables

      Execute immediate cmd into  lprice, hprice using  prodid;  

      Dbms_output.put_line( ‘Lowest price is : ‘ ||   lprice);
      Dbms_output.put_line(‘Highest price is : ‘ ||   hprice);

End;

While a query is executed using EXECUTE IMMEDIATE command the following rules will apply:

The number of columns to be retrieved should be known at the time of writing program.
Only one record can be retrieved as only one set of variable can be passed using INTO.

Executing multi-row query dynamically

Using simple EXECUTE IMMEDIATE statement we can execute a query that retrieves only one row. However, when we need to execute a query that has the potential to retrieve multiple rows, we need to follow a different process.

To deal with execution of multi-row query dynamically, we have to use a cursor. All the rows retrieved by the query are to be copied into cursor. And then using FETCH statement each row will be fetched and processed.

The following are the statements used to execute a multi-row query dynamically.

OPEN-FOR statement to open the cursor

The first step is to open a cursor with the query statement. This statement associates a cursor variable with the given query and executes the query. This statement has USING option to pass values to placeholders.

The complete syntax of OPEN-FOR statement is as follows:


OPEN {cursor_variable} 
  FOR dynamic_string
   [USING bind_argument[, bind_argument]...];

cursor_variable Is a weakly typed cursor variable, i.e a variable that doesn’t have any return type.
Dynamic_stringThe SELECT command to be executed.
Bind_argumentIs the value to be passed to placeholders of the command.

The following is an example where all rows of sales table of the current year are retrieved.


DECLARE
   TYPE SalesCursorType IS REF CURSOR;  -- define weak REF CURSOR type
   salescursor  SalesCursorType;  -- declare cursor variable  
   prodid  number(5);
   qty     number(5);
   price   number(5);
 BEGIN
  OPEN salescursor FOR  -- open cursor variable 
      'SELECT  proid, qty, price from sales’ || to_char(sysdate,’yyyy’); 
   .
   .
   .


 END;

Fetching row from cursor

Fetches one row from the cursor and copies the values of the columns into corresponding variables.


FETCH {cursor_variable } INTO {define_variable[, define_variable]... 
         | record};

Cursor_variable is the cursor variable with which the result of the query is associated.
Define_variable is the variable into which the value the corresponding columns should be copied.
Record is the variable of a user-defined record type or %ROWTYPE%.

To fetch rows from SalesCursor that we have defined in the previous step, we have to use FETCH statement as follows:

Loop 
    Fetch  salescursor  into  prodid, qty, price; 
    Exit  when   sales_cursor%notfound;    -- exit when no more records exist
    -- process the record here
End loop

Closing the cursor

The last step in the process is closing the cursor after it is processed.


CLOSE {cursor_variable};

A sample program

The following program is consolidating all that we have seen regarding how to execute a multi-row query dynamically.


DECLARE
   TYPE SalesCursorType IS REF CURSOR;  -- define weak REF CURSOR type
   salescursor  SalesCursorType;  -- declare cursor variable
   prodid  number(5);
   qty     number(5);
   price   number(5);
 BEGIN
   OPEN salescursor FOR  -- open cursor variable
      'SELECT  proid, qty, price from sales’ || to_char(sysdate,’yyyy’);

 Loop 
    Fetch  salescursor  into  prodid, qty, price;
    Exit when sales_cursor%notfound;  -- exit when no more records  exist
    -- process the record here
 End loop

 Close salescursor;   -- close the cursor after it is processed.

End;   -- end of the block

Important points

The following are the important points that you have to keep on your mind while dealing with native dynamic sql.

You cannot use bind arguments to pass schema object names.
For example, you cannot have table name in an SQL command to be replaced with the value of a bind argument as follows:
```
EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;
```
Instead it should be as follows:
```
EXECUTE IMMEDIATE  'DROP TABLE ' || table_name;
```
If you want, you can use a single bind argument for more than one placeholder. Because the association between bind arguments and placeholders is based on the position and not on name.
For example, the following is valid
```
EXECUTE IMMEDIATE 'INSERT INTO payroll VALUES (:x, :x, :y, :x)’ 
                  USING a, a, b, a; 
```
You can use implicit cursor to get information about dynamically executed command.
In the following example function rows_deleted takes the name of the table and a condition and returns the number of rows deleted. For this it is using %ROWCOUNT% attribute of implicit commit.
```
CREATE FUNCTION rows_deleted (
   table_name IN VARCHAR2, 
   condition IN VARCHAR2) RETURN INTEGER AS
BEGIN
   EXECUTE IMMEDIATE 
      'DELETE FROM ' || table_name || ' WHERE ' || condition;
   RETURN SQL%ROWCOUNT;  -- return number of rows deleted
END;
```

You cannot pass a null value directly but can pass a null value through a variable. For example, the following is invalid:

EXECUTE IMMEDIATE ‘UPDATE product SET disrate = :x' USING null;

Null value can be passed using a variable as follows:

Declare
       Disrate  number(5) := null;
Begin
       EXECUTE IMMEDIATE ‘UPDATE product SET disrate = :x' USING disrate;
End;

Autonomous Transaction

A transaction is a collection of SQL statements that is used to perform a logical unit of work. Autonomous PL/SQL blocks are PL/SQL blocks that have a transaction scope independent of the transaction scope of the calling PL/SQL block. Autonomous block can commit and rollback the changes without effecting the changes made in calling transaction.

First let us understand how a procedure works with normal transactions. In the given example procedure p1 is calling procedure p2. If p2 commits the changes then any changes made in both p1 and p2 will be committed. In the same way if p2 rolls back any changes made in both the procedure will be undone.


Procedure  p1 is
Begin
    Update  ….;
    Delete  …
    P2;    -- call  procedure p2
    Commit;
End;


Procedure  p2  is
Begin
     Insert …;

     If  ...  then
        Commit;
     Else
        Rollback;
     End if;

End;

If condition in the second procedure - p2 is true then update, delete of p1 and insert of p2 will be committed. If condition is false all of then will be rolled back. And then commit given in procedure p1 is of no use.

But in some cases the procedure should have their own independent transaction. And it can be achieved using autonomous transaction. An autonomous transaction suspends the main transaction (transaction of the calling program) and resumes the main transaction once the autonomous transaction is complete.

Here is procedure p1 and p2 with p2 containing autonomous transaction.

Autonomous transaction is defined using the statement PRAGA AUTONOMOUNS_TRANSACTION.

Procedure  p1 is
Begin
    Update ...;
    Delete ...
    P2;    -- call  procedure p2
    Commit;
End;


Procedure  p2  is  
    Pragma autonomous_transaction
Begin
     Insert ...;

     If ... then
           Commit;
     Else
           Rollback;
     End if;

End;

As p2 has autonomous transaction, changes made in p1 are preserved irrespective of the commit or rollback given in p2. Just before p2 is called in p1 the main transaction is suspended. That means any changes made in p2 will not have any effect on changes made in p1. And the transaction in p1 will resume after procedure p2 is completed.

An autonomous block makes procedures independent of the transaction context of the calling program. And even the calling program is not effected by the autonomous transaction block. This allows you to build reusable components more easily.

Autonomous transactions have the same functionality as the regular transaction. The commands that are used to control the regular transactions, commit, rollback and savepoint, are used to control autonomous transaction also.

The following is an example where a database trigger uses an autonomous transaction to insert a record into a table called ACTIONS for each delete statement executed on SALES table. This insertion will be committed irrespective of the calling transaction.


CREATE TRIGGER sales_delete_action
BEFORE  DELETE
ON parts 
FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN 
    INSERT INTO  ACTIONS VALUES( :new.invno, user, sysdate); 
   COMMIT;
END;

Invoker Rights

By default, the stored procedures and methods of object types execute under the rights of the definer and not the invoker. when a procedure is executed under the right of the definer then it is said to use definer-rights. Definer-rights is the only option available until oracle8. But, Oracle8i has introduced invoker-rights in which a procedure is executed under the rights of the invoker and not the definer.

To understand where we need invoker rights, assume we need to create a procedure to delete records from ADDRESSES table. Each user maintains his own ADDRESSES table. User PRANEETH has created a procedure as follows:


CREATE PROCEDURE  DeleteAddresses AS
BEGIN
     Delete from addresses;
     Commit;
END;

When user PRANEETH executes the procedure, it will delete all rows from ADDRESSES table of user PRANEETH. If user PRANEETH has given execute privilege to user ANURAG, and if ANURAG executes the procedure then also the ADDRESSES table of the user PRANEETH will be used by the procedure. It is because all references to the schma objects are made to the object of the definer of the procedure.

However, if a general procedure is to be written and if it has to delete rows from the ADDRESSES table of the invoker and not the definer of the procedure then invoker rights should be used as follows.


CREATE PROCEDURE DeleteAddresses 
AUTHID CURRENT_USER AS 
BEGIN
     Delete from addresses;
     Commit;
END;

Now as the reference to table ADDRESSES is unqualified, it is resolved according to the invoking user and not the definer. That means, if ANURAG invokes it then rows of ADDRESSES table of user ANURAG will be deleted.

One of the biggest advantage of the invoker rights is centralizing the code. One procedure can access the data of multiple users without the need to write multiple procedures to access the data of multiple users.

Referring to a schema object of a user

If you ever have to access a schema object of a particular user then you can qualify the schema object with the name of the user (for example, PRANEETH.ADDRESSES).

If a schema object is not qualified then whether it is resolved to invoker’s to schema or definer’s depends upon the whether invoker rights are used or definer rights are used.

AUTHID Clause

This clause is used to specify whether a procedure is to be executed under definer rights or invoker rights.


[AUTHID {CURRENT_USER | DEFINER}]

If CURRENT_USER option is used then invoker’s rights are used.

If DEFINER option is used then the rights of the definer of the procedure will be used. This is the default.

Name Resolution

When invoker rights are used the unqualified object used in the procedure will be resolved to the invoker schema at runtime. But, as PL/SQL must resolve all references to the schema objects at the time of compilation, the definer must have all the object that are used in the procedure in his schema. For this purpose the definer may have to create template objects. At runtime, the actual object and the template objects used by definer must exactly match, otherwise it will result in error.

Resolving objects used in Views and Triggers

For invoker-rights routines executed within a view expression, the view owner, not the view user, is considered to be the invoker. For example, if user PRANEETH creates a view, as follows:


 CREATE VIEW vizag AS SELECT  * from addresses where city='Vizag';

The table ADDRESSES is taken from user PRANEETH, who is the view owner.

This rule also applies to database triggers.

Note: The definer of the procedure must give EXECUTE privilege on the procedure before any other user can call it.

NOCOPY Compiler Hint

By default OUT and IN OUT parameters of a procedure are passed by value. That is, the value of OUT or IN OUT parameter is passed to corresponding formal parameter in the procedure. When the procedure terminates successfully then the value of OUT and IN OUT parameters are copied back to corresponding actual parameters.

When a parameter contains large amount of data such as a large record or a large character variable then copying data from actual parameter to formal parameter is time consuming and also consumes more space in memory.

To solve this problem, Oracle8i provided a new compiler hint – NOCOPY. This is used topass OUT and IN OUT parameters by reference. That means, a reference of actual parameter is passed to formal parameter and as the result even formal parameter will refer to the same memory location as the actual parameter.

However, note that NOCOPY is only a hint and not a directive. So PL/SQL may ignore the hint and pass the parameter by value even though you ask for reference.

Here is an example, where a varray consisting of 200 Project_type objects is passed by reference using NOCOPY hint.


DECLARE
   TYPE Projects_array IS VARRAY (200) OF Project_type;
   PROCEDURE reorganize (Empno in Number, 
                         projects  IN OUT NOCOPY Projects_array )
   IS ...

The following important points are to be remembered while dealing with NOCOPY compiler hint.

Note: When an OUT parameter is passed by value, only when procedure succeeds the value of formal parameter is copied to actual parameter. So the value of actual parameter is always reliable. But with pass by reference as the actual parameter is directly modified by the corresponding formal parameter, if procedure fails, then the value of the actual parameter is not reliable and the changes made to actual parameter by corresponding formal parameter are not rolled back.

Something To Try

Is it possible to use a placeholder in place of a schema object?
How do you create a procedure so that the objects used in it are resolved to schema of the invoking user and the defining users.
What happens to main transaction when an autonomous transaction starts.
Is it possible to create a procedure where some objects are resolved to specific schema and other objects to invoker’s schema.
Create a function that takes the year and returns the number of rows in sales table of the year. Assume sales table is named as Salesyyyy ( where yyyy refer to year).
Create a database trigger on Addresses table that writes a record into logchanges table for each change made to Address table. The details should contain the user, date and time, and kind of change(Insert, delete or update).