This chapter provides additional examples to further explore the tight integration of TimesTen SQL 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 multiple rows.
The section provides the following examples:
This example uses a SELECT...INTO statement to calculate the sum of salaries for all employees in the department where department_id is 60.
Example 5-1 Using SELECT... INTO to return sum of salaries
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.
This example provides two users, USER1 and USER2, to show one user employing SELECT...INTO to query another user's table.
Example 5-2 Using SELECT...INTO to query another user's table
The following privileges are assumed:
grant create session to user1; grant create session to user2; grant create table to user1; grant select on user1.test to user2;
USER1:
Command> create table test(name varchar2(20), id number);
Command> insert into test values('posey', 363);
1 row inserted.
USER2:
Command> declare
> targetid number;
> begin
> select id into targetid from user1.test where name='posey';
> dbms_output.put_line('Target ID is ' || targetid);
> end;
> /
Target ID is 363
PL/SQL procedure successfully completed.
TimesTen supports the TimesTen DML statements INSERT, UPDATE, DELETE, and MERGE. This section has an example of the INSERT statement.
Example 5-3 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 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.
The following examples in this section use IN, OUT, and IN OUT parameters, including bind variables (host variables) from outside PL/SQL:
This example creates a procedure query_emp to retrieve information about an employee, passes the employee_id value 171 to the procedure, and retrieves the name and salary into two OUT parameters.
Example 5-4 Using IN and 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.
Consider a situation where you want to format a phone number. This example takes a 10-character string containing digits for a phone number and passes this unformatted string to a procedure as an IN OUT parameter. After the procedure is executed, the IN OUT parameter contains the formatted phone number value.
Example 5-5 Using IN OUT parameters
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
This example uses ttIsql to bind a NUMBER array and a VARCHAR2 array to corresponding OUT associative arrays in a PL/SQL procedure.
See "Using associative arrays from applications" for related information.
Example 5-6 Binding to an associative array from ttIsql
Assume the following SQL setup.
DROP TABLE FOO;
CREATE TABLE FOO (CNUM INTEGER,
CVC2 VARCHAR2(20));
INSERT INTO FOO VALUES ( null,
'VARCHAR 1');
INSERT INTO FOO VALUES (-102,
null);
INSERT INTO FOO VALUES ( 103,
'VARCHAR 3');
INSERT INTO FOO VALUES (-104,
'VARCHAR 4');
INSERT INTO FOO VALUES ( 105,
'VARCHAR 5');
INSERT INTO FOO VALUES ( 106,
'VARCHAR 6');
INSERT INTO FOO VALUES ( 107,
'VARCHAR 7');
INSERT INTO FOO VALUES ( 108,
'VARCHAR 8');
COMMIT;
Assume the following PL/SQL package definition. This includes the INTEGER associative array type NUMARRTYP and the VARCHAR2 associative array type VCHARRTYP, used for output associative arrays c1 and c2, respectively, in the definition of procedure P1.
CREATE OR REPLACE PACKAGE PKG1 AS
TYPE NUMARRTYP IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
TYPE VCHARRTYP IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP);
END PKG1;
/
CREATE OR REPLACE PACKAGE BODY PKG1 AS
CURSOR CUR1 IS SELECT CNUM, CVC2 FROM FOO;
PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP) IS
BEGIN
IF NOT CUR1%ISOPEN THEN
OPEN CUR1;
END IF;
FOR i IN 1..8 LOOP
FETCH CUR1 INTO c1(i), c2(i);
IF CUR1%NOTFOUND THEN
CLOSE CUR1;
EXIT;
END IF;
END LOOP;
END P1;
END PKG1;
Now ttIsql calls PKG1.P1, binds arrays to the P1 output associative arrays, and prints the contents of those associative arrays.
Command> var c1[10] number; Command> var c2[10] varchar2(20); Command> print; C1 : ARRAY [ 10 ] (Current Size 0) C2 : ARRAY [ 10 ] (Current Size 0) Command> BEGIN PKG1.P1(:c1, :c2); END; > / PL/SQL procedure successfully completed. Command> print C1 : ARRAY [ 10 ] (Current Size 8) C1[1] : <NULL> C1[2] : -102 C1[3] : 103 C1[4] : -104 C1[5] : 105 C1[6] : 106 C1[7] : 107 C1[8] : 108 C2 : ARRAY [ 10 ] (Current Size 8) C2[1] : VARCHAR 1 C2[2] : <NULL> C2[3] : VARCHAR 3 C2[4] : VARCHAR 4 C2[5] : VARCHAR 5 C2[6] : VARCHAR 6 C2[7] : VARCHAR 7 C2[8] : VARCHAR 8
TimesTen 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.
This section provides examples of how to fetch values from a cursor, including how to fetch the values into a record.
Example 5-7 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-8 Fetching values into a record
This is similar to Example 5-7 above, 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.
Example 5-9 shows how to use the %ROWCOUNT cursor attribute as well as the %NOTFOUND cursor attribute previously shown in Example 5-7 and Example 5-8 above.
Example 5-9 Using %ROWCOUNT and %NOTFOUND attributes
This example has the same results as Example 5-8, 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.
PL/SQL in TimesTen supports cursor FOR loops, as shown in the following examples.
Example 5-10 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-9 above.
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-11 Using a cursor FOR loop with subqueries
This example illustrates a FOR loop using subqueries. The results are the same as in Example 5-9 and Example 5-10 above.
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.
TimesTen 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:
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-12.
Example 5-12 Using the FORALL statement with SQL%BULK_ROWCOUNT
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.
Use BULK COLLECT with the SELECT statement in PL/SQL to retrieve rows without using a cursor.
Example 5-13 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.
Example 5-14 uses a cursor to bulk-collect rows from a table.
Example 5-14 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-13 above.
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.
SAVE EXCEPTIONS allows an UPDATE, INSERT, or DELETE statement to continue executing after it issues an exception. When the statement finishes, an error is issued to signal that at least one exception occurred. Exceptions are collected into an array that you can examine using %BULK_EXCEPTIONS after the statement has executed.
Example 5-15 Using SAVE EXCEPTIONS with BULK COLLECT
In this example, PL/SQL raises predefined exceptions because some new values are too large for the job_id column. After the FORALL statement, SQL%BULK_EXCEPTIONS.COUNT returns 2, and the contents of SQL%BULK_EXCEPTIONS are (7, 01401) and (13, 01401), indicating the error number and the line numbers where the error was detected. To get the error message, the negative of SQL%BULK_EXCEPTIONS(i).ERROR_CODE is passed to the error-reporting function SQLERRM (which expects a negative number).
The following script is executed using ttIsql:
-- create a temporary table for this example
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
emp_sr empid_tab;
-- create an exception handler for ORA-24381
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
SELECT employee_id
BULK COLLECT INTO emp_sr FROM emp_temp
WHERE hire_date < '1994-12-30';
-- add '_SR' to the job_id of the most senior employees
FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
UPDATE emp_temp SET job_id = job_id || '_SR'
WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
-- Figure out what failed and why
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE
('Number of statements that failed: ' || errors);
FOR i IN 1..errors LOOP
DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||
'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
/
DROP TABLE emp_temp;
Results are as follows:
Number of statements that failed: 2 Error #1 occurred during iteration #7 Error message is ORA-01401: inserted value too large for column Error #2 occurred during iteration #13 Error message is ORA-01401: inserted value too large for column PL/SQL procedure successfully completed.
TimesTen supports the EXECUTE IMMEDIATE statement, as noted in "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)". This section provides the following additional examples to consider as you develop your PL/SQL applications in TimesTen:
Consider a situation where you do not know your table definition at compilation. By using an EXECUTE IMMEDIATE statement, you can create your table at execution time. This example shows a procedure that creates a table using the EXECUTE IMMEDIATE statement. The procedure is executed with the table name and column definitions passed as parameters, then creation of the table is verified.
Example 5-16 Using EXECUTE IMMEDIATE to create a table
Command> CREATE OR REPLACE PROCEDURE create_table
> (p_table_name VARCHAR2, p_col_specs VARCHAR2) IS
> BEGIN
> EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name
>
> || ' (' || p_col_specs|| ' )';
> END;
> /
Procedure created.
Execute the procedure and verify the table is created.
Command> BEGIN
> create_table ('EMPLOYEES_NAMES', 'id NUMBER (4)
> PRIMARY KEY, name VARCHAR2 (40)');
> END;
> /
PL/SQL procedure successfully completed.
Command> DESCRIBE employees_names;
Table USER.EMPLOYEES_NAMES:
Columns:
*ID NUMBER (4) NOT NULL
NAME VARCHAR2 (40) INLINE
1 table found.
(primary key columns are indicated with *)
In this example, the function get_emp retrieves an employee record. The function is executed and returns the results in v_emprec.
Example 5-17 Using EXECUTE IMMEDIATE with a single row query
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.
This example uses an 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. (The next example calls ttConfiguration from inside an EXECUTE IMMEDIATE statement.) Refer to "ttConfiguration" in Oracle TimesTen In-Memory Database Reference for information about this procedure.
Example 5-18 Using EXECUTE IMMEDIATE to alter PLSCOPE_SETTINGS
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.
In PL/SQL, you can use an EXECUTE IMMEDIATE statement with CALL syntax to call a TimesTen built-in procedure.
For example, to call the built-in procedure ttConfiguration and return its output result set, create a PL/SQL record type then use EXECUTE IMMEDIATE with BULK COLLECT to fetch the result set into an array.
For more information on TimesTen built-in procedures, see "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.
Example 5-19 Using EXECUTE IMMEDIATE to call ttConfiguration
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.
This example uses an EXECUTE IMMEDIATE statement to execute a TimesTen SELECT FIRST n statement. This syntax is specific to TimesTen.
Example 5-20 Using EXECUTE IMMEDIATE with TimesTen-specific syntax
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.
This section includes the following two examples using the RETURNING INTO clause:
See "RETURNING INTO clause" for an overview.
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.
Example 5-21 Using the RETURNING INTO clause with a record
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.
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.
Example 5-22 Using BULK COLLECT INTO with the RETURNING INTO clause
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.
This section runs a script twice in ttIsql 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:
Creates the procedure, printInventoryStatistics, as the tool vendor.
Creates a table with the same name, myInventory, in each of the three user schemas, populating it with unique data in each case.
Runs the procedure as each of the tool users.
The different results between the two executions of the script show the difference between invoker's rights and definer's rights.
Following 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;
/
...
This example shows 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.
Example 5-23 Using AUTHID CURRENT_USER
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;
This example shows 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 myInventory belonging to the tool vendor (the definer).
Example 5-24 Using AUTHID 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;
This section provides an example that queries a system view.
Example 5-25 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-4. (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 otherUSER_* system views, all users have SELECT privilege for the USER_SOURCE system view.