Example 8-1 Simple PL/SQL Procedure

DECLARE  in_string  VARCHAR2(100) := 'This is my test string.';  out_string VARCHAR2(200);  PROCEDURE double ( original IN VARCHAR2, new_string OUT VARCHAR2 ) AS    BEGIN      new_string := original || ' + ' || original;      EXCEPTION      WHEN VALUE_ERROR THEN        DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.');    END;BEGIN  double(in_string, out_string);  DBMS_OUTPUT.PUT_LINE(in_string || ' - ' || out_string);END;/

Example 8-2 Simple PL/SQL Function

DECLARE  FUNCTION square(original NUMBER)    RETURN NUMBER AS original_squared NUMBER;    BEGIN      original_squared := original * original;      RETURN original_squared;    END;BEGIN  DBMS_OUTPUT.PUT_LINE(square(100));END;/

Using the RETURN Statement

TheRETURN statement immediately ends the execution of a subprogram and returns control to the caller. Execution continues with the statement following the subprogram call. (Do not confuse theRETURN statement with theRETURN clause in a function spec, which specifies the datatype of the return value.)

A subprogram can contain severalRETURN statements. The subprogram does not have to conclude with aRETURN statement. Executing anyRETURN statement completes the subprogram immediately.

In procedures, aRETURN statement does not return a value and so cannot contain an expression. The statement returns control to the caller before the end of the procedure.

In functions, aRETURN statement must contain an expression, which is evaluated when theRETURN statement is executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in theRETURN clause. See the use of theRETURN statement inExample 8-2.

The expression in a functionRETURN statement can be arbitrarily complex:

CREATE OR REPLACE FUNCTION half_of_square(original NUMBER)  RETURN NUMBER ISBEGIN   RETURN (original * original)/2 + (original * 4);END half_of_square;/

In a function, there must be at least one execution path that leads to aRETURN statement. Otherwise, you get afunction returned without value error at run time.

Example 8-3 Forward Declaration for a Nested Subprogram

DECLARE   PROCEDURE proc1(number1 NUMBER);  -- forward declaration   PROCEDURE proc2(number2 NUMBER) IS      BEGIN        proc1(number2);  -- calls proc1      END;    PROCEDURE proc1(number1 NUMBER) IS      BEGIN      proc2 (number1);  -- calls proc2     END;BEGIN  NULL;END;/

Actual Versus Formal Subprogram Parameters

Subprograms pass information using parameters:

• The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.

• The variables or expressions passed from the calling subprogram are actual parameters.

A good programming practice is to use different names for actual and formal parameters.

When you call a procedure, the actual parameters are evaluated and the results are assigned to the corresponding formal parameters. If necessary, before assigning the value of an actual parameter to a formal parameter, PL/SQL converts the datatype of the value. For example, if you pass a number when the procedure expects a string, PL/SQL converts the parameter so that the procedure receives a string.

The actual parameter and its corresponding formal parameter must have compatible datatypes. For instance, PL/SQL cannot convert between theDATE andNUMBER datatypes, or convert a string to a number if the string contains extra characters such as dollar signs.

The procedure inExample 8-4 declares two formal parameters namedemp_id andamount and the procedure call specifies actual parametersemp_num andbonus.

DECLARE  emp_num NUMBER(6) := 120;  bonus   NUMBER(6) := 100;  merit   NUMBER(4) := 50;  PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS    BEGIN      UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;  END raise_salary;BEGIN  raise_salary(emp_num, bonus); -- procedure call specifies actual parameters  raise_salary(emp_num, merit + bonus); -- expressions can be used as parametersEND;/

Using Positional, Named, or Mixed Notation for Subprogram Parameters

When calling a subprogram, you can write the actual parameters using either:

• Positional notation. You specify the same parameters in the same order as they are declared in the procedure.

  This notation is compact, but if you specify the parameters (especially literals) in the wrong order, the bug can be hard to detect. You must change your code if the procedure's parameter list changes.

• Named notation. You specify the name of each parameter along with its value. An arrow (=>) serves as the association operator. The order of the parameters is not significant.

  This notation is more verbose, but makes your code easier to read and maintain. You can sometimes avoid changing your code if the procedure's parameter list changes, for example if the parameters are reordered or a new optional parameter is added. Named notation is a good practice to use for any code that calls someone else's API, or defines an API for someone else to use.

• Mixed notation. You specify the first parameters with positional notation, then switch to named notation for the last parameters.

  You can use this notation to call procedures that have some required parameters, followed by some optional parameters.

Example 8-5 shows equivalent procedure calls using positional, named, and mixed notation.

DECLARE  emp_num NUMBER(6) := 120;  bonus   NUMBER(6) := 50;  PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS    BEGIN      UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;  END raise_salary;BEGIN  raise_salary(emp_num, bonus); -- positional procedure call for actual parameters  raise_salary(amount => bonus, emp_id => emp_num); -- named parameters  raise_salary(emp_num, amount => bonus); -- mixed parametersEND;/

Specifying Subprogram Parameter Modes

You use parameter modes to define the behavior of formal parameters. The three parameter modes areIN (the default),OUT, andINOUT.

Any parameter mode can be used with any subprogram. Avoid using theOUT andINOUT modes with functions. To have a function return multiple values is a poor programming practice. Also, functions should be free fromside effects, which change the values of variables not local to the subprogram.

DECLARE  emp_num       NUMBER(6) := 120;  bonus         NUMBER(6) := 50;  emp_last_name VARCHAR2(25);  PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER,                           emp_name OUT VARCHAR2) IS    BEGIN      UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;      SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;  END raise_salary;BEGIN  raise_salary(emp_num, bonus, emp_last_name);   DBMS_OUTPUT.PUT_LINE('Salary has been updated for: ' || emp_last_name);END;/

Using Default Values for Subprogram Parameters

By initializingIN parameters to default values, you can pass different numbers of actual parameters to a subprogram, accepting the default values for any parameters you omit. You can also add new formal parameters without having to change every call to the subprogram.

If a parameter is omitted, the default value of its corresponding formal parameter is used. You cannot skip a formal parameter by leaving out its actual parameter. To omit the first parameter and specify the second, use named notation.

You cannot assign a null to an uninitialized formal parameter by leaving out its actual parameter. You must pass the null explicitly, or you can specify a default value ofNULL in the declaration.

Example 8-7 illustrates the use of default values for subprogram parameters.

DECLARE  emp_num NUMBER(6) := 120;  bonus   NUMBER(6);  merit   NUMBER(4);  PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER DEFAULT 100,                           extra IN NUMBER DEFAULT 50) IS    BEGIN      UPDATE employees SET salary = salary + amount + extra         WHERE employee_id = emp_id;  END raise_salary;BEGIN  raise_salary(120); -- same as raise_salary(120, 100, 50)  raise_salary(emp_num, extra => 25); -- same as raise_salary(120, 100, 25)END;/

Example 8-8 Overloading a Subprogram Name

DECLARE   TYPE DateTabTyp IS TABLE OF DATE INDEX BY PLS_INTEGER;   TYPE NumTabTyp IS TABLE OF NUMBER INDEX BY PLS_INTEGER;   hiredate_tab DateTabTyp;   sal_tab NumTabTyp;   PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS     BEGIN        FOR i IN 1..n LOOP          tab(i) := SYSDATE;     END LOOP;   END initialize;   PROCEDURE initialize (tab OUT NumTabTyp, n INTEGER) IS     BEGIN       FOR i IN 1..n LOOP         tab(i) := 0.0;     END LOOP;   END initialize;BEGIN   initialize(hiredate_tab, 50);  -- calls first (DateTabTyp) version   initialize(sal_tab, 100);      -- calls second (NumTabTyp) versionEND;/

Guidelines for Overloading with Numeric Types

You can overload two subprograms if their formal parameters differ only in numeric datatype. This technique might be useful in writing mathematical application programming interfaces (APIs), where several versions of a function could use the same name, each accepting a different numeric type. For example, a function acceptingBINARY_FLOAT might be faster, while a function acceptingBINARY_DOUBLE might provide more precision.

To avoid problems or unexpected results passing parameters to such overloaded subprograms:

• Make sure to test that the expected version of a subprogram is called for each set of expected parameters. For example, if you have overloaded functions that acceptBINARY_FLOAT andBINARY_DOUBLE, which is called if you pass aVARCHAR2 literal such as '5.0'?

• Qualify numeric literals and use conversion functions to make clear what the intended parameter types are. For example, use literals such as5.0f (forBINARY_FLOAT),5.0d (forBINARY_DOUBLE), or conversion functions such asTO_BINARY_FLOAT(),TO_BINARY_DOUBLE(), andTO_NUMBER().

PL/SQL looks for matching numeric parameters starting withPLS_INTEGER orBINARY_INTEGER, thenNUMBER, thenBINARY_FLOAT, thenBINARY_DOUBLE. The first overloaded subprogram that matches the supplied parameters is used. AVARCHAR2 value can match aNUMBER,BINARY_FLOAT, orBINARY_DOUBLE parameter.

For example, consider theSQRT function, which takes a single parameter. There are overloaded versions that accept aNUMBER, aBINARY_FLOAT, or aBINARY_DOUBLE parameter. If you pass aPLS_INTEGER parameter, the first matching overload (using the order given in the preceding paragraph) is the one with aNUMBER parameter, which is likely to be the slowest. To use one of the faster versions, use theTO_BINARY_FLOAT orTO_BINARY_DOUBLE functions to convert the parameter to the right datatype.

For another example, consider theATAN2 function, which takes two parameters of the same type. If you pass two parameters of the same type, you can predict which overloaded version is used through the same rules as before. If you pass parameters of different types, for example onePLS_INTEGER and oneBINARY_FLOAT, PL/SQL tries to find a match where both parameters use the higher type. In this case, that is the version ofATAN2 that takes twoBINARY_FLOAT parameters; thePLS_INTEGER parameter is converted upwards.

The preference for converting upwards holds in more complicated situations. For example, you might have a complex function that takes two parameters of different types. One overloaded version might take aPLS_INTEGER and aBINARY_FLOAT parameter. Another overloaded version might take aNUMBER and aBINARY_DOUBLE parameter. What happens if you call this procedure name and pass twoNUMBER parameters? PL/SQL looks upward first to find the overloaded version where the second parameter isBINARY_FLOAT. Because this parameter is a closer match than theBINARY_DOUBLE parameter in the other overload, PL/SQL then looks downward and converts the firstNUMBER parameter toPLS_INTEGER.

Restrictions on Overloading

Only local or packaged subprograms, or type methods, can be overloaded. You cannot overload standalone subprograms.

You cannot overload two subprograms if their formal parameters differ only in name or parameter mode. For example, you cannot overload the following two procedures:

DECLARE   PROCEDURE balance (acct_no IN INTEGER) IS   BEGIN NULL; END;   PROCEDURE balance (acct_no OUT INTEGER) IS   BEGIN NULL; END;BEGIN  DBMS_OUTPUT.PUT_LINE('The following procedure call raises an error.');-- balance(100); raises an error because the procedure declaration is not uniqueEND;/

You cannot overload subprograms whose parameters differ only in subtype. For example, you cannot overload procedures where one accepts anINTEGER parameter and the other accepts aREAL parameter, even thoughINTEGER andREAL are both subtypes ofNUMBER and so are in the same family.

You cannot overload two functions that differ only in the datatype of the return value, even if the types are in different families. For example, you cannot overload two functions where one returnsBOOLEAN and the other returnsINTEGER.

Figure 8-1 How the PL/SQL Compiler Resolves Calls

Example 8-10 Resolving PL/SQL Procedure Names

DECLARE  PROCEDURE swap (n1 NUMBER, n2 NUMBER) IS    num1 NUMBER;    num2 NUMBER;    FUNCTION balance (bal NUMBER) RETURN NUMBER IS      x NUMBER := 10;      PROCEDURE swap (d1 DATE, d2 DATE) IS BEGIN NULL; END;      PROCEDURE swap (b1 BOOLEAN, b2 BOOLEAN) IS BEGIN NULL; END;    BEGIN      DBMS_OUTPUT.PUT_LINE('The following raises an error');--      swap(num1, num2); wrong number or types of arguments in call to 'SWAP'      RETURN x;    END balance;  BEGIN NULL;END swap;BEGIN  NULL;END;/

How Overloading Works with Inheritance

The overloading algorithm allows substituting a subtype value for a formal parameter that is a supertype. This capability is known assubstitutability. If more than one instance of an overloaded procedure matches the procedure call, the following rules apply to determine which procedure is called:

If the only difference in the signatures of the overloaded procedures is that some parameters are object types from the same supertype-subtype hierarchy, the closest match is used. The closest match is one where all the parameters are at least as close as any other overloaded instance, as determined by the depth of inheritance between the subtype and supertype, and at least one parameter is closer.

A semantic error occurs when two overloaded instances match, and some argument types are closer in one overloaded procedure to the actual arguments than in any other instance.

A semantic error also occurs if some parameters are different in their position within the object type hierarchy, and other parameters are of different datatypes so that an implicit conversion would be necessary.

For example, create a type hierarchy with three levels and then declare two overloaded instances of a function, where the only difference in argument types is their position in this type hierarchy, as shown inExample 8-11. We declare a variable of typefinal_t, then call the overloaded function. The instance of the function that is executed is the one that accepts asub_t parameter, because that type is closer tofinal_t in the hierarchy thansuper_t is.

CREATE OR REPLACE TYPE super_t AS OBJECT  (n NUMBER) NOT final;/CREATE OR REPLACE TYPE sub_t UNDER super_t  (n2 NUMBER) NOT final;/CREATE OR REPLACE TYPE final_t UNDER sub_t  (n3 NUMBER);/CREATE OR REPLACE PACKAGE p IS   FUNCTION func (arg super_t) RETURN NUMBER;   FUNCTION func (arg sub_t) RETURN NUMBER;END;/CREATE OR REPLACE PACKAGE BODY p IS   FUNCTION func (arg super_t) RETURN NUMBER IS BEGIN RETURN 1; END;   FUNCTION func (arg sub_t) RETURN NUMBER IS BEGIN RETURN 2; END;END;/DECLARE  v final_t := final_t(1,2,3);BEGIN  DBMS_OUTPUT.PUT_LINE(p.func(v));  -- prints 2END;/

InExample 8-11, the choice of which instance to call is made at compile time. InExample 8-12, this choice is made dynamically. We declarev as an instance ofsuper_t, but because we assign a value ofsub_t to it, the appropriate instance of the function is called. This feature is known asdynamic dispatch.

CREATE TYPE super_t AS OBJECT  (n NUMBER, MEMBER FUNCTION func RETURN NUMBER) NOT final;/CREATE TYPE BODY super_t AS MEMBER FUNCTION func RETURN NUMBER IS BEGIN RETURN 1; END; END;/CREATE OR REPLACE TYPE sub_t UNDER super_t  (n2 NUMBER,   OVERRIDING MEMBER FUNCTION func RETURN NUMBER) NOT final;/CREATE TYPE BODY sub_t AS OVERRIDING MEMBER FUNCTION func RETURN NUMBER IS BEGIN RETURN 2; END; END;/CREATE OR REPLACE TYPE final_t UNDER sub_t  (n3 NUMBER);/DECLARE  v super_t := final_t(1,2,3);BEGIN  DBMS_OUTPUT.PUT_LINE(v.func); -- prints 2END;/

Example 8-13 Specifying Invoker's Rights With a Procedure

CREATE OR REPLACE PROCEDURE create_dept (   v_deptno NUMBER,   v_dname  VARCHAR2,   v_mgr    NUMBER,   v_loc    NUMBER) AUTHID CURRENT_USER ASBEGIN   INSERT INTO departments VALUES (v_deptno, v_dname, v_mgr, v_loc);END;/CALL create_dept(44, 'Information Technology', 200, 1700);

Advantages of Invoker's Rights

Invoker's rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas. All the schemas in one instance can call procedures owned by a central schema. You can even have schemas in different instances call centralized procedures using a database link.

Consider a company that uses a stored procedure to analyze sales. If the company has several schemas, each with a similarSALES table, normally it would also need several copies of the stored procedure, one in each schema.

To solve the problem, the company installs an invoker's rights version of the stored procedure in a central schema. Now, all the other schemas can call the same procedure, which queries the appropriate toSALES table in each case.

You can restrict access to sensitive data by calling from an invoker's rights subprogram to a definer's rights subprogram that queries or updates the table containing the sensitive data. Although multiple users can call the invoker's rights subprogram, they do not have direct access to the sensitive data.

Specifying the Privileges for a Subprogram with the AUTHID Clause

To implement invoker's rights, use theAUTHID clause, which specifies whether a subprogram executes with the privileges of its owner or its current user. It also specifies whetherexternal references (that is, references to objects outside the subprogram) are resolved in the schema of the owner or the current user.

TheAUTHID clause is allowed only in the header of a standalone subprogram, a package spec, or an object type spec. In theCREATE FUNCTION,CREATE PROCEDURE,CREATE PACKAGE, orCREATE TYPE statement, you can include eitherAUTHID CURRENT_USER orAUTHID DEFINER immediately before theIS orAS keyword that begins the declaration section.

DEFINER is the default option. In a package or object type, theAUTHID clause applies to all subprograms.

Most supplied PL/SQL packages (such asDBMS_LOB,DBMS_PIPE,DBMS_ROWID,DBMS_SQL, andUTL_REF) are invoker's rights packages.

Who Is the Current User During Subprogram Execution?

In a sequence of calls, whenever control is inside an invoker's rights subprogram, the current user is the session user. When a definer's rights subprogram is called, the owner of that subprogram becomes the current user. The current user might change as new subprograms are called or as subprograms exit.

To verify who the current user is at any time, you can check theUSER_USERS data dictionary view. Inside an invoker's rights subprogram, the value from this view might be different from the value of theUSER built-in function, which always returns the name of the session user.

How External References Are Resolved in Invoker's Rights Subprograms

If you specifyAUTHIDCURRENT_USER, the privileges of the current user are checked at run time, and external references are resolved in the schema of the current user. However, this applies only to external references in:

• SELECT,INSERT,UPDATE, andDELETE data manipulation statements

• TheLOCKTABLE transaction control statement

• OPEN andOPEN-FOR cursor control statements

• EXECUTEIMMEDIATE andOPEN-FOR-USING dynamic SQL statements

• SQL statements parsed usingDBMS_SQL.PARSE()

For all other statements, the privileges of the owner are checked at compile time, and external references are resolved in the schema of the owner. For example, the assignment statement inExample 8-14 refers to the packaged functionnum_above_salary in theemp_actions package inExample 1-13. This external reference is resolved in the schema of the owner of procedureabove_salary.

CREATE PROCEDURE above_salary (emp_id IN NUMBER)  AUTHID CURRENT_USER AS  emps NUMBER;BEGIN  emps := emp_actions.num_above_salary(emp_id);  DBMS_OUTPUT.PUT_LINE( 'Number of employees with higher salary: ' ||                          TO_CHAR(emps));END;/CALL above_salary(120);

Overriding Default Name Resolution in Invoker's Rights Subprograms

Occasionally, you might want an unqualified name to refer to some particular schema, not the schema of the caller. In the same schema as the invoker's rights subprogram, create a public synonym for the table, procedure, function, or other object using theCREATESYNONYM statement:

CREATE PUBLIC SYNONYM emp FOR hr.employees;

When the invoker's rights subprogram refers to this name, it will match the synonym in its own schema, which resolves to the object in the specified schema. This technique does not work if the calling schema already has a schema object or private synonym with the same name. In that case, the invoker's rights subprogram must fully qualify the reference.

Granting Privileges on Invoker's Rights Subprograms

To call a subprogram directly, users must have theEXECUTE privilege on that subprogram. By granting the privilege, you allow a user to:

• Call the subprogram directly

• Compile functions and procedures that call the subprogram

For external references resolved in the current user's schema (such as those in DML statements), the current user must have the privileges needed to access schema objects referenced by the subprogram. For all other external references (such as function calls), the owner's privileges are checked at compile time, and no run-time check is done.

A definer's rights subprogram operates under the security domain of its owner, no matter who is executing it. The owner must have the privileges needed to access schema objects referenced by the subprogram.

You can write a program consisting of multiple subprograms, some with definer's rights and others with invoker's rights. Then, you can use theEXECUTE privilege to restrict program entry points. That way, users of an entry-point subprogram can execute the other subprograms indirectly but not directly.

Granting Privileges on an Invoker's Rights Subprogram: Example

Suppose userUTIL grants theEXECUTE privilege on subprogramFFT to userAPP:

GRANT EXECUTE ON util.fft TO app;

Now, userAPP can compile functions and procedures that call subprogramFFT. At run time, no privilege checks on the calls are done. AsFigure 8-2 shows, userUTIL need not grant theEXECUTE privilege to every user who might callFFT indirectly.

Since subprogramutil.fft is called directly only from invoker's rights subprogramapp.entry, userutil must grant theEXECUTE privilege only to userAPP. WhenUTIL.FFT is executed, its current user could beAPP,SCOTT, orBLAKE even thoughSCOTT andBLAKE were not granted theEXECUTE privilege.

Figure 8-2 Indirect Calls to an Invoker's Rights Subprogram

Description of the illustration lnpls026.gif

Using Roles with Invoker's Rights Subprograms

The use of roles in a subprogram depends on whether it executes with definer's rights or invoker's rights. Within a definer's rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.

Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.

Using Views and Database Triggers with Invoker's Rights Subprograms

For invoker's rights subprograms executed within a view expression, the schema that created the view, not the schema that is querying the view, is considered to be the current user. This rule also applies to database triggers.

Using Database Links with Invoker's Rights Subprograms

You can create a database link to use invoker's rights:

A current-user link lets you connect to a remote database as another user, with that user's privileges. To connect, Oracle uses the username of the current user (who must be a global user). Suppose an invoker's rights subprogram owned by userOE references the following database link. If global userHR calls the subprogram, it connects to the Dallas database as userHR, who is the current user.

CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...

If it were a definer's rights subprogram, the current user would beOE, and the subprogram would connect to the Dallas database as global userOE.

Using Object Types with Invoker's Rights Subprograms

To define object types for use in any schema, specify theAUTHIDCURRENT_USER clause. For information on object types, seeOracle Database Application Developer's Guide - Object-Relational Features.

Suppose userHR creates the following object type:

CREATE TYPE person_typ AUTHID CURRENT_USER AS OBJECT (  person_id   NUMBER,  person_name VARCHAR2(30),  person_job  VARCHAR2(10),  STATIC PROCEDURE new_person_typ (    person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2,     schema_name VARCHAR2, table_name VARCHAR2),  MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ, new_job VARCHAR2) );/CREATE TYPE BODY person_typ AS  STATIC PROCEDURE new_person_typ (    person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2,     schema_name VARCHAR2, table_name VARCHAR2) IS    sql_stmt VARCHAR2(200);  BEGIN     sql_stmt := 'INSERT INTO ' || schema_name || '.'       || table_name || ' VALUES (HR.person_typ(:1, :2, :3))';    EXECUTE IMMEDIATE sql_stmt USING person_id, person_name, person_job;  END;  MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ, new_job VARCHAR2) IS  BEGIN    person_job := new_job;  END;END;/

Then, userHR grants theEXECUTE privilege on object typeperson_typ to userOE:

GRANT EXECUTE ON person_typ TO OE;

Finally, userOE creates an object table to store objects of typeperson_typ, then calls procedurenew_person_typ to populate the table:

CONNECT oe/oe;CREATE TABLE person_tab OF hr.person_typ;BEGIN  hr.person_typ.new_person_typ(1001, 'Jane Smith', 'CLERK', 'oe', 'person_tab');  hr.person_typ.new_person_typ(1002, 'Joe Perkins', 'SALES','oe', 'person_tab');  hr.person_typ.new_person_typ(1003, 'Robert Lange', 'DEV','oe', 'person_tab');END;/

The calls succeed because the procedure executes with the privileges of its current user (OE), not its owner (HR).

For subtypes in an object type hierarchy, the following rules apply:

• If a subtype does not explicitly specify anAUTHID clause, it inherits theAUTHID of its supertype.

• If a subtype does specify anAUTHID clause, itsAUTHID must match theAUTHID of its supertype. Also, if theAUTHID isDEFINER, both the supertype and subtype must have been created in the same schema.

-- oe creates a procedure that calls change_jobCREATE PROCEDURE reassign (p IN OUT NOCOPY hr.person_typ, new_job VARCHAR2) ASBEGIN   p.change_job(new_job); -- executes with the privileges of oeEND;/-- OE grants EXECUTE to HR on procedure reassignGRANT EXECUTE ON reassign to HR;CONNECT hr/hr-- user hr passes a person_typ object to the procedure reassignDECLARE   p1 person_typ;BEGIN   p1 := person_typ(1004,  'June Washburn', 'SALES');   oe.reassign(p1, 'CLERK'); -- current user is oe, not hrEND;/

What Is a Recursive Subprogram?

A recursive subprogram is one that calls itself. Each recursive call creates a new instance of any items declared in the subprogram, including parameters, variables, cursors, and exceptions. Likewise, new instances of SQL statements are created at each level in the recursive descent.

Be careful where you place a recursive call. If you place it inside a cursorFOR loop or betweenOPEN andCLOSE statements, another cursor is opened at each call, which might exceed the limit set by the Oracle initialization parameterOPEN_CURSORS.

There must be at least two paths through a recursive subprogram: one that leads to the recursive call and one that does not. At least one path must lead to a terminating condition. Otherwise, the recursion would go on until PL/SQL runs out of memory and raises the predefined exceptionSTORAGE_ERROR.

Example 8-17 Calling an External Procedure From PL/SQL

CREATE OR REPLACE PROCEDURE raise_salary (empid NUMBER, pct NUMBER)ASLANGUAGE JAVANAME 'Adjuster.raiseSalary(int, float)';/DECLARE   emp_id  NUMBER := 120;   percent NUMBER := 10;BEGIN   -- get values for emp_id and percent   raise_salary(emp_id, percent);  -- call external subprogramEND;/

Example 8-18 Calling a Java Function From PL/SQL

-- the following nested Java call spec is not valid, throws PLS-00999--   CREATE PROCEDURE sleep (milli_seconds in number) IS--     PROCEDURE java_sleep (milli_seconds IN NUMBER) AS ...-- first, create the Java call spec, then call from a PL/SQL procedureCREATE PROCEDURE java_sleep (milli_seconds IN NUMBER)  AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';/CREATE PROCEDURE sleep (milli_seconds in number) IS-- the following nested PROCEDURE spec is not legal--  PROCEDURE java_sleep (milli_seconds IN NUMBER)--    AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';BEGIN  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time());  java_sleep (milli_seconds);  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time());END;/

Example 8-19 Aliasing from Passing Global Variable with NOCOPY Hint

DECLARE   TYPE Definition IS RECORD (      word    VARCHAR2(20),      meaning VARCHAR2(200));   TYPE Dictionary IS VARRAY(2000) OF Definition;   lexicon Dictionary := Dictionary();   PROCEDURE add_entry (word_list IN OUT NOCOPY Dictionary) IS   BEGIN     word_list(1).word := 'aardvark';     lexicon(1).word := 'aardwolf';   END;BEGIN   lexicon.EXTEND;   add_entry(lexicon);   DBMS_OUTPUT.PUT_LINE(lexicon(1).word);END;/

The program printsaardwolf if the compiler obeys theNOCOPY hint. The assignment toWORD_LIST is done immediately through a pointer, then is overwritten by the assignment toLEXICON.

The program printsaardvark if theNOCOPY hint is omitted, or if the compiler does not obey the hint. The assignment toWORD_LIST uses an internal copy of the varray, which is copied back to the actual parameter (overwriting the contents ofLEXICON) when the procedure ends.

Example 8-20 Aliasing Passing Same Parameter Multiple Times

DECLARE   n NUMBER := 10;   PROCEDURE do_something (      n1 IN NUMBER,      n2 IN OUT NUMBER,      n3 IN OUT NOCOPY NUMBER) IS   BEGIN      n2 := 20;      DBMS_OUTPUT.put_line(n1);  -- prints 10      n3 := 30;      DBMS_OUTPUT.put_line(n1);  -- prints 30   END;BEGIN   do_something(n, n, n);   DBMS_OUTPUT.put_line(n);  -- prints 20END;/

Example 8-21 Aliasing from Assigning Cursor Variables to Same Work Area

DECLARE  TYPE EmpCurTyp IS REF CURSOR;  c1 EmpCurTyp;  c2 EmpCurTyp;  PROCEDURE get_emp_data (emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp) IS    emp_rec employees%ROWTYPE;  BEGIN    OPEN emp_cv1 FOR SELECT * FROM employees;    emp_cv2 := emp_cv1;    FETCH emp_cv1 INTO emp_rec;  -- fetches first row    FETCH emp_cv1 INTO emp_rec;  -- fetches second row    FETCH emp_cv2 INTO emp_rec;  -- fetches third row    CLOSE emp_cv1;    DBMS_OUTPUT.put_line('The following raises an invalid cursor');--  FETCH emp_cv2 INTO emp_rec; raises invalid cursor when get_emp_data is called  END;BEGIN  get_emp_data(c1, c2);END;/