PL/SQL Procedures and Functions

Creating Procedures and Functions

In TimesTen, you can create standalone subprograms (stored procedures or functions) at the database level with the CREATE PROCEDURE or CREATE FUNCTION statement.

Optionally use CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION if you want the subprogram to be replaced if it already exists.

Use ALTER PROCEDURE or ALTER FUNCTION to explicitly compile a procedure or function or modify the compilation options. (To recompile a procedure or function that is part of a package, recompile the package using the ALTER PACKAGE statement.)

In TimesTen, syntax for CREATE PROCEDURE and CREATE FUNCTION is a subset of what is supported in Oracle Database. For information on these statements and the ALTER PROCEDURE and ALTER FUNCTION statements in TimesTen, see SQL Statements in Oracle TimesTen In-Memory Database SQL Reference.

Note:

ALTER is not supported for PL/SQL objects in TimesTen Scaleout.

Executing Procedures and Functions

TimesTen supports execution of PL/SQL from client applications using ODBC, OCI, Pro*C/C++, ODP.NET, JDBC, or TimesTen TTClasses (for C++).

As noted earlier, a block is the basic unit of a PL/SQL source program. Anonymous blocks were also discussed earlier. By contrast, procedures and functions are PL/SQL blocks that have been defined with a specified name.

See PL/SQL Procedures and Functions for how to define and create them.

In TimesTen, a PL/SQL procedure or function that is standalone (created with CREATE PROCEDURE or CREATE FUNCTION) or part of a package can be executed using an anonymous block or a CALL statement. (See CALL in Oracle TimesTen In-Memory Database SQL Reference for details about CALL syntax.)

Consider the following function:

create or replace function mytest return number is
begin
  return 1;
end;
/

In TimesTen, you can execute mytest in either of the following ways.

In an anonymous block:

Command> variable n number;
Command> begin
         :n := mytest();
         end;
         /
 
PL/SQL procedure successfully completed.
 
Command> print n;
N                    : 1

In a CALL statement:

Command> variable n number;
Command> call mytest() into :n;
Command> print n;
N                    : 1

In Oracle Database, you could also execute mytest through a SQL statement, as follows. This execution mechanism is not supported in TimesTen.

In a SELECT statement:

SQL> select mytest from dual;
 
    MYTEST
----------
         1

Note:

A user's own procedure takes precedence over a TimesTen built-in procedure with the same name, but it is best to avoid such naming conflicts.

This example creates a procedure that uses OUT parameters, executes the procedure in an anonymous block, then displays the OUT values. The procedure takes an employee ID as input then outputs the salary and job ID for the employee.

Command>  CREATE OR REPLACE PROCEDURE get_employee
            (p_empid in employees.employee_id%TYPE,
             p_sal OUT employees.salary%TYPE,
             p_job OUT employees.job_id%TYPE) IS
         BEGIN
           SELECT salary,job_id
           INTO p_sal, p_job
           FROM employees
           WHERE employee_id = p_empid;
         END;
         /
 
Procedure created.
 
Command> VARIABLE v_salary NUMBER;
Command> VARIABLE v_job VARCHAR2(15);
Command> BEGIN
           GET_EMPLOYEE (120, :v_salary, :v_job);
         END;
         /
 
PL/SQL procedure successfully completed.
 
Command> PRINT
V_SALARY             : 8000
V_JOB                : ST_MAN

Command> SELECT salary, job_id FROM employees WHERE employee_id = 120;
< 8000, ST_MAN >
1 row found.

Note:

Instead of using the anonymous block shown in the preceding example, you could use a CALL statement:

Command> CALL GET_EMPLOYEE(120, :v_salary, :v_job);

The next example creates a function that returns the salary of the employee whose employee ID is specified as input, then calls the function and displays the result that was returned.

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> variable n number;
Command> call get_sal(100) into :n;
Command> print n;
N                    : 24000

Note:

Instead of using the CALL statement shown in the preceding example, you could use an anonymous block:

Command> begin
           :n := get_sal(100);
         end;
         /

Using Synonyms for Procedures and Functions

TimesTen supports private and public synonyms (aliases) for database objects, including PL/SQL procedures, functions, and packages. Synonyms are often used to mask object names and object owners or to simplify SQL statements.

Create a private synonym for procedure foo in your schema as follows:

CREATE SYNONYM synfoo FOR foo;

Create a public synonym as follows:

CREATE PUBLIC SYNONYM pubfoo FOR foo;

A private synonym exists in the schema of a specific user and shares the same namespace as database objects such as tables, views, and sequences. A private synonym cannot have the same name as a table or other object in the same schema.

A public synonym does not belong to any particular schema, is accessible to all users, and can have the same name as any private object.

To use a synonym you must have appropriate privileges to access the underlying object. For required privileges to create or drop a synonym, see Privileges for PL/SQL Statements and Operations in Oracle TimesTen In-Memory Database Security Guide.

For general information about synonyms, see Understanding Synonyms in Oracle TimesTen In-Memory Database Operations Guide. For information about the CREATE SYNONYM and DROP SYNONYM statements, see SQL Statements in Oracle TimesTen In-Memory Database SQL Reference.

In the following example, USER1 creates a procedure in the user's schema and creates a public synonym for it. Then USER2 executes the procedure through the public synonym. Assume the following:

USER1 has been granted CREATE SESSION, CREATE PROCEDURE, and CREATE PUBLIC SYNONYM privileges.
USER2 has been granted CREATE SESSION and EXECUTE ANY PROCEDURE privileges.
Both users have connected to the database.
USER2 employs the SET SERVEROUTPUT ON setting.

USER1:

Command> create or replace procedure test is
         begin
         dbms_output.put_line('Running the test');
         end;
         /
 
Procedure created.
 
Command> create public synonym pubtest for test;
 
Synonym created.

USER2:

Command> begin
         pubtest;
         end;
         /
Running the test
 
PL/SQL procedure successfully completed.

Usage Notes for Procedures and Functions in TimesTen

Be aware of these usage notes for using PL/SQL procedures and functions in TimesTen.

If you use replication: PL/SQL DDL statements, such as CREATE statements for PL/SQL functions, procedures, and packages, are not replicated. See Creating a New PL/SQL Object in an Existing Active Standby Pair and Adding a PL/SQL Object to an Existing Classic Replication Scheme in Oracle TimesTen In-Memory Database Replication Guide for steps to address this.
If you use cache: A PL/SQL procedure or function resident in Oracle Database cannot be called in TimesTen by passthrough. Procedures and functions must be defined in TimesTen to be executable in TimesTen. (Also see TimesTen PL/SQL with Cache.)
PL/SQL and database object names: TimesTen does not support non-ASCII or quoted non-uppercase names of PL/SQL objects (procedures, functions, and packages). Also, trailing spaces in the quoted names of PL/SQL objects are not supported. In addition, trailing spaces in the quoted names of objects such as tables and views that are passed to PL/SQL are silently removed.
Definer's rights or invoker's rights determines access to SQL objects used by a PL/SQL procedure or function. Refer to Definer's Rights and Invoker's Rights (AUTHID Clause) in Oracle TimesTen In-Memory Database Security Guide.
See Showing Errors in ttIsql for how to get information when you encounter errors in compiling a procedure or function.