5 Examples Using TimesTen SQL in PL/SQL

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 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.

Example 5-11 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-12 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-13 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-14 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.

Example 5-15 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-16 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;