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 SELECT...INTO to query another user's table

This example provides two users, USER1 and USER2, to show one user employing 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.

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.

Example 5-4 Using IN and OUT parameters

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.

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-5 Using bind variables to execute a procedure

This example uses bind variables to execute procedure query_emp from Example 5-4 above. 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

You can use bind variables to pass data between a user application and PL/SQL.

Example 5-6 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

Example 5-16 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-17 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-18 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-19 Using EXECUTE IMMEDIATE to call ttConfiguration

In PL/SQL, you can use the EXECUTE IMMEDIATE statement with CALL syntax to call TimesTen built-in procedures, such as ttConfiguration.

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.

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-20 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-21 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 myInventory 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 5-22 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.