Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide
Release 11.2.1

Part Number E13076-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

5 Examples Using TimesTen SQL in PL/SQL

This chapter provides additional examples to further explore the tight integration of TimesTen SQL in PL/SQL:

Example using the SELECT statement in PL/SQL

Use the SELECT... INTO statement to retrieve exactly one row of data. TimesTen returns an error for any query that returns no rows or more than one row.

Example 5-1 Using SELECT... INTO to return sum of salaries

This example uses the SELECT...INTO statement to calculate the sum of salaries for all employees in the department where department_id is 60.

Command> DECLARE
       >   v_sum_sal  NUMBER (10,2);
       >   v_dept_no  NUMBER NOT NULL := 60;
       > BEGIN
       >   SELECT SUM(salary) -- aggregate function
       >   INTO v_sum_sal FROM employees
       >   WHERE department_id = v_dept_no;
       >   DBMS_OUTPUT.PUT_LINE ('Sum is ' || v_sum_sal);
       >  END;
       >  /
Sum is 28800
 
PL/SQL procedure successfully completed.

Example using the INSERT statement

Oracle TimesTen In-Memory Database supports the TimesTen DML statements INSERT, UPDATE, DELETE, and MERGE. This section has an example of the INSERT statement.

Example 5-2 Using the INSERT statement in PL/SQL

This example uses the AS SELECT query clause to create table emp_copy, sets AUTOCOMMIT off, creates a sequence to increment employee_id, and uses the INSERT statement in PL/SQL to insert a row of data in table emp_copy.

Command> CREATE TABLE emp_copy AS SELECT * FROM employees;
107 rows inserted.
Command> SET AUTOCOMMIT OFF;

Command> CREATE SEQUENCE emp_copy_seq
       > START WITH 207
       > INCREMENT BY 1;

Command>  BEGIN
       >    INSERT INTO emp_copy
       >      (employee_id, first_name, last_name, email, hire_date, job_id,
       >       salary)
       >    VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
       >      'AD_ASST', 4000);
       >  END;
       >  /
 
PL/SQL procedure successfully completed.

Continuing, the example confirms the row was inserted, then rolls back the transaction.

Command> SELECT * FROM EMP_COPY WHERE first_name = 'Parker';
< 207, Parker, Cores, PCORES, <NULL>, 2008-07-19 21:49:55, AD_ASST, 4000, <NULL>
, <NULL>, <NULL> >
1 row found.
Command> ROLLBACK;
Command>  SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.

Now the INSERT is executed again, then the transaction is rolled back in PL/SQL. Finally, the example verifies that TimesTen did not insert the row.

Command> BEGIN
       >   INSERT INTO emp_copy
       >     (employee_id, first_name, last_name, email, hire_date, job_id,
       >      salary)
       >   VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
       >           'AD_ASST',4000);
       > ROLLBACK;
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.

Examples using input and output parameters and bind variables

The examples in this section use IN parameters, OUT parameters, and IN OUT parameters, including bind variables (host variables) from outside PL/SQL.

Example 5-3 Using IN and OUT parameters

This example creates a procedure query_emp to retrieve information about an employee, passes the employee_id 171 to the procedure, and retrieves the name and salary into two OUT parameters.

Command> CREATE OR REPLACE PROCEDURE query_emp
       >   (p_id IN employees.employee_id%TYPE,
       >    p_name  OUT employees.last_name%TYPE,
       >    p_salary OUT employees.salary%TYPE) IS
       > BEGIN
       >   SELECT last_name, salary INTO p_name, p_salary
       >   FROM employees
       >   WHERE employee_id = p_id;
       > END query_emp;
       > /
 
Procedure created.

Command> -- Execute the procedure
       > DECLARE
       >  v_emp_name employees.last_name%TYPE;
       >  v_emp_sal  employees.salary%TYPE;
       > BEGIN
       >  query_emp (171, v_emp_name, v_emp_sal);
       >  DBMS_OUTPUT.PUT_LINE (v_emp_name || ' earns ' ||
       >   TO_CHAR (v_emp_sal, '$999,999.00'));
       > END;
       > /
Smith earns    $7,400.00
 
PL/SQL procedure successfully completed.

Example 5-4 Using bind variables to execute a procedure

This example uses bind variables to execute procedure query_emp from Example 5-3. Remember to check that data types are compatible.

Command> VARIABLE b_name VARCHAR2 (25);
Command> VARIABLE b_sal  NUMBER;
Command> BEGIN
       >  query_emp (171, :b_name, :b_sal);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_name
B_NAME               : Smith
Command> PRINT b_sal
B_SAL                : 7400

Bind variables can be used to pass data between a user application and PL/SQL.

Example 5-5 Using IN OUT parameters and bind variables

Consider a situation where you want to format a phone number. You decide to use an IN OUT parameter to pass the unformatted phone number to a procedure. After the procedure is executed, the IN OUT parameter contains the formatted phone number value. Procedure FORMAT_PHONE in this example accomplishes that, accepting a 10-character string containing digits for a phone number. Bind variable b_phone_no first provides the input value passed to FORMAT_PHONE, then after execution is used as an output value returning the updated string.

Command> CREATE OR REPLACE PROCEDURE format_phone
       >   (p_phone_no IN OUT VARCHAR2 ) IS
       > BEGIN
       >  p_phone_no := '('  || SUBSTR (p_phone_no,1,3) ||
       >                ') ' || SUBSTR (p_phone_no,4,3) ||
       >                '-'  || SUBSTR (p_phone_no,7);
       > END format_phone;
       > /
 
Procedure created.

Create the bind variable, execute the procedure, and verify the results.

Command> VARIABLE b_phone_no VARCHAR2 (15);
Command> EXECUTE :b_phone_no := '8006330575';
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_phone_no;
B_PHONE_NO           : 8006330575
Command> BEGIN
       >   format_phone (:b_phone_no);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_phone_no
B_PHONE_NO           : (800) 633-0575

Examples using cursors

Oracle TimesTen In-Memory Database supports cursors, as discussed in "Use of cursors in PL/SQL programs". Use a cursor to handle the result set of a SELECT statement.

Examples in this section cover the following:

See "Explicit Cursor Attributes" in Oracle Database PL/SQL Language Reference for information about the cursor attributes used in these examples.

Fetching values

This section provides examples of how to fetch values from a cursor, including how to fetch the values into a record.

Example 5-6 Fetching values from a cursor

The following example uses a cursor to select employee_id and last_name from the employees table where department_id is 30 Two variables are declared to hold the fetched values from the cursor, and the FETCH statement retrieves rows one at a time in a loop to retrieve all rows. Execution stops when there are no remaining rows in the cursor, illustrating use of the %NOTFOUND cursor attribute.

%NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows.

Command>  DECLARE
       >    CURSOR c_emp_cursor IS
       >      SELECT employee_id, last_name FROM employees
       >      WHERE department_id = 30;
       >    v_empno  employees.employee_id%TYPE;
       >    v_lname  employees.last_name%TYPE;
       >  BEGIN
       >    OPEN c_emp_cursor;
       >    LOOP
       >     FETCH c_emp_cursor INTO v_empno, v_lname;
       >    EXIT WHEN c_emp_cursor%NOTFOUND;
       >    DBMS_OUTPUT.PUT_LINE (v_empno || ' ' || v_lname);
       >    END LOOP;
       >    CLOSE c_emp_cursor;
       >  END;
       >  /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares

Example 5-7 Fetching values into a record

This is similar to Example 5-6, with the same results, but fetches the values into a PL/SQL record instead of PL/SQL variables.

Command> DECLARE
       >   CURSOR c_emp_cursor IS
       >     SELECT employee_id, last_name FROM employees
       >     WHERE department_id = 30;
       >   v_emp_record  c_emp_cursor%ROWTYPE;
       > BEGIN
       >   OPEN c_emp_cursor;
       >   LOOP
       >     FETCH c_emp_cursor INTO v_emp_record;
       >   EXIT WHEN c_emp_cursor%NOTFOUND;
       >   DBMS_OUTPUT.PUT_LINE (v_emp_record.employee_id || ' ' |
       >     v_emp_record.last_name);
       >   END LOOP;
       >   CLOSE c_emp_cursor;
       > END;
       > /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

Using the %ROWCOUNT and %NOTFOUND attributes

Example 5-8 shows how to use the %ROWCOUNT cursor attribute as well as the %NOTFOUND cursor attribute previously shown in Example 5-6 and Example 5-7.

Example 5-8 Using %ROWCOUNT and %NOTFOUND attributes

This example has the same results as Example 5-7, but illustrating the %ROWCOUNT cursor attribute as well as the %NOTFOUND attribute for exit conditions in the loop.

%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement or returned by a SELECT...INTO or FETCH...INTO statement.

Command> DECLARE
       >   CURSOR c_emp_cursor IS
       >     SELECT employee_id, last_name FROM employees
       >     WHERE department_id = 30;
       >   v_emp_record  c_emp_cursor%ROWTYPE;
       > BEGIN
       > OPEN c_emp_cursor;
       > LOOP
       >   FETCH c_emp_cursor INTO v_emp_record;
       >   EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor%NOTFOUND;
       >   DBMS_OUTPUT.PUT_LINE (v_emp_record.employee_id || ' ' ||
       >     v_emp_record.last_name);
       >   END LOOP;
       >   CLOSE c_emp_cursor;
       >  END;
       >  /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

Using cursor FOR loops

PL/SQL in TimesTen supports cursor FOR loops, as shown in the following examples.

Example 5-9 Using a cursor FOR loop

In this example, PL/SQL implicitly declares emp_record. No OPEN and CLOSE statements are necessary. The results are the same as in Example 5-8.

Command> DECLARE
       >   CURSOR c_emp_cursor IS
       >    SELECT employee_id, last_name FROM employees
       >    WHERE department_id = 30;
       > BEGIN
       >   FOR emp_record IN c_emp_cursor
       >    LOOP
       >      DBMS_OUTPUT.PUT_LINE (emp_record.employee_id || ' ' ||
       >         emp_record.last_name);
       >    END LOOP;
       > END;
       > /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

Example 5-10 Using a cursor FOR loop with subqueries

This example illustrates a FOR loop using subqueries. The results are the same as in Example 5-8 and Example 5-9.

Command> BEGIN
       >  FOR emp_record IN (SELECT employee_id, last_name FROM
       >   employees WHERE department_id = 30)
       >  LOOP
       >    DBMS_OUTPUT.PUT_LINE (emp_record.employee_id || ' ' ||
       >      emp_record.last_name);
       >   END LOOP;
       > END;
       > /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

Examples using FORALL and BULK COLLECT

Oracle TimesTen In-Memory Database supports bulk binding and the FORALL statement and BULK COLLECT feature, as noted in "FORALL and BULK COLLECT operations".

Examples in this section cover the following:

Using FORALL with SQL%BULK_ROWCOUNT

The %BULK_ROWCOUNT cursor attribute is a composite structure designed for use with the FORALL statement.

The attribute acts like an associative array (index-by table). Its ith element stores the number of rows processed by the ith execution of the INSERT statement. If the ith execution affects no rows, then %BULK_ROWCOUNT(i) returns zero.

This is demonstrated in Example 5-11.

Example 5-11 Using the FORALL statement with SQL%BULKROWCOUNT

Command> DECLARE
       >   TYPE num_list_type IS TABLE OF NUMBER
       >      INDEX BY BINARY_INTEGER;
       > v_nums num_list_type;
       > BEGIN
       >   v_nums (1) := 1;
       >   v_nums (2) := 3;
       >   v_nums (3) := 5;
       >   v_nums (4) := 7;
       >   v_nums (5) := 11;
       >     FORALL i IN v_nums.FIRST .. v_nums.LAST
       >      INSERT INTO num_table (n) VALUES (v_nums (i));
       >    FOR i IN v_nums.FIRST .. v_nums.LAST
       >    LOOP
       >       DBMS_OUTPUT.PUT_LINE ('Inserted '||
       >         SQL%BULK_ROWCOUNT (i) || ' row (s)' ||
       >         ' on iteration  ' || i );
       >    END LOOP;
       > END;
       > /
Inserted 1 row (s) on iteration  1
Inserted 1 row (s) on iteration  2
Inserted 1 row (s) on iteration  3
Inserted 1 row (s) on iteration  4
Inserted 1 row (s) on iteration  5
 
PL/SQL procedure successfully completed.

Using BULK COLLECT INTO with queries

Use BULK COLLECT with the SELECT statement in PL/SQL to retrieve rows without using a cursor.

Example 5-12 Using BULK COLLECT INTO with queries

This example selects all rows from the departments table for a specified location into a nested table, then uses a FOR LOOP to output data.

Command> CREATE OR REPLACE PROCEDURE get_departments (p_loc NUMBER) IS
       >    TYPE dept_tab_type IS
       > TABLE OF departments%ROWTYPE;
       >   v_depts dept_tab_type;
       >  BEGIN
       >    SELECT * BULK COLLECT INTO v_depts
       >   FROM departments
       >  where location_id = p_loc;
       >  FOR i IN 1 .. v_depts.COUNT
       >  LOOP
       >     DBMS_OUTPUT.PUT_LINE (v_depts(i).department_id
       >       || ' ' || v_depts (i).department_name);
       >  END LOOP;
       >  END;
       >  /
 
Procedure created.

The following executes the procedure and verifies the results:

Command> EXECUTE GET_DEPARTMENTS (1700);
10 Administration
30 Purchasing
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
 
PL/SQL procedure successfully completed.
 
Command> SELECT department_id, department_name FROM departments WHERE
         location_id = 1700;
< 10, Administration >
< 30, Purchasing >
< 90, Executive >
< 100, Finance >
< 110, Accounting >
< 120, Treasury >
< 130, Corporate Tax >
< 140, Control And Credit >
< 150, Shareholder Services >
< 160, Benefits >
< 170, Manufacturing >
< 180, Construction >
< 190, Contracting >
< 200, Operations >
< 210, IT Support >
< 220, NOC >
< 230, IT Helpdesk >
< 240, Government Sales >
< 250, Retail Sales >
< 260, Recruiting >
< 270, Payroll >
21 rows found.

Using BULK COLLECT INTO with cursors

Example 5-13 uses a cursor to bulk-collect rows from a table.

Example 5-13 Using BULK COLLECT INTO with cursors

This example uses a cursor to bulk-collect rows from the departments table with a specified location_id. value. Results are the same as in Example 5-12.

Command> CREATE OR REPLACE PROCEDURE get_departments2 (p_loc NUMBER) IS
       >   CURSOR cur_dept IS
       >    SELECT * FROM departments
       >    WHERE location_id = p_loc;
       >  TYPE dept_tab_type IS TABLE OF cur_dept%ROWTYPE;
       >  v_depts dept_tab_type;
       > BEGIN
       >   OPEN cur_dept;
       >   FETCH cur_dept BULK COLLECT INTO v_depts;
       >   CLOSE cur_dept;
       > FOR i IN 1 .. v_depts.COUNT
       >  LOOP
       >    DBMS_OUTPUT.PUT_LINE (v_depts (i).department_id
       >    || ' ' || v_depts (i).department_name );
       > END LOOP;
       > END;
       > /
 
Procedure created.
 
Command> EXECUTE GET_DEPARTMENTS2 (1700);
10 Administration
30 Purchasing
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
 
PL/SQL procedure successfully completed.

Examples using EXECUTE IMMEDIATE

TimesTen supports the EXECUTE IMMEDIATE statement, as noted in "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)". This section provides additional examples to consider as you develop your PL/SQL applications in TimesTen, including how to use EXECUTE IMMEDIATE to alter a PL/SQL connection attribute or call a TimesTen built-in procedure.

Example 5-14 Using EXECUTE IMMEDIATE to alter PLSCOPE_SETTINGS

This example uses the EXECUTE IMMEDIATE statement with ALTER SESSION to alter the PLSQL_OPTIMIZE_LEVEL setting, calling the ttConfiguration built-in procedure before and after to verify the results. Refer to "ttConfiguration" in Oracle TimesTen In-Memory Database Reference for information about this procedure.

Command> call ttconfiguration;
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
< PLSQL, 1 >
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x10000000 >
< PLSQL_MEMORY_SIZE, 32 >
< PLSQL_OPTIMIZE_LEVEL, 2 >
< PLSQL_TIMEOUT, 30 >
...
54 rows found.
 
Command> begin
       > execute immediate 'alter session set PLSQL_OPTIMIZE_LEVEL=3';
       > end;
       > /
 
PL/SQL procedure successfully completed.
 
Command> call ttconfiguration;
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
< PLSQL, 1 >
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x10000000 >
< PLSQL_MEMORY_SIZE, 32 >
< PLSQL_OPTIMIZE_LEVEL, 3 >
< PLSQL_TIMEOUT, 30 >
...
54 rows found.

Example 5-15 Using the EXECUTE IMMEDIATE statement with a single row query

In this example, the function get_emp retrieves the employee record into variable v_emprec. Execute the function and return the results in v_emprec.

Command> CREATE OR REPLACE FUNCTION get_emp (p_emp_id NUMBER)
       >   RETURN employees%ROWTYPE IS
       >   v_stmt VARCHAR2 (200);
       >   v_emprec employees%ROWTYPE;
       > BEGIN
       >   v_stmt:= 'SELECT * FROM EMPLOYEES '||
       >   'WHERE employee_id = :p_emp_id';
       >   EXECUTE IMMEDIATE v_stmt INTO v_emprec USING p_emp_id;
       >   RETURN v_emprec;
       > END;
       > /
 
Function created.
 
Command> DECLARE
       >   v_emprec employees%ROWTYPE := GET_EMP (100);
       > BEGIN
       >  DBMS_OUTPUT.PUT_LINE ('Employee: ' || v_emprec.last_name);
       > END;
       > /
Employee: King
 
PL/SQL procedure successfully completed.

Example 5-16 Using EXECUTE IMMEDIATE with TimesTen specific syntax

Use the EXECUTE IMMEDIATE statement to execute a TimesTen SELECT FIRST n statement. This syntax is specific to TimesTen.

Command> DECLARE v_empid NUMBER;
       > BEGIN
       >   EXECUTE IMMEDIATE 'SELECT FIRST 1 employee_id FROM employees'
       >    INTO v_empid;
       >  DBMS_OUTPUT.PUT_LINE ('Employee id: ' || v_empid);
       > END;
       > /
Employee id: 100
 
PL/SQL procedure successfully completed.

Example 5-17 Using EXECUTE IMMEDIATE to call ttConfiguration

Oracle TimesTen In-Memory Database supports a number of TimesTen specific built-in procedures, such as ttConfiguration. You can use the EXECUTE IMMEDIATE statement with CALL to call these built-in procedures.

For example, to call the built-in procedure ttConfiguration, create a PL/SQL record type and then SELECT INTO that record type. Because ttConfiguration returns more than one row, use BULK COLLECT.

For more information on TimesTen built-in procedures, see "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.

Command> DECLARE
       >   TYPE ttConfig_record IS RECORD
       >      (name varchar2(255), value varchar2 (255));
       >   TYPE ttConfig_table IS TABLE OF ttConfig_record;
       > v_ttConfigs ttConfig_table;
       > BEGIN
       >  EXECUTE IMMEDIATE  'CALL ttConfiguration'
       >   BULK COLLECT into v_ttConfigs;
       >  DBMS_OUTPUT.PUT_LINE ('Name: ' || v_ttConfigs(1).name
       >    || ' Value: ' || v_ttConfigs(1).value);
       > end;
       > /
Name: CacheGridEnable Value: 0
 
PL/SQL procedure successfully completed.

Examples using RETURNING INTO

This section includes the following two examples using the RETURNING INTO clause:

See "RETURNING INTO clause" for an overview.

Using the RETURNING INTO clause with a record

The following example uses ttIsql to run a SQL script that uses a RETURNING INTO clause to return data into a record. The example gives a raise to a specified employee, returns his name and new salary into a record, then outputs the data from the record. For reference, the original salary is shown before running the script.

Command> SELECT SALARY,LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
< 24000, King >
1 row found.

Command> run ReturnIntoWithRecord.sql;
 
CREATE TABLE emp_temp AS SELECT * FROM employees;
107 rows inserted.
 
DECLARE
   TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE,
                          salary employees.salary%TYPE);
   emp_info EmpRec;
   emp_id NUMBER := 100;
BEGIN
   UPDATE emp_temp SET salary = salary * 1.1
      WHERE employee_id = emp_id
      RETURNING last_name, salary INTO emp_info;
   DBMS_OUTPUT.PUT_LINE
      ('Just gave a raise to ' || emp_info.last_name ||
       ', who now makes ' || emp_info.salary);
   ROLLBACK;
END;
/
 
Just gave a raise to King, who now makes 26400
 
PL/SQL procedure successfully completed.

Using BULK COLLECT INTO with the RETURNING INTO clause

The following example uses ttIsql to run a SQL script that uses a RETURNING INTO clause with BULK COLLECT to return data into nested tables, a type of PL/SQL collection. The example deletes all the employees from a specified department, then, using one nested table for employee IDs and one for last names, outputs the employee ID and last name of each deleted employee. For reference, the IDs and last names of employees in the department are also displayed before execution of the script.

Command> select employee_id, last_name from employees where department_id=30;
< 114, Raphaely >
< 115, Khoo >
< 116, Baida >
< 117, Tobias >
< 118, Himuro >
< 119, Colmenares >
6 rows found.
Command> run ReturnIntoWithBulkCollect.sql;
 
CREATE TABLE emp_temp AS SELECT * FROM employees;
107 rows inserted.
 
DECLARE
   TYPE NumList IS TABLE OF employees.employee_id%TYPE;
   enums NumList;
   TYPE NameList IS TABLE OF employees.last_name%TYPE;
   names NameList;
BEGIN
   DELETE FROM emp_temp WHERE department_id = 30
     RETURNING employee_id, last_name
     BULK COLLECT INTO enums, names;
   DBMS_OUTPUT.PUT_LINE
      ('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN enums.FIRST .. enums.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE
         ('Employee #' || enums(i) || ': ' || names(i));
   END LOOP;
END;
/
 
Deleted 6 rows:
Employee #114: Raphaely
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares
 
PL/SQL procedure successfully completed.

Examples using the AUTHID clause

This section runs a script twice with just one change, first defining a PL/SQL procedure with AUTHID CURRENT_USER for invoker's rights, then with AUTHID DEFINER for definer's rights. See "Definer's rights and invoker's rights" for related information.

The script assumes three users have been created: a tool vendor and two tool users (brandX and brandY). Each has been granted CREATE SESSION, CREATE PROCEDURE, and CREATE TABLE privileges as necessary. The following setup is also assumed, to allow "use username;" syntax to connect to the database as username:

connect adding "uid=toolVendor;pwd=pw" as toolVendor;
connect adding "uid=brandX;pwd=pw" as brandX;
connect adding "uid=brandY;pwd=pw" as brandY;

The script does the following:

The different results between the two executions of the script show the difference between invoker's rights and definer's rights.

Here is the script for the invoker's rights execution:

use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);

create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
set serveroutput on
execute toolVendor.printInventoryStatistics;
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
set serveroutput on
execute toolVendor.printInventoryStatistics; 

The only difference for the definer's rights execution is the change in the AUTHID clause for the procedure definition:

...
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
...

Example 5-18 Using AUTHID CURRENT_USER

Following are the results when the procedure is defined with invoker's rights. Note that when the tool users brandX and brandY run the printInventoryStatistics procedure, each sees the data in his own (the invoker's) myInventory table.

Command> run invoker.sql
 
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/

Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
toothpaste 100
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
shampoo 10
 
PL/SQL procedure successfully completed.

Use the following to terminate all the connections:

Command> disconnect all;

Example 5-19 Using AUTHID DEFINER

Following are the results when the procedure is defined with definer's rights. Note that when the tool users brandX and brandY run printInventoryStatistics, each sees the data in the myInventory table belonging to the tool vendor (the definer).

Command> run definer.sql
 
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
 
Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.

In this case, it is also instructive to see that although brandX and brandY can each access the toolVendor.myInventory table through the procedure, they cannot access it directly. That is a key use of definer's rights, to allow specific and restricted access to a table or other SQL object through the actions of a procedure.

Command> use brandX;
brandx: Command> select * from toolVendor.myInventory;
15100: User BRANDX lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

brandx: Command> use brandY;
brandy: Command> select * from toolVendor.myInventory;
15100: User BRANDY lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

Use the following to terminate all the connections:

Command> disconnect all;

Example querying a system view

This section provides an example that queries a system view.

Example 5-20 Querying system view USER_SOURCE

This example queries the USER_SOURCE system view to examine the source code of procedure query_emp from Example 5-3. (You must create that procedure before completing this example.)

Command> SELECT SUBSTR (text, 1, LENGTH(text)-1)
       > FROM user_source
       > WHERE name = 'QUERY_EMP' AND type = 'PROCEDURE';

This produces the following output:

< PROCEDURE query_emp >
< (p_id IN employees.employee_id%TYPE, >
<  p_name OUT employees.last_name%TYPE, >
<  p_salary OUT employees.salary%TYPE) IS >
< BEGIN >
< SELECT last_name, salary INTO p_name, p_salary >
< FROM employees >
< WHERE employee_id = p_id; >
< END query_emp; >
9 rows found.

Note:

As with other USER_* system views, all users have SELECT privilege for the USER_SOURCE system view.

Example creating and invoking a standalone function

This section contains an example that executes a standalone function.

Example 5-21 Creating and invoking a standalone function

This example creates and invokes the function get_sal, which has one input parameter and returns salary as type NUMBER.

Command> CREATE OR REPLACE FUNCTION get_sal
       >   (p_id employees.employee_id%TYPE) RETURN NUMBER IS
       >   v_sal employees.salary%TYPE := 0;
       > BEGIN
       >   SELECT salary INTO v_sal FROM employees
       >     WHERE employee_id = p_id;
       >   RETURN v_sal;
       > END get_sal;
       > /
 
Function created.
Command> BEGIN
       >   DBMS_OUTPUT.PUT_LINE (get_sal (100));
       > END;
       > /
24000
 
PL/SQL procedure successfully completed.