select object_name 
  from user_objects
 where object_type='PROCEDURE';

create or replace procedure ShowTestTable as
begin
  for v_row in (select name, id from pl_test) loop
    dbms_output.put_line('name: ' || v_row.name ||  '   id: ' || v_row.id);
  end loop;
end;

SQL> select object_name, status
  2    from user_objects
  3   where object_type='PROCEDURE' or object_type='FUNCTION';

OBJECT_NAME          STATUS
--------------------------
ADDUSER              VALID
ADD_TEST_VALUE       VALID
GET_SUM              VALID
INSERT_IN_TEST       VALID
PRINTREQUIRED        VALID
SHOWTESTTABLE        VALID

SQL> alter table pl_test add phone number(7);

Table altered.

SQL> select object_name, status
  2    from user_objects
  3   where object_type='PROCEDURE' or object_type='FUNCTION';

OBJECT_NAME          STATUS
--------------------------
ADDUSER              VALID
ADD_TEST_VALUE       VALID
GET_SUM              VALID
INSERT_IN_TEST       VALID
PRINTREQUIRED        VALID
SHOWTESTTABLE        INVALID

SQL> exec ShowTestTable;

The kind of the dependency we just discussed is called direct dependency. Oracle also takes care of indirect dependencies, when a procedure doesn't directly refer to a changed object, but it refers to another object which becomes invalid upon those changes. We can illustrate this kind of dependency by creating another procedure that doesn't do anything to the pl_test table directly, but calls the ShowTestTable function.

The situations with packages is different, however. This difference is caused by the fact that a package consists of two parts the header and the body. The body of a package depends on the header, but the header does not depend on the body. All outside procedures that call procedures from this package depend only on the header. Thus, if a package body becomes invalid, it doesn't influence the header or outside procedures. This situation is very well illustrated by the example from Scott Urman book.

Privileges and stored subprograms

execute privilege

Case 1: In the first case we'll assume that UserA owns all the objects listed above, and the UserB was only granted the execute privilege on the RecordFullClass

SQL> grant execute on RecordFullClass to UserB;

Case 2: The second case is very similar to the first one, except this time we assume that UserB also has a table temp_table.

Case 3: In the last case we assume that UserB owns the RecordFullClass procedure. Of course, in this case this procedure needs to be a little bit different. We need to manually specify that it should read from the table classes from the UseA schema and should call the function AlmostFull that belongs to the UserA:

create or replace procedure RecordFullClass as
  cursor c_Classes is select department, course from UserA.classes;
begin
  for c_ClassRec in c_Classes loop
    if UserA.AlmostFull(v_ClassRec.department, v_ClassRec) then
      insert into temp_table (char_col)
        values (v_ClassRec.department||' '||v_ClassRec||' is almost full');
    end if;
  end loop;
end RecordFullClass;

SQL> grant execute on AlmostFull to UserB;
SQL> grant select on classes to UserB;

There is one more thing we would like to point out.

If a subprogram needs some privileges these privileges must be granted explicitly and not through a role.

Invoker's versus definer's rights

This default behavior can be changed (starting from Oracle8i). In an invoker's rights subprogram, external references are resolved under the privilege set of the caller, not the owner. An invoker's rights routine is created by using authid clause. It is valid for stand-alone subprograms, package specifications, and object type specifications only. The general syntax of using this clause is

create [or replace] function function_name [(parameter_list)] return return_type 
  [authid {current user | definer}] 
  {as | is}
  function_body;

create [or replace] function procedure_name [(parameter_list)]  
  [authid {current user | definer}] 
  {as | is}
  procedure_body;

create or replace package package_name
  [authid {current user | definer}]
  {as | is}
  package_specification; 

Using stored functions in SQL statements

Depending of the purity level of a function, it is subject to the following restrictions:

• Any function called from a SQL statement should be WNDS (cannot modify any database tables).
  Note: in Oracle8i and higher, a function may modify tables if it's called not from a select statement.
• In order to be executed remotely or in parallel, a function must be RNPS and WNPS levels.
• Function called from select, values, or set clause can write packaged variables. Function in all other clauses must have the WNPS purity level.
• Regardless of their purity level, stored PL/SQL functions cannot be called from a check constraint clause of a create table or alter table command, or be used as a default value for a column, because these situations require an unchanged definition.

In addition to these restrictions, a user-defined functions must also meet the following requirements to be callable from a SQL statement:

• The function has to be stored in the database, either stand-alone or as a part of a package. It mast not be local to another block.
• The function is allowed to take only in parameters, no out or in out.
• The formal parameters must use only database types, not PL/SQL types such as boolean or record.
• The return type of a function must also be a database type.
• The function must not end the current transaction with commit or rollback, or rollback to a savepoint prior to the function execution.
• The function must also not issue any alter session or alter system commands.

Here is an example of a function that can be used in a SQL statement

create or replace function Balance(end_date in date) return number as
  res BankAccount.amount%type;
begin
  select sum(amount) 
    into res 
    from BankAccount 
   where tdate <= end_date;
  return res;
end Balance;

In class assignment:

1. Write a function that checks if the student with given ID took all prerequisite courses for the given course.
2. Write a function that returns the number of students that may take a particular class; that is, they have taken all prerequisites.
3. Create a package with these two functions. Make one of them global and the other local.
4. Use the second function to print all the classes offered next semester and the number of students that may take these courses.

References: