PLSQL-FORWARD DECLERATION,PRIVILEGES

FORWARD DECLERATION

Before going to use the procedure in any other subprogram or other block , you must declare the prototype of the procedure in declarative section.

Ex1:

DECLARE

      PROCEDURE P1 IS

      BEGIN

         dbms_output.put_line('From procedure p1');

         p2;

      END P1;

      PROCEDURE P2 IS

      BEGIN

         dbms_output.put_line('From procedure p2');

         p3;

      END P2;

      PROCEDURE P3 IS

      BEGIN

         dbms_output.put_line('From procedure p3');

      END P3;

BEGIN

     p1;

END;

Output:

p2;

*

ERROR at line 5:

ORA-06550: line 5, column 1:

PLS-00313: 'P2' not declared in this scope

ORA-06550: line 5, column 1:

PL/SQL: Statement ignored

ORA-06550: line 10, column 1:

PLS-00313: 'P3' not declared in this scope

ORA-06550: line 10, column 1:

PL/SQL: Statement ignored

Ex2:

DECLARE

      PROCEDURE P2;   --  forward declaration

      PROCEDURE P3;  

      PROCEDURE P1 IS

      BEGIN

         dbms_output.put_line('From procedure p1');

         p2;

      END P1;

      PROCEDURE P2 IS

      BEGIN

         dbms_output.put_line('From procedure p2');

         p3;

      END P2;

      PROCEDURE P3 IS

      BEGIN

         dbms_output.put_line('From procedure p3');

      END P3;

BEGIN

     p1;

END;

Output:

From procedure p1

From procedure p2

From procedure p3

PRIVILEGES AND STORED SUBPROGRAMS

EXECUTE PREVILEGE

Ø  For stored subprograms and packages the relevant privilege is EXECUTE.

Ø  If user A had the procedure called emp_proc then user A grants execute privilege on procedure to user B with the following command.

SQL> Grant execute on emp_proc to user B.

Ø  Then user B can run the procedure by issuing

SQL> Exec user A.emp_proc

userA created the following procedure

CREATE OR REPLACE PROCEDURE P IS

     cursor is select *from student1;

BEGIN

     for v in c loop

           insert into student2 values(v.no,v.name,v.marks);

     end loop;

END P;

userA granted execute privilege to userB using

SQL> grant execute on p to userB

Then userB executed the procedure

SQL> Exec userA.p

If suppose userB also having student2 table then which table will populate whether userA’s or userB’s.

The answer is userA’s student2 table only because by default the procedure will execute under the privlige set of its owner.

The above procedure is known as definer’s procedure.

HOW TO POPULATE USER B’s TABLE

Ø  Oracle introduces Invoker’s and Definer’s rights.

Ø  By default it will use the definer’s rights.

Ø  An invoker’s rights routine can be created by using AUTHID clause to populate the

     userB’s table.

Ø  It is valid for stand-alone subprograms, package specifications, and object type

     specifications only.

userA created the following procedure

CREATE OR REPLACE PROCEDURE P

AUTHID CURRENT_USER IS

      cursor is select *from student1;

BEGIN

      for v in c loop

            insert into student2 values(v.no,v.name,v.marks);

      end loop;

END P;

Then grant execute privilege on p to userB.

Executing the procedure by userB, which populates userB’s table.

The above procedure is called invoker’s procedure.

Instead of current_user of authid clause, if you use definer then it will be called definer’ procedure.

STORED SUBPROGRAMS AND ROLES

we have two users saketh and sudha in which saketh has student table and sudha does not.

Sudha is going to create a procedure based on student table owned by saketh. Before doing this saketh must grant the permissions on this table to sudha.

SQL> conn saketh/saketh

SQL> grant all on student to sudha;

then sudha can create procedure

SQL> conn sudha/sudha

CREATE OR REPLACE PROCEDURE P IS

      cursor c is select *from saketh.student;

BEGIN

      for v in c loop

            dbms_output.put_line(‘No = ‘ || v.no);

      end loop;

END P;

here procedure will be created.

If the same privilege was granted through a role it wont create the procedure.

Examine the following code

SQL> conn saketh/saketh

SQL> create role saketh_role;

SQL> grant all on student to saketh_role;

SQL> grant saketh_role to sudha;

then conn sudha/sudha

CREATE OR REPLACE PROCEDURE P IS

      cursor c is select *from saketh.student;

BEGIN

      for v in c loop

            dbms_output.put_line(‘No = ‘ || v.no);

      end loop;

END P;

The above code will raise error instead of creating procedure .

This is because of early binding which PL/SQL uses by default in which references are evaluated in compile time but when you are using a role this will affect immediately.