PK
.r-Ioa, mimetypeapplication/epub+zipPK .r-I META-INF/container.xml
TimesTen has features to control database access with object-level resolution for database objects such as tables, views, materialized views, indexes, sequences, functions, procedures, and packages, for example. You can refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for introductory information about TimesTen access control features.
This chapter introduces access control as it relates to PL/SQL users.
Note: Access control is automatically enabled when you install TimesTen. You cannot disable it. |
Topics in this chapter include the following:
This section covers the following topics:
For PL/SQL users, access control affects the ability to create, alter, drop, or execute PL/SQL procedures and functions, including packages and their member procedures and functions.
You need the CREATE PROCEDURE
privilege to create a procedure, function, package definition, or package body if it is being created in your own schema, or CREATE ANY PROCEDURE
if it is being created in any schema other than your own. To alter or drop a procedure, function, package definition, or package body, you must be the owner or have the ALTER ANY PROCEDURE
privilege or DROP ANY PROCEDURE
privilege, respectively.
To execute a procedure or function, you must be the owner, have the EXECUTE
privilege for the procedure or function (or for the package to which it belongs, if applicable), or have the EXECUTE ANY PROCEDURE
privilege. This is all summarized in Table 7-1.
Table 7-1 Privileges for using PL/SQL procedures and functions
Action | SQL statement or operation | Required Privilege |
---|---|---|
Create a procedure, function, package definition, or package body. |
|
Or:
|
Alter a procedure, function, or package. |
|
Ownership of the procedure, function, or package Or:
|
Drop a procedure, function, package definition, or package body. |
|
Ownership of the procedure, function, or package Or:
|
Execute a procedure or function. |
Invoke the procedure or function. |
Ownership of the procedure or function, or of the package to which it belongs (if applicable) Or:
Or:
|
Create a private synonym for a procedure, function, or package. |
|
Or:
|
Create a public synonym for a procedure, function, or package |
|
|
Use a synonym to execute a procedure or function. |
Invoke the procedure or function through its synonym. |
Privilege to execute the underlying procedure or function |
Drop a private synonym for a procedure, function, or package. |
|
Ownership of the synonym Or:
|
Drop a public synonym for a procedure, function, or package. |
|
|
See "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for the syntax and required privileges of SQL statements discussed in this section.
Use the SQL statement GRANT
to grant a privilege. Use REVOKE
to revoke one.
The following example grants EXECUTE
privilege to user2
for a procedure and a package that user1
owns:
Command> grant execute on user1.myproc to user2; Command> grant execute on user1.mypkg to user2;
This example revokes the privileges:
Command> revoke execute on user1.myproc from user2; Command> revoke execute on user1.mypkg from user2;
Example 7-1 Granting of required privileges
This example shows a series of attempted operations by a user, user1
, as follows:
The user attempts each operation before having the necessary privilege. The resulting error is shown.
The instance administrator grants the necessary privilege.
The user successfully performs the operation.
The ttIsql
utility is used by user1
to perform (or attempt) the operations and by the instance administrator to grant privileges.
USER1
:
Initially the user does not have permission to create a procedure. That must be granted even in one's own schema.
Command> create procedure testproc is > begin > dbms_output.put_line('user1.testproc called'); > end; > / 15100: User USER1 lacks privilege CREATE PROCEDURE The command failed.
Instance administrator:
Command> grant create procedure to user1;
USER1
:
Once user1
can create a procedure in his own schema, he can execute it because he owns it.
Command> create procedure testproc is > begin > dbms_output.put_line('user1.testproc called'); > end; > / Procedure created. Command> begin > testproc(); > end; > / user1.testproc called PL/SQL procedure successfully completed.
The user cannot yet create a procedure in another schema, though.
Command> create procedure user2.testproc is > begin > dbms_output.put_line('user2.testproc called'); > end; > / 15100: User USER1 lacks privilege CREATE ANY PROCEDURE The command failed.
Instance administrator:
Command> grant create any procedure to user1;
USER1
:
Now user1
can create a procedure in another schema, but he cannot execute it yet because he does not own it or have privilege.
Command> create procedure user2.testproc is > begin > dbms_output.put_line('user2.testproc called'); > end; > / Procedure created. Command> begin > user2.testproc(); > end; > / 8503: ORA-06550: line 2, column 7: PLS-00904: insufficient privilege to access object USER2.TESTPROC 8503: ORA-06550: line 2, column 1: PL/SQL: Statement ignored The command failed.
Instance administrator:
Command> grant execute any procedure to user1;
USER1
:
Now user1
can execute a procedure in another schema.
Command> begin > user2.testproc(); > end; > / user2.testproc called PL/SQL procedure successfully completed.
When a privilege on an object is revoked from a user, all of that user's PL/SQL objects that refer to that object are temporarily invalidated. Once the privilege has been restored, a user can explicitly recompile and revalidate an object by executing ALTER PROCEDURE
, ALTER FUNCTION
, or ALTER PACKAGE
, as applicable, on the object. Alternatively, each object will be recompiled and revalidated automatically the next time it is executed.
For example, if user1
has a procedure user1.proc0
that calls user2.proc1
, proc0
becomes invalid if EXECUTE
privilege for proc1
is revoked from user1
.
Use the following to see if any of your objects are invalid:
select * from user_objects where status='INVALID';
See "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for information about the ALTER
statements.
Example 7-2 Invalidated object
This example shows a sequence that results in an invalidated object, in this case a PL/SQL procedure, as follows:
A user is granted CREATE ANY PROCEDURE
privilege, creates a procedure in another user's schema, then creates a procedure in his own schema that calls the procedure in the other user's schema.
The user is granted EXECUTE
privilege to execute the procedure in the other user's schema.
The user executes the procedure in his schema that calls the procedure in the other user's schema.
EXECUTE
privilege for the procedure in the other user's schema is revoked from the user, invalidating the user's own procedure.
EXECUTE
privilege for the procedure in the other user's schema is granted to the user again. When he executes his own procedure, it is implicitly recompiled and revalidated.
Instance administrator:
Command> grant create any procedure to user1;
USER1
:
Command> create procedure user2.proc1 is > begin > dbms_output.put_line('user2.proc1 is called'); > end; > / Procedure created. Command> create procedure user1.proc0 is > begin > dbms_output.put_line('user1.proc0 is called'); > user2.proc1; > end; > / Procedure created.
Instance administrator:
Command> grant execute on user2.proc1 to user1;
USER1
:
Command> begin > user1.proc0; > end; > / user1.proc0 is called user2.proc1 is called PL/SQL procedure successfully completed.
And to confirm user1
has no invalid objects:
Command> select * from user_objects where status='INVALID'; 0 rows found.
Instance administrator:
Now revoke the EXECUTE
privilege from user1
.
Command> revoke execute on user2.proc1 from user1;
USER1
:
Immediately, user1.proc0
becomes invalid because user1
no longer has privilege to execute user2.proc1
.
Command> select * from user_objects where status='INVALID'; < PROC0, <NULL>, 273, <NULL>, PROCEDURE, 2009-06-04 14:51:34, 2009-06-04 14:58:23, 2009-06-04:14:58:23, INVALID, N, N, N, 1, <NULL> > 1 row found.
So user1
can no longer execute the procedure.
Command> begin > user1.proc0; > end; > / 8503: ORA-06550: line 2, column 7: PLS-00905: object USER1.PROC0 is invalid 8503: ORA-06550: line 2, column 1: PL/SQL: Statement ignored The command failed.
Instance administrator:
Again grant EXECUTE
privilege on user2.proc1
to user1
.
Command> grant execute on user2.proc1 to user1;
USER1
:
The procedure user1.proc0
is still invalid until it is either explicitly or implicitly recompiled. It is implicitly recompiled when it is executed, as shown here. Or ALTER PROCEDURE
could be used to explicitly recompile it.
Command> select * from user_objects where status='INVALID'; < PROC0, <NULL>, 273, <NULL>, PROCEDURE, 2009-06-04 14:51:34, 2009-06-04 16:13:00, 2009-06-04:16:13:00, INVALID, N, N, N, 1, <NULL> > 1 row found. Command> begin > user1.proc0; > end; > / user1.proc0 is called user2.proc1 is called PL/SQL procedure successfully completed. Command> select * from user_objects where status='INVALID'; 0 rows found.
For any query or SQL DML statement executed in an anonymous block, or any SQL DDL statement executed in an EXECUTE IMMEDIATE
statement, including all such operations discussed in this document or used in any example, it is assumed that the user has appropriate privilege to execute the statement and access the desired objects. SQL executed in a PL/SQL anonymous block requires the same privilege as when executed directly. For example, to insert rows of data into a table you own, no privilege is required. If you want to insert rows of data into a table you do not own, you must be granted INSERT
privilege on that table or granted INSERT ANY TABLE
.
Refer to "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for details SQL statements and their required privileges.
When a PL/SQL procedure or function is defined, the optional AUTHID
clause of the CREATE FUNCTION
or CREATE PROCEDURE
statement specifies whether the function or procedure executes with definer's rights (AUTHID DEFINER
, the default) or invoker's rights (AUTHID CURRENT_USER
). Similarly, for procedures or functions in a package, the AUTHID
clause of the CREATE PACKAGE
statement specifies whether each member function or procedure of the package executes with definer's rights or invoker's rights. The AUTHID
clause is shown in the syntax documentation for these statements, under "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.
The AUTHID
setting affects the name resolution and privilege checking of SQL statements that a procedure or function issues at runtime. With definer's rights, SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. With invoker's rights, SQL name resolution and privilege checking simply operate as though the current user (the invoker) is running it.
Invoker's rights would be useful in a scenario where you might want to grant broad privileges for a body of code, but would want that code to affect only each user's own objects in his or her own schema.
Definer's rights would be useful in a situation where you want all users to have access to the same centralized tables or other SQL objects, but only for the specific and limited actions that are executed by the procedure. The users would not have access to the SQL objects otherwise.
See "Examples using the AUTHID clause" for examples using definer's and invoker's rights.
Refer to "Invoker's Rights and Definer's Rights (AUTHID Property)" in Oracle Database PL/SQL Language Reference for additional information.
This section covers the following:
Note the following when connecting to the database:
Privilege to connect to a database must be explicitly granted to every user, other than the instance administrator, through the CREATE SESSION
privilege. This is a system privilege so must be granted to the user either by the instance administrator or by a user with ADMIN
privilege. This can be accomplished either directly or through the PUBLIC
role. Refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for additional information and examples.
Required privileges for PL/SQL connection attributes are included in "PL/SQL connection attributes".
Note the following regarding access to system views and PL/SQL supplied packages.
SELECT
and EXECUTE
privileges on various system tables, system views, PL/SQL functions, PL/SQL procedures, and PL/SQL packages are granted by default to all users through the PUBLIC
role, of which all users are a member. This role is documented in "Privileges" in Oracle TimesTen In-Memory Database SQL Reference. Use the following command to see the list of these public database objects and the associated privileges:
SELECT table_name, privilege FROM sys.all_tab_privs WHERE grantee='PUBLIC';
All users have SELECT
privilege for the ALL_*
and USER_*
system views.
EXECUTE ANY PROCEDURE
does not apply to supplied packages; however, most are accessible through the PUBLIC
role. Access control for PL/SQL packages provided with TimesTen is noted at the beginning of Chapter 8, "TimesTen Supplied PL/SQL Packages."
The ttPLSQLMemoryStats
built-in procedure, which returns statistics about library cache performance and activity, can be called by any user. This procedure is documented under "ttPLSQLMemoryStats" in Oracle TimesTen In-Memory Database Reference. Also see Example 6-4.
This chapter explores the flexible error trapping and error handling you can use in your PL/SQL programs.
For more information on error-handling and exceptions in PL/SQL, see "PL/SQL Error Handling" in Oracle Database PL/SQL Language Reference.
See the end of this chapter for TimesTen-specific considerations.
The following topics are covered:
This section provides an overview of exceptions in PL/SQL programming, covering the following topics:
An exception is a PL/SQL error that is raised during program execution, either implicitly by TimesTen or explicitly by your program. Handle an exception by trapping it with a handler or propagating it to the calling environment.
For example, if your SELECT
statement returns multiple rows, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA
error message. (ORA
messages, originally defined for Oracle Database, are similarly implemented by TimesTen.)
Command> DECLARE > v_lname VARCHAR2 (15); > BEGIN > SELECT last_name INTO v_lname > FROM employees > WHERE first_name = 'John'; > DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname); > END; > / 8507: ORA-01422: exact fetch returns more than requested number of rows 8507: ORA-06512: at line 4 The command failed.
You can handle such exceptions in your PL/SQL block so that your program completes successfully. For example:
Command> DECLARE > v_lname VARCHAR2 (15); > BEGIN > SELECT last_name INTO v_lname > FROM employees > WHERE first_name = 'John'; > DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname); > EXCEPTION > WHEN TOO_MANY_ROWS THEN > DBMS_OUTPUT.PUT_LINE (' Your SELECT statement retrieved multiple > rows. Consider using a cursor.'); > END; > / Your SELECT statement retrieved multiple rows. Consider using a cursor. PL/SQL procedure successfully completed.
There are three types of exceptions:
Predefined exceptions are error conditions that are defined by PL/SQL.
Non-predefined exceptions include any standard TimesTen errors.
User-defined exceptions are exceptions specific to your application.
In TimesTen, these three types of exceptions are used in the same way as in Oracle Database.
Exception | Description | How to handle |
---|---|---|
Predefined TimesTen error | One of approximately 20 errors that occur most often in PL/SQL code. | You are not required to declare these exceptions. They are predefined by TimesTen. TimesTen implicitly raises the error. |
Non-predefined TimesTen error | Any other standard TimesTen error. | Must be declared in the declarative section of your application. TimesTen implicitly raises the error and you can use an exception handler to catch the error. |
User-defined error | Error defined and raised by the application. | Must be declared in the declarative section. Developer raises the exception explicitly. |
This section describes how to trap predefined TimesTen errors or user-defined errors.
Trap a predefined TimesTen error by referencing its predefined name in your exception-handling routine. PL/SQL declares predefined exceptions in the STANDARD
package.
Table 4-1 lists predefined exceptions supported by TimesTen, the associated ORA
error numbers and SQLCODE
values, and descriptions of the exceptions.
Also see "Unsupported predefined errors".
Table 4-1 Predefined exceptions
Exception name | Oracle error number | SQLCODE | Description |
---|---|---|---|
|
|
-6530 |
Program attempted to assign values to the attributes of an uninitialized object. |
|
|
-6592 |
None of the choices in the |
|
|
-6531 |
Program attempted to apply collection methods other than EXISTS to an uninitialized nested table or varray, or program attempted to assign values to the elements of an uninitialized nested table or varray. |
|
|
-6511 |
A program attempted to open an already opened cursor. |
|
|
-1 |
A program attempted to insert duplicate values in a column that is constrained by a unique index. |
|
|
-1001 |
Illegal cursor operation. |
|
|
-1722 |
Conversion of character string to number failed. |
|
|
+100 |
Single row |
|
|
-6501 |
PL/SQL has an internal problem. |
|
|
-6504 |
Host cursor variable and PL/SQL cursor variable involved in an assignment statement have incompatible return types. |
|
|
-6500 |
PL/SQL ran out of memory or memory was corrupted. |
|
|
-6533 |
A program referenced a nested table or varray using an index number larger than the number of elements in the collection. |
|
|
-6532 |
A program referenced a nested table or varray element using an index number that is outside the legal range (for example, -1). |
|
|
-1410 |
The conversion of a character string into a universal rowid failed because the character string does not represent a value |
|
|
-1422 |
Single row |
|
|
-6502 |
An arithmetic, conversion, truncation, or size constraint error occurred. |
|
|
-1476 |
A program attempted to divide a number by zero. |
Example 4-1 Using the ZERO_DIVIDE predefined exception
In this example, a PL/SQL program attempts to divide by 0. The ZERO_DIVIDE
predefined exception is used to trap the error in an exception-handling routine.
Command> DECLARE v_invalid PLS_INTEGER; > BEGIN > v_invalid := 100/0; > EXCEPTION > WHEN ZERO_DIVIDE THEN > DBMS_OUTPUT.PUT_LINE ('Attempt to divide by 0'); > END; > / Attempt to divide by 0 PL/SQL procedure successfully completed.
You can define your own exceptions in PL/SQL in TimesTen, and you can raise user-defined exceptions explicitly with either the PL/SQL RAISE
statement or the RAISE_APPLICATION_ERROR
procedure.
The RAISE
statement stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler. RAISE
statements can raise predefined exceptions, or user-defined exceptions whose names you decide.
Example 4-2 Using RAISE statement to trap user-defined exception
In this example, the department number 500 does not exist, so no rows are updated in the departments
table. The RAISE
statement is used to explicitly raise an exception and display an error message, returned by the SQLERRM
built-in function, and an error code, returned by the SQLCODE
built-in function. Use the RAISE
statement by itself within an exception handler to raise the same exception again and propagate it back to the calling environment.
Command> DECLARE > v_deptno NUMBER := 500; > v_name VARCHAR2 (20) := 'Testing'; > e_invalid_dept EXCEPTION; > BEGIN > UPDATE departments > SET department_name = v_name > WHERE department_id = v_deptno; > IF SQL%NOTFOUND THEN > RAISE e_invalid_dept; > END IF; > ROLLBACK; > EXCEPTION > WHEN e_invalid_dept THEN > DBMS_OUTPUT.PUT_LINE ('No such department'); > DBMS_OUTPUT.PUT_LINE (SQLERRM); > DBMS_OUTPUT.PUT_LINE (SQLCODE); > END; > / No such department User-Defined Exception 1 PL/SQL procedure successfully completed. The command succeeded.
Note: Given the same error condition in TimesTen and Oracle Database,SQLCODE will return the same error code, but SQLERRM will not necessarily return the same error message. This is also noted in "TimesTen error messages and SQL codes". |
Use the RAISE_APPLICATION_ERROR
procedure in the executable section or exception section (or both) of your PL/SQL program. TimesTen reports errors to your application so you can avoid returning unhandled exceptions.
Use an error number between -20,000 and -20,999. Specify a character string up to 2,048 bytes for your message.
Example 4-3 Using the RAISE_APPLICATION_ERROR procedure
This example attempts to delete from the employees
table where last_name=Patterson
. The RAISE_APPLICATION_ERROR
procedure raises the error, using error number -20201.
Command> DECLARE > v_last_name employees.last_name%TYPE := 'Patterson'; > BEGIN > DELETE FROM employees WHERE last_name = v_last_name; > IF SQL%NOTFOUND THEN > RAISE_APPLICATION_ERROR (-20201, v_last_name || ' does not exist'); > END IF; > END; > / 8507: ORA-20201: Patterson does not exist 8507: ORA-06512: at line 6 The command failed.
You can use the show errors
command in ttIsql
to see details about errors you encounter in executing anonymous blocks or compiling packages, procedures, or functions. This is shown in Example 4-4.
Example 4-4 ttIsql show errors command
Again consider Example 2-17. Assume the same package specification shown there, which declares the procedures and functions hire_employee
, remove_employee
, and num_above_salary
. But instead of the body definition shown there, consider the following, which defines hire_employee
and num_above_salary
but not remove_employee
:
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- Code for procedure hire_employee: PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) IS BEGIN INSERT INTO employees VALUES (employee_id, last_name, first_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id); END hire_employee; -- Code for function num_above_salary: FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS emp_sal NUMBER(8,2); num_count NUMBER; BEGIN SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT COUNT(*) INTO num_count FROM employees WHERE salary > emp_sal; RETURN num_count; END num_above_salary; END emp_actions; /
Attempting this body definition after the original package specification results in the following:
Warning: Package body created with compilation errors.
To get more information, run ttIsql
and use the command show errors
. In this example, show errors
provides the following:
Command> show errors; Errors for PACKAGE BODY EMP_ACTIONS: LINE/COL ERROR -------- ----------------------------------------------------------------- 13/13 PLS-00323: subprogram or cursor 'REMOVE_EMPLOYEE' is declared in a package specification and must be defined in the package body
You should be aware of some error-related behaviors that differ between TimesTen PL/SQL and Oracle PL/SQL:
TimesTen PL/SQL transaction and rollback behavior for unhandled exceptions
Possibility of runtime errors after clean compile (use of Oracle SQL parser)
TimesTen PL/SQL differs from Oracle PL/SQL in a scenario where an application executes PL/SQL in the middle of a transaction, and an unhandled exception occurs during execution of the PL/SQL. Oracle will roll back to the beginning of the anonymous block. TimesTen will not roll back.
An application should always handle any exception that results from execution of a PL/SQL block, as in the following example, run with autocommit disabled:
create table mytable (num int not null primary key); set serveroutput on insert into mytable values(1); begin insert into mytable values(2); insert into mytable values(1); exception when dup_val_on_index then dbms_output.put_line('oops:' || sqlerrm); rollback; end; / select * from mytable; commit;
The second INSERT
will fail because values must be unique, so there will be an exception and the program will perform a rollback. Running this in TimesTen results in the following.
oops:TT0907: Unique constraint (MYTABLE) violated at Rowid <BMUFVUAAABQAAAADjq> PL/SQL procedure successfully completed. select * from mytable; 0 rows found.
The result is equivalent in Oracle Database, with the SELECT
results showing no rows.
Now consider a TimesTen example where the exception is not handled, again run with autocommit disabled:
create table mytable (num int not null primary key); set serveroutput on insert into mytable values(1); begin insert into mytable values(2); insert into mytable values(1); end; / select * from mytable; commit;
In TimesTen, the SELECT
query will indicate execution of the first two inserts:
907: Unique constraint (MYTABLE) violated at Rowid <BMUFVUAAABQAAAADjq> 8507: ORA-06512: at line 3 The command failed. select * from mytable; < 1 > < 2 > 2 rows found.
If you execute this in Oracle, there will be a rollback to the beginning of the PL/SQL block, so the SELECT
results will indicate execution of only the first insert:
ORA-00001: unique constraint (SYSTEM.SYS_C004423) violated ORA-06512: at line 3 NUM ---------- 1
Notes:
|
Given the same error condition, TimesTen does not guarantee that the error message returned by TimesTen will be the same as the message returned by Oracle Database, although the SQL code will be the same. Therefore, the information returned by the SQLERRM
function may be different, but that returned by the SQLCODE
function will be the same.
For further information:
Example 4-2 uses SQLERRM
and SQLCODE
.
Refer to "Warnings and Errors" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps for information about specific TimesTen error messages.
Refer to "SQLERRM Function" and "SQLCODE Function" in Oracle Database PL/SQL Language Reference for general (and Oracle-specific) information.
Oracle Database does not have the concept of runtime warnings, so Oracle PL/SQL does not support warnings.
TimesTen In-Memory Database does have the concept of warnings, but because the TimesTen PL/SQL implementation is based on the Oracle PL/SQL implementation, TimesTen PL/SQL does not support warnings.
As a result, in TimesTen you could execute a SQL statement and see a resulting warning, but if you execute the same statement through PL/SQL you will not see the warning.
"Trapping predefined TimesTen errors" lists predefined exceptions supported by TimesTen, the associated ORA
error numbers and SQLCODE
values, and descriptions of the exceptions.
Table 4-2 notes predefined exceptions that are not supported by TimesTen.
Table 4-2 Predefined exceptions not supported by TimesTen
Exception name | Oracle error number | SQLCODE | Description |
---|---|---|---|
|
|
-1017 |
Invalid user name and password. |
|
|
-1012 |
A program issued a database call without being connected to the database. |
|
|
-30625 |
A program attempted to invoke a |
|
|
-51 |
A timeout occurred while the database was waiting for a resource. |
The TimesTen PL/SQL implementation uses the Oracle SQL parser in compiling PL/SQL programs. (This is discussed in "PL/SQL in TimesTen versus PL/SQL in Oracle Database".) As a result, if your program uses Oracle syntax or Oracle built-in procedures that are not supported by TimesTen, the issue will not be discovered during compilation. A runtime error would occur during program execution, however.
TimesTen SQL includes several constructs that are not present in Oracle SQL. The PL/SQL language does not include these constructs. To use TimesTen-specific SQL from PL/SQL, execute the SQL statements using the EXECUTE IMMEDIATE
statement. This will avoid compilation errors.
For lists of TimesTen-specific SQL and expressions, see "Compatibility Between TimesTen and Oracle" in Oracle In-Memory Database Cache User's Guide.
For more information about EXECUTE IMMEDIATE
, refer to "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)".
The purpose of this chapter is to summarize PL/SQL language elements and features and compare their support in TimesTen to their support in Oracle. In the Oracle Database documentation, many of these features are covered in "PL/SQL Language Elements" in Oracle Database PL/SQL Language Reference.
Table 9-1 PL/SQL Language Element and Feature Support in TimesTen
Feature Name | Description | Supported? | Example/Comment |
---|---|---|---|
|
Recompiles a PL/SQL procedure, function, or package. |
Y |
Syntax and semantics are the same as in Oracle Database. For information about these statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference. |
|
Changes session parameters dynamically. |
Y |
In TimesTen you can use For more information on this statement in TimesTen, see "ALTER SESSION" in Oracle TimesTen In-Memory Database SQL Reference. |
Assignment statement |
Sets current value of a variable, parameter, or element. |
Y | |
|
Marks a routine as autonomous. |
N |
TimesTen does not support autonomous transactions. |
Block declaration |
Basic unit of a PL/SQL source program. |
Y |
See "PL/SQL blocks". |
|
Can be used to select multiple rows. |
Y |
This clause can be used with the |
|
Executes a routine from within SQL. |
Y |
In TimesTen, use the |
|
Evaluates an expression, compares it against several values, and takes action according to the comparison that is |
Y | |
|
Closes cursor or cursor variable. |
Y |
See Example 2-13, "Using a cursor to retrieve information about an employee" (among others). |
Collection definition |
Specifies a collection, which is an ordered group of elements, all of the same type. |
Y |
Examples include: associative arrays (index-by tables), nested tables, and varrays. While TimesTen supports these types, it does not support passing them between PL/SQL and applications written in other languages. See "Using collections". |
Collection methods |
Built-in subprograms that operate on collections and are called using "dot" notation. |
Y |
See "Collection Methods" in Oracle Database PL/SQL Language Reference. Examples include |
Comments |
Text included within your code for explanatory purposes. |
Y |
Single-line and multi-line comments are supported. |
|
Ends the current transaction and makes permanent all changes performed in the transaction. |
Y |
See "COMMIT" in Oracle TimesTen In-Memory Database SQL Reference. Important: |
Connection attributes |
Equivalent to initialization parameters in Oracle Database. |
Y |
See "PL/SQL connection attributes". Also see "PL/SQL first connection attributes" and "PL/SQL general connection attributes" in Oracle TimesTen In-Memory Database Reference. |
Constant and variable declarations |
Specify constants and variables to be used in PL/SQL code, in the declarative part of any PL/SQL block, subprogram, or package. |
Y | |
|
Exits the current iteration of a loop and transfers control to the next iteration. |
Y |
See "CONTINUE statement". |
|
Creates a PL/SQL function. |
Y |
See "PL/SQL procedures and functions". Also see "CREATE FUNCTION" in Oracle TimesTen In-Memory Database SQL Reference. You are not required to run |
|
Creates a schema object associated with an operating system shared library. |
N |
|
|
These statements are used together to create a PL/SQL package definition and package body. |
Y |
Syntax and semantics are the same as in Oracle Database. See "PL/SQL packages". Also see "CREATE PACKAGE" and "CREATE PACKAGE BODY" in Oracle TimesTen In-Memory Database SQL Reference. You are not required to run |
|
Creates a PL/SQL procedure. |
Y |
See "PL/SQL procedures and functions". Also see "CREATE PROCEDURE" in Oracle TimesTen In-Memory Database SQL Reference. You are not required to run |
|
Creates a user-defined object type or collection type. |
N |
TimesTen does not support |
Cursor attributes |
Appended to the cursor or cursor variable to return useful information about the execution of a data manipulation statement. |
Y |
Explicit cursors and cursor variables have four attributes: The implicit cursor (SQL) has additional attributes: See "Using the %ROWCOUNT and %NOTFOUND attributes" and "Using FORALL with SQL%BULK_ROWCOUNT". Also see "Named Cursor Attribute" in Oracle Database PL/SQL Language Reference. |
Cursor declaration |
Declares a cursor. To execute a multi-row query, TimesTen opens an unnamed work area that stores processing information. A cursor lets you name the work area, access the information, and process the rows individually. |
Y | |
Cursor variables (REF CURSORs) |
Act as handles to cursors over SQL result sets. |
Y |
TimesTen supports See "PL/SQL REF CURSORs". |
Database links (dblinks) |
A pointer that defines a one-way communication path from an Oracle Database server to another database server. |
N |
TimesTen does not support database links. |
|
Deletes rows from a table. |
Y |
See "DELETE" in Oracle TimesTen In-Memory Database SQL Reference. |
|
Removes a PL/SQL procedure, function, or package, as specified. |
Y |
Syntax and semantics are the same as in Oracle Database. You can refer to information about these statements in "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference. |
Error reporting |
Y |
TimesTen applications report errors using Oracle Database error codes instead of TimesTen error codes. The error messages that accompany the error codes are either TimesTen error messages or Oracle Database error messages. | |
|
Associates a user-defined exception with a TimesTen error number. |
Y |
See "EXCEPTION_INIT Pragma" in Oracle Database PL/SQL Language Reference. |
Exception definition |
Specifies an exception, which is a runtime error or warning condition. Can be predefined or user-defined. |
Y |
Predefined conditions are raised implicitly. User-defined exceptions are raised explicitly by the |
|
Builds and executes a dynamic SQL statement. |
Y |
TimesTen supports this to execute SQL DML and DDL statements, but not to execute PL/SQL. See "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)". In TimesTen, the |
Executing PL/SQL from client applications |
Y |
Oracle TimesTen In-Memory Database supports ODBC, OCI, Pro*C/C++, TTClasses (a set of TimesTen C++ classes), and JDBC. | |
Executing PL/SQL from SQL |
N |
In TimesTen, you cannot execute PL/SQL from either a static or dynamic SQL statement. | |
|
Exits a loop and transfers control to the end of the loop. |
Y |
See Example 6-3, "Use ALTER SESSION to change attribute settings" (among others). |
Expression definition |
Specifies an expression, which is a combination of operands (variables, constants, literals, operators, and so on) and operators. The simplest expression is a single variable. |
Y | |
|
Retrieves rows of data from the result set of a multi-row query. |
Y |
See Example 2-13, "Using a cursor to retrieve information about an employee" (among others). |
|
Bulk-binds input collections before sending them to the SQL engine. |
Y | |
Function declaration and definition |
Specifies a subprogram or stored program that can be declared and defined in a PL/SQL block or package and returns a single value. |
Y |
In TimesTen, a stored function or procedure can be executed in an anonymous block or through a Use the Also refer to the table entry below for "Procedure declaration and definition". |
|
Branches unconditionally to a statement label or block label. |
Y |
See "GOTO Statement" in Oracle Database PL/SQL Language Reference. |
|
Executes or skips a sequence of statements depending on the value of the associated boolean expression. |
Y | |
Initialization parameters |
Initial parameter settings for an Oracle Database. |
TimesTen connection attributes are equivalent. See that entry above. | |
|
Specifies whether a subprogram call is to be inline. |
Y |
See "INLINE Pragma" in Oracle Database PL/SQL Language Reference. |
|
Inserts one or more rows of data into a table. |
Y |
See "Example using the INSERT statement". Also see "INSERT" in Oracle TimesTen In-Memory Database SQL Reference. |
Literal declaration |
Specifies a numeric, character string, or boolean value. |
Y |
Examples: Numeric literal: 135 String literal: 'TimesTen' |
|
Locks database tables in a specified lock mode. |
N |
TimesTen does not support the |
|
Executes a sequence of statements multiple times. Can be used, for example, in implementing a |
Y |
See Example 2-8, "Using a WHILE loop". Also see "Basic LOOP Statement" in Oracle Database PL/SQL Language Reference. |
|
Allows you to select rows from one or more sources for update or insertion into a target table. |
Y |
TimesTen SQL statement. See "MERGE" in Oracle TimesTen In-Memory Database SQL Reference. |
Native dynamic SQL execution |
Processes most dynamic SQL statements through the |
Y |
See the |
Use of non-ASCII character sets in names of tables, columns, procedures, functions, and other database objects. |
N |
In TimesTen (unlike in Oracle Database), this is not supported. | |
Use of quoted non-uppercase names of tables, columns, procedures, functions, and other database objects. |
N |
In TimesTen (unlike in Oracle Database), this is not supported (such as create or replace procedure "MixedCase" as begin ... end; / | |
|
A no-operation statement. Passes control to the next statement without performing any action. |
Y |
See "NULL Statement" in Oracle Database PL/SQL Language Reference. Also, one is used in Example 3-3, "Declaring a record type". |
Object type declaration |
Specifies a custom object type, which is created in SQL and stored in the database. |
N |
Object types are not supported at the database level. For example, |
|
Executes the query associated with a cursor. Allocates database resources to process the query, and identifies the result set. |
Y |
See Example 2-13, "Using a cursor to retrieve information about an employee". |
|
Executes the |
Y |
See Example 3-4, "Fetch rows from result set of a dynamic multirow query". |
Package declaration |
Specifies a package, which is a database object that groups logically related PL/SQL types, items, and subprograms. |
Y |
TimesTen SQL statements See "PL/SQL packages". Also see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for information about the |
Procedure declaration and definition |
Specifies a subprogram or stored program that can be declared and defined in a PL/SQL block or package and performs a specific action. |
Y |
In TimesTen, a stored procedure or function can be executed in an anonymous block or through a Use the Also refer to the table entry above for "Function declaration and definition". |
|
Stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler. |
Y | |
Record definition |
Defines a record, which is a composite variable that stores data values of different types (similar to a database row). |
Y |
See "Using records". |
|
Asserts that a subprogram (usually a function) in a package specification or object type specification does not read or write database tables or package variables. |
N |
TimesTen ignores this. |
Result cache |
This is a mechanism for caching the results of PL/SQL functions in a shared global area (SGA) that is available to every session that runs your application. |
N |
Oracle TimesTen In-Memory Database does not support the PL/SQL function result cache. |
|
Immediately completes the execution of a subprogram and returns control to the invoker. Execution resumes with the statement following the subprogram call. |
Y |
See "RETURN Statement" in Oracle Database PL/SQL Language Reference. |
|
Specifies the variables in which to store the values returned by the statement to which the clause belongs. |
Y |
See "RETURNING INTO clause" and "Examples using RETURNING INTO". |
|
Undoes database changes made during the current transaction. |
Y |
See "ROLLBACK" in Oracle TimesTen In-Memory Database SQL Reference. Important: |
|
Provides a record type that represents a row in a database table. |
Y |
See Example 2-2, "Assigning values to variables with the assignment operator". |
|
Names and marks the current point in the processing of a transaction. |
N |
TimesTen does not support savepoints. |
|
Retrieves values from one row of a table ( |
Y |
See Example 2-3, "Using SELECT INTO to assign values to variables". Also see "Query Result Set Processing" in Oracle Database PL/SQL Language Reference. |
|
Indicates that package state is required only for the duration of one call to the server. |
N |
TimesTen does not support the |
|
Begins a read-only or read and write transaction. |
N |
TimesTen does not support the |
|
Returns a character string containing the phonetic representation of a |
N |
TimesTen does not support this function. |
SQL cursor |
Either explicit or implicit, is used to handle the result set of a |
Y | |
|
Returns number code of the most recent exception. |
Y |
Given the same error condition, error codes returned by the built-in function This is also noted in "TimesTen error messages and SQL codes". |
|
Returns the error message associated with the error-number argument. |
Y |
Given the same error condition, error messages returned by the built-in function This is also noted in "TimesTen error messages and SQL codes". |
Supplied packages |
PL/SQL packages supplied with the database. |
Y |
TimesTen provides a subset of the Oracle Database PL/SQL supplied packages. |
System tables and views |
Tables and views provided with the database for administrative purposes. |
Y |
TimesTen supports a subset of the Oracle Database system tables and views. See "System Tables" in Oracle TimesTen In-Memory Database System Tables and Limits Reference. |
Triggers |
Procedures that are stored in the database and activated when specific conditions occur, such as adding a row to a table. |
N |
TimesTen does not support triggers. |
Returns statistics about library cache performance and activity. |
Y |
See "ttPLSQLMemoryStats" in Oracle TimesTen In-Memory Database Reference. In Oracle Database, use the | |
|
Lets you use the data type of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the data type. Particularly useful when declaring variables, fields, and parameters that refer to database columns. |
Y | |
|
Updates the values of one or more columns in all rows of a table or in rows that satisfy a search condition. |
Y |
See "UPDATE" in Oracle TimesTen In-Memory Database SQL Reference. |
|
In Oracle Database, use this system view to return statistics about library cache performance and activity. |
In TimesTen, use the |
Oracle TimesTen In-Memory Database supports PL/SQL (Procedural Language Extension to SQL), a programming language that enables you to integrate procedural constructs with SQL in your database. PL/SQL is an integral part of Oracle Database. As such, many of the PL/SQL features present in Oracle 11g (11.1.0.7) are also present in TimesTen. In addition, PL/SQL operates in essentially the same way in TimesTen as in Oracle.
This chapter provides a brief introduction to TimesTen PL/SQL, covering the following topics:
PL/SQL support in TimesTen enables you to do the following:
Take full advantage of the PL/SQL programming language.
Execute PL/SQL from your client applications that use these APIs:
ODBC
JDBC
OCI
Pro*C/C++
TTClasses
Execute TimesTen SQL from PL/SQL.
Create, alter, or drop standalone procedures, functions, packages and package bodies.
Use PL/SQL packages to extend your database functionality and to provide PL/SQL access to SQL features.
Handle exceptions and errors in your PL/SQL applications.
Set connection attributes in your database to customize your PL/SQL environment.
Alter session parameters so you can manage your PL/SQL environment.
Display PL/SQL metadata in your database by using PL/SQL system views.
This section provides an overview of PL/SQL operations in TimesTen, including discussion of how an application interacts with PL/SQL and how PL/SQL components interact with other components of TimesTen. The following topics are covered:
Figure 1-1 shows the PL/SQL components and their interactions with each other and with other TimesTen components during PL/SQL operations.
An application uses the API of its choice—ODBC, JDBC, OCI, Pro*C, or TTClasses—to send requests to the database. ODBC is the TimesTen native API, so each of the other APIs ultimately calls the ODBC layer.
The ODBC driver calls the TimesTen SQL parser to examine each incoming request and determine whether it is SQL or PL/SQL. The request is then passed to the appropriate subsystem within TimesTen. PL/SQL source and SQL statements are compiled, optimized and executed by the PL/SQL subsystem and SQL subsystem, respectively.
The PL/SQL compiler is responsible for generating executable code from PL/SQL source, while the SQL compiler does the same for SQL statements. Each compiler generates intermediate code that can then be executed by the appropriate PL/SQL or SQL execution engine. This executable code, along with metadata about the PL/SQL blocks, is then stored in tables in the database.
When PL/SQL blocks are executed, the PL/SQL execution engine is invoked. As PL/SQL blocks in turn invoke SQL, the PL/SQL execution engine will call the TimesTen SQL compiler and the TimesTen SQL execution engine to handle SQL execution.
Note: The introduction of PL/SQL into TimesTen has little impact on applications that do not use it. If applications execute SQL directly, then requests are passed from the TimesTen ODBC driver to the TimesTen SQL compiler and execution engine in the same way they were in previous releases. |
PL/SQL processing in TimesTen is largely identical to its processing in Oracle Database. The PL/SQL compiler and execution engine that are included with TimesTen originated in Oracle Database, and the relationship between PL/SQL components and the SQL compiler and execution engine is comparable. The tables used to store PL/SQL units are the same in TimesTen and Oracle, as are the views that are available to query information about stored PL/SQL units.
Beyond these basic similarities, however, are some potentially significant differences. These are detailed in the following subsections:
In TimesTen, as in Oracle Database, PL/SQL blocks may include SQL statements. Consider the anonymous block in the following example:
Command> create table tab2 (x number, last_name VARCHAR2 (25) INLINE NOT NULL); Command> declare > x number; > begin > select salary into x from employees where last_name = 'Whalen'; > insert into tab2 values(x, 'Whalen'); > end; > / PL/SQL procedure successfully completed.
The PL/SQL compiler in TimesTen calls a copy of the Oracle SQL parser to analyze and validate the syntax of such SQL statements. This Oracle parser is included in TimesTen for this purpose. As part of this processing, PL/SQL may rewrite parts of the SQL statements (for example, by removing INTO
clauses or replacing PL/SQL variables with binds). This processing is identical in TimesTen and in Oracle Database. The rewritten SQL statements are then included in the executable code for the PL/SQL block. When the PL/SQL block is executed, these SQL statements are compiled and executed by the TimesTen SQL subsystem.
In Oracle Database, the same SQL parser is used by the PL/SQL compiler and the SQL compiler. In TimesTen, however, different SQL parsers are used. TimesTen PL/SQL uses the Oracle SQL parser, while TimesTen SQL uses the native TimesTen SQL parser. This difference is typically, but not always, transparent to the end user. In particular, be aware of the following:
SQL statements in TimesTen PL/SQL programs must obey Oracle SQL syntax. While TimesTen SQL is generally a subset of Oracle SQL, there are some expressions that are permissible in TimesTen SQL but not in Oracle SQL. Such TimesTen-specific SQL operations cannot be used within PL/SQL except by using dynamic SQL through EXECUTE IMMEDIATE
statements or the DBMS_SQL
package. See "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)".
SQL statements that would be permissible in Oracle Database will be accepted by the PL/SQL compiler as valid even if they cannot be executed by TimesTen. If SQL features are used that TimesTen does not support, compilation of a PL/SQL block may be successful, but a runtime error would occur when the PL/SQL block is executed.
In Oracle Database, PL/SQL blocks can invoke SQL statements, and SQL statements can in turn invoke PL/SQL functions. For example, a stored procedure can invoke an UPDATE
statement that employs a user-written PL/SQL function in its WHERE
clause.
In TimesTen, a SQL statement cannot invoke a PL/SQL function.
In addition, TimesTen does not support triggers.
There are two primary developer audiences for this document:
Developers experienced with Oracle Database and Oracle PL/SQL who want to learn how to use PL/SQL in TimesTen. In particular, they want to learn the differences between PL/SQL in Oracle and PL/SQL in TimesTen.
Developers experienced with TimesTen who are not familiar with PL/SQL. These readers need general information about PL/SQL.
The following subsections note areas of particular interest in this document for each audience.
Developers experienced with Oracle PL/SQL can bypass much of this document, which covers many general concepts of PL/SQL. Likely areas of interest, particularly differences in PL/SQL functionality between Oracle and TimesTen, include the following. Note that TimesTen-specific considerations are discussed at the end of Chapter 2, Chapter 3, and Chapter 4 and throughout Chapter 9.
"How to execute PL/SQL procedures and functions". This includes a comparison between how you can execute them in TimesTen and in Oracle.
"Differences in TimesTen: transaction behavior". This discusses cursor behavior when a transaction ends in TimesTen.
"Differences in TimesTen: data type considerations". This includes TimesTen-specific conversions, and types that TimesTen does not support.
"Differences in TimesTen: exception handing and error behavior".
Chapter 6, "PL/SQL Installation and Environment." This includes discussion of TimesTen connection attributes.
Chapter 8, "TimesTen Supplied PL/SQL Packages." This documents the subset of Oracle PL/SQL packages that TimesTen supports.
Chapter 9, "TimesTen PL/SQL Support: Reference Summary." This reference chapter provides a detailed treatment of differences between TimesTen PL/SQL and Oracle PL/SQL.
Most of this document is geared toward readers without prior PL/SQL experience, especially prior TimesTen users who are not familiar with PL/SQL, and nearly the entire document should be useful. In particular, Chapter 2, "Programming Features in PL/SQL in TimesTen," will help these readers get started and Chapter 5, "Examples Using TimesTen SQL in PL/SQL," includes some additional examples.
Chapter 9, "TimesTen PL/SQL Support: Reference Summary," is geared toward differences between TimesTen PL/SQL and Oracle PL/SQL and may be of less interest.
After you have configured your environment, you can confirm that everything is set up correctly by compiling and running the TimesTen Quick Start demo applications. Refer to the Quick Start welcome page at install_dir
/quickstart.html
, especially the links under SAMPLE PROGRAMS, for information about the following:
Demo schema and setup
The build_sampledb
script creates a sample database and demo schema. You must run this before you start using the demos.
Demo environment and setup
The ttquickstartenv
script, a superset of the ttenv
script generally used for TimesTen setup, sets up the demo environment. You must run this each time you enter a session where you want to compile and run any of the demos.
Demos and setup
TimesTen provides demos for PL/SQL in a subdirectory under the quickstart/sample_code
directory. For instructions on running the demos, see the README file in the subdirectory.
What the demos do
A synopsis of each demo is provided when you click PL/SQL under SAMPLE PROGRAMS.
The chapter shows you how to manage PL/SQL in your TimesTen database, set connection attributes, and display system-provided packages. It also describes the ttSrcScan
utility, which you can use to check for PL/SQL features unsupported in TimesTen. The chapter concludes with examples to assist you in your setup procedures.
Topics in this chapter include:
This section covers the following topics:
Oracle TimesTen In-Memory Database installs PL/SQL by default. If you chose not to install PL/SQL during installation, you can use the TimesTen ttmodinstall
utility to install it later. For more information, see "ttmodinstall" in Oracle TimesTen In-Memory Database Reference.
Note: Only the instance administrator can run this utility. |
PL/SQL is enabled in TimesTen through the first connection attribute PLSQL
. You can set this attribute when you initially create your database or at any first connection afterward. Note that once PL/SQL is enabled (PLSQL=1
), it cannot be disabled (PLSQL=0
would have no effect).
If PL/SQL is supported on your platform and enabled at installation time, TimesTen sets PLSQL=1
by default. You can also set the PLSQL
connection attribute in the odbc.ini
file or in your application.
For more information on the PLSQL
connection attribute, see "PLSQL" in Oracle TimesTen In-Memory Database Reference.
There are several ways to check the status of PL/SQL in your database:
Use the ttVersion
utility to confirm that PL/SQL is enabled in your installation, as indicated in the following example:
$ ttVersion TimesTen Release 11.2.1.0.0 (32 bit Linux/x86) (user:4738) 2008-07-04T22:01:57Z Instance admin: user Instance home directory: /scratch/user... Daemon home directory: /scratch/user... PL/SQL enabled.
Use the ttStatus
utility to determine if PL/SQL is enabled in your database. In the following example, PL/SQL is enabled in database plsql1
and is not enabled in database plsql0
.
$ ttstatus TimesTen status report as of Wed Jul 16 14:35:31 2008 Daemon pid 00000 port 0000 instance user TimesTen server pid 00000 started on port 0000 ------------------------------------------------------------------------ Data store /scratch/user/plsql1 There are no connections to the data store Replication policy : Manual Cache Agent policy : Manual PL/SQL enabled. ------------------------------------------------------------------------ Data store /scratch/user/plsql0 There are no connections to the data store Replication policy : Manual Cache Agent policy : Manual ------------------------------------------------------------------------ ...
Using the ttIsql
utility, call the ttConfiguration
built-in procedure to determine the PLSQL
connection attribute setting for your database. Refer to "ttConfiguration" in Oracle TimesTen In-Memory Database Reference for information about this procedure.
For example:
Command> call ttConfiguration; < CacheGridEnable, 0 > < CacheGridMsgWait, 60 > ... < PLSQL, 1 > ...
There are several TimesTen connection attributes specific to PL/SQL, as summarized in Table 6-1 that follows. For additional information on these connection attributes, see "PL/SQL first connection attributes" and "PL/SQL general connection attributes" in Oracle TimesTen In-Memory Database Reference.
The table also notes any required access control privileges and whether each connection attribute is a first connection attribute or general connection attribute. First connection attributes are set when the database is first loaded, and persist for all connections. Only the instance administrator can load a database with changes to first connection attribute settings. A general connection attribute setting applies to one connection only, and requires no special privilege.
Table 6-1 PL/SQL Connection Attributes
Attribute | Summary |
---|---|
First connection attribute. Required privilege: Instance administrator. Enables PL/SQL in the database. If If You can enable PL/SQL when your database is initially created or at any first connection. Once PL/SQL is enabled, it cannot be disabled. Default: 1 (for platforms where PL/SQL is supported). | |
First connection attribute. Required privilege: Instance administrator. Specifies the virtual address, as a hexadecimal value, at which the PL/SQL shared memory segment is loaded into each process that uses the TimesTen direct drivers. This memory address must be identical in all connections to a given database and in all processes that connect to that database. If a single application simultaneously connects to multiple databases in direct mode, then you must set different values for each of the databases. Default: Platform-specific value. Refer to "PLSQL_MEMORY_ADDRESS" in Oracle TimesTen In-Memory Database Reference for platform-specific information. | |
First connection attribute. Required privilege: Instance administrator. Determines the size, in megabytes, of memory allocated for the PL/SQL shared memory segment, which is shared by all connections. This is memory used to hold recently executed PL/SQL code and metadata about PL/SQL objects, as opposed to storing runtime data such as database output. Default: Platform-specific value. Refer to "PLSQL_MEMORY_SIZE" in Oracle TimesTen In-Memory Database Reference for platform-specific values and tuning information. | |
General connection attribute. Required privilege: None. Controls whether the PL/SQL compiler generates cross-reference information. Possible values are You can use the Default: | |
General connection attribute. Required privilege: None. Use this to set inquiry directives to control conditional compilation of PL/SQL units, which enables you to customize the functionality of a PL/SQL program depending on conditions that are checked. This is especially useful when applications may be deployed to multiple database environments. Possible uses include activating debugging or tracing features, or basing functionality on the version of the database. The following is an example: PLSQL_CCFLAGS='DEBUG:TRUE, PRODUCTION:YES' PL/SQL conditional compilation flags are similar in concept to flags on a C compiler command line, such as the following: % cc -DEBUG=TRUE -DPRODUCTION=YES ... You can use the See "Conditional Compilation" in Oracle Database PL/SQL Language Reference for information about this feature. There is also an example of conditional compilation (though not involving Default: | |
General connection attribute. Required privilege: None. Specifies the maximum amount of PL/SQL shared memory (process heap memory) that PL/SQL can allocate for the current connection. (Note that this memory is not actually allocated until needed.) This is memory used for runtime data, such as large PL/SQL collections, as opposed to cached executable code. This limit setting protects other parts of your application, such as C or Java components, when PL/SQL might otherwise take all available runtime memory. The amount of space consumed by PL/SQL variables is roughly what you might expect comparable variables to consume in other programming languages. As an example, consider a large array of strings: type chararr is table of varchar2(32767) index by binary_integer; big_array chararr; If 100,000 strings of 100 bytes each are placed into such an array, approximately 12 megabytes of memory is consumed. Memory consumed by variables in PL/SQL blocks is used while the block executes, then is released. Memory consumed by variables in PL/SQL package specifications or bodies (not within a procedure or function) is used for the lifetime of the package. Memory consumed by variables in a PL/SQL procedure or function, including one defined within a package, is used for the lifetime of the procedure or function. However, in all cases, memory freed by PL/SQL is not returned to the operating system. Instead, it is kept by PL/SQL and reused by future PL/SQL invocations. The memory is freed when the application disconnects from TimesTen. The You can use the Default: 100 megabytes Note: In | |
General connection attribute. Required privilege: None. Specifies the optimization level used to compile PL/SQL library units. The higher the setting, the more effort the compiler makes to optimize PL/SQL library units. Possible values are 0, 1, 2, or 3. You can use the Default: 2 | |
General connection attribute Required privilege: None Controls how long PL/SQL program units are allowed to run, in seconds, before being terminated. A new value impacts PL/SQL programs currently running. Possible values are 0 (meaning no time limit) or any positive integer. You can use the Default: 30 seconds Note: The frequency with which PL/SQL programs check execution time against this timeout value is variable. It is possible for programs to run significantly longer than the timeout value before being terminated. |
Notes: There are additional TimesTen connection attributes you should consider for PL/SQL. For more information about them, refer to the indicated sections in Oracle TimesTen In-Memory Database Reference.
|
The rest of this section provides some examples for setting and altering PL/SQL connection attributes.
Example 6-1 Create a database with PL/SQL default connection attributes
This example creates a database without specifying PL/SQL connection attributes. (Be aware that only an instance administrator can create a database.)
Sample odbc.ini
:
[pldef]
Driver=path/libtten.so
DataStore=/scratch/user/pldef
DatabaseCharacterSet=US7ASCII
Connect to database pldef
:
$ ttIsql pldef
Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=pldef";
Connection successful: DSN=pldef;UID=user;DataStore=/scratch/user/pldef;Database
CharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=path/libtten.so;
TypeMode=0;
(Default setting AutoCommit=1)
Call the ttConfiguration
built-in procedure to display settings, which shows you the default PL/SQL settings:
Command> call ttConfiguration; ... < ConnectionCharacterSet, US7ASCII > < ConnectionName, pldef > ... < DataBaseCharacterSet, US7ASCII > < DataStore, /scratch/user/pldef > ... < 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 > ...
Example 6-2 Enable PL/SQL at first connection
This example establishes a first connection to a database that does not yet have PL/SQL enabled, specifying PLSQL=1
. Because the connection is a first connection, TimesTen enables PL/SQL in the database. The sample odbc.ini
file also provides settings for PLSQL_MEMORY_SIZE
and PLSQL_MEMORY_ADDRESS
.
Sample odbc.ini
:
[plsql0]
Driver=path/libtten.so
DataStore=/scratch/user/plsql0
DatabaseCharacterSet=US7ASCII
PLSQL=0
PLSQL_MEMORY_SIZE=40
PLSQL_MEMORY_ADDRESS=20000000
Connect to the plsql0
database with PLSQL=1
:
$ ttisql
Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
Command> connect "DSN=plsql0;PLSQL=1";
Connection successful: DSN=plsql0;UID=user;DataStore=/scratch/user/plsql0;
DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=path
/
libtten.so;TypeMode=0;PLSQL_MEMORY_SIZE=40;PLSQL_MEMORY_ADDRESS=20000000;
(Default setting AutoCommit=1)
Call ttConfiguration
to verify PLSQL=1
and PL/SQL settings from odbc.ini
.
Command> call ttConfiguration; ... < ConnectionCharacterSet, US7ASCII > < ConnectionName, plsql0 > ... < DataBaseCharacterSet, US7ASCII > < DataStore, /scratch/user/plsql0 > ... < PLSCOPE_SETTINGS, IDENTIFIERS:NONE > < PLSQL, 1 > < PLSQL_CCFLAGS, <NULL> > < PLSQL_CODE_TYPE, INTERPRETED > < PLSQL_CONN_MEM_LIMIT, 100 > < PLSQL_MEMORY_ADDRESS, 0x20000000 > < PLSQL_MEMORY_SIZE, 40 > < PLSQL_OPTIMIZE_LEVEL, 2 > < PLSQL_TIMEOUT, 30 > ...
Example 6-3 Use ALTER SESSION to change attribute settings
This example uses ALTER SESSION
statements to alter PL/SQL connection attributes, changing the settings of PLSCOPE_SETTINGS
, PLSQL_OPTIMIZE_LEVEL
, and PLSQL_CONN_MEM_LIMIT
. It then calls the ttConfiguration
built-in procedure to display the new values.
Command> ALTER SESSION SET PLSCOPE_SETTINGS = "IDENTIFIERS:ALL"; Session altered. Command> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3; Session altered. Command> ALTER SESSION SET PLSQL_CONN_MEM_LIMIT=200; Session altered. Command> call ttconfiguration; ... < PLSCOPE_SETTINGS, IDENTIFIERS:ALL > ... < PLSQL_CONN_MEM_LIMIT, 200 > ... < PLSQL_OPTIMIZE_LEVEL, 3 > ...
Next, the example sets the PLSQL_TIMEOUT
connection attribute to 20 seconds. When there is an attempt to execute a program that loops indefinitely, sometime after 20 seconds has passed the execution is terminated and TimesTen returns an error.
Command> ALTER SESSION SET PLSQL_TIMEOUT = 20; Session altered. Command> DECLARE v_timeout NUMBER; > BEGIN > LOOP > v_timeout := 0; > EXIT WHEN v_timeout < 0; > END LOOP; > END; > / 8509: PL/SQL execution terminated; PLSQL_TIMEOUT exceeded The command failed.
Example 6-4 View PL/SQL performance statistics
The ttPLSQLMemoryStats
built-in procedure returns statistics about PL/SQL library cache performance and activity. This example shows sample output. Refer to "ttPLSQLMemoryStats" in Oracle TimesTen In-Memory Database Reference for information about this procedure.
Command> call ttplsqlmemorystats; < Gets, 0.000000e+00 > < GetHits, 0.000000e+00 > < GetHitRatio, 1.000000 > < Pins, 0.000000e+00 > < PinHits, 0.000000e+00 > < PinHitRatio, 1.000000 > < Reloads, 0.000000e+00 > < Invalidations, 0.000000e+00 > < CurrentConnectionMemory, 0.000000e+00 > 9 rows found.
Note: CurrentConnectionMemory is related to the PLSQL_CONN_MEM_LIMIT connection attribute documented in "PL/SQL connection attributes", indicating the amount of heap memory that has actually been acquired by PL/SQL. |
If you have an existing PL/SQL program and want to see whether it uses PL/SQL features that TimesTen does not support, you can use the ttSrcScan
command line utility to scan your program for unsupported functions, packages, types, type codes, attributes, modes, and constants. This is a standalone utility that can be run without TimesTen or Oracle being installed and runs on any platform supported by TimesTen. It reads source code files as input and creates HTML and text files as output. If the utility finds unsupported items, they are logged and alternatives are suggested. You can find the ttSrcScan
executable in the quickstart/sample_util
directory in your TimesTen installation.
Specify an input file or directory for the program to be scanned and an output directory for the ttSrcScan
reports. Other options are available as well. See the README file in the sample_util
directory for information.
Oracle TimesTen In-Memory Database is a memory-optimized relational database. Deployed in the application tier, TimesTen operates on databases that fit entirely in physical memory using standard SQL interfaces. High availability for the in-memory database is provided through real-time transactional replication.
TimesTen supports a variety of programming interfaces, including ODBC (Open Database Connectivity), OCI (Oracle Call Interface), Oracle Pro*C/C++ (precompiler for embedded SQL and PL/SQL instructions in C or C++ code), JDBC (Java Database Connectivity), and PL/SQL (Oracle procedural language extension for SQL).
This preface covers the following topics:
This document is intended for anyone developing or supporting applications that use PL/SQL with TimesTen. Although it provides some overview, you should be familiar with PL/SQL or have access to more detailed documentation. This manual emphasizes TimesTen-specific functionality.
You should also be familiar with TimesTen, SQL (Structured Query Language), and database operations.
You would typically use PL/SQL through some programming interface such as those mentioned above, so should also consult any relevant additional TimesTen developer documentation.
Also see "Audiences for this document", which goes into more detail.
TimesTen documentation is available on the product distribution media and on the Oracle Technology Network:
http://www.oracle.com/technetwork/database/timesten/documentation/
Oracle documentation is also available on the Oracle Technology network. This may be especially useful for Oracle features that TimesTen supports but does not attempt to fully document:
http://www.oracle.com/technetwork/database/enterprise-edition/documentation/
In particular, these Oracle documents may be of interest:
Oracle Database PL/SQL Language Reference
Oracle Database PL/SQL Packages and Types Reference
Oracle Database SQL Language Reference
Oracle Database Reference
In addition, numerous third-party documents are available that describe PL/SQL in detail.
TimesTen supports multiple platforms. Unless otherwise indicated, the information in this guide applies to all supported platforms. The term Windows refers to Windows 2000, Windows XP and Windows Server 2003. The term UNIX refers to Solaris, Linux, HP-UX, and AIX.
Note: In TimesTen documentation, the terms "data store" and "database" are equivalent. Both terms refer to the TimesTen database unless otherwise noted. |
This document uses the following text conventions:
Convention | Meaning |
---|---|
italic | Italic type indicates terms defined in text, book titles, or emphasis. |
monospace | Monospace type indicates commands, URLs, procedure and function names, package names, attribute names, directory names, file names, text that appears on the screen, or text that you enter. |
italic monospace | Italic monospace type indicates a placeholder or a variable in a code example for which you specify or use a particular value, such as in the following example:
This means replace |
[ ] | Square brackets indicate that an item in a command line is optional. |
{ } | Curly braces indicated that you must choose one of the items separated by a vertical bar ( | ) in a command line. |
| | A vertical bar (or pipe) separates alternative arguments. |
. . . | An ellipsis (. . .) after an argument indicates that you may use more than one argument on a single command line. An ellipsis in a code example indicates that what is shown is only a partial example. |
% | The percent sign indicates the UNIX shell prompt. |
TimesTen documentation uses the following variables to identify path, file and user names.
Convention | Meaning |
---|---|
install_dir | The path that represents the directory where TimesTen is installed. |
TTinstance | The instance name for your specific installation of TimesTen. Each installation of TimesTen must be identified at installation time with a unique instance name. This name appears in the installation path. |
bits or bb | Two digits, either 32 or 64, that represent either a 32-bit or 64-bit operating system. |
release or rr | Numbers that represent a major TimesTen release, with or without dots. For example, 1121 or 11.2.1 represents TimesTen Release 11.2.1. |
DSN | TimesTen data source name (for the TimesTen database). |
Our goal is to make Oracle products, services, and supporting documentation accessible to all users, including users that are disabled. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/
.
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/support/contact.html
or visit http://www.oracle.com/accessibility/support.html
if you are hearing impaired.
For information about obtaining technical support for TimesTen products, go to the following Web address:
One of the advantages of PL/SQL in TimesTen is the ability to integrate PL/SQL procedural constructs with the flexible and powerful TimesTen SQL language.
This chapter surveys the main PL/SQL programming features described in "Overview of PL/SQL" in Oracle Database PL/SQL Language Reference. Working from simple examples, you will learn how to use PL/SQL in TimesTen. Unless otherwise noted, the examples have the same results in TimesTen as in Oracle.
See the end of the chapter for TimesTen-specific considerations. See "TimesTen PL/SQL components and operations" for an overview of how applications interact with TimesTen in general and PL/SQL in particular.
The following are the main topics of this chapter:
Note: Except where stated otherwise, the examples in this guide use the TimesTenttIsql utility. In order to display output in the examples, the setting SET SERVEROUTPUT ON is used. For more information on ttIsql , see "ttIsql" in Oracle TimesTen In-Memory Database Reference. |
The basic unit of a PL/SQL source program is the block, or anonymous block, which groups related declarations and statements. Oracle TimesTen In-Memory Database supports PL/SQL blocks.
A PL/SQL block is defined by the keywords DECLARE
, BEGIN
, EXCEPTION
, and END
. Example 2-1 shows the basic structure of a PL/SQL block.
Note: If you use Oracle In-Memory Database Cache: A PL/SQL block cannot be passed through to Oracle. |
Example 2-1 PL/SQL block structure
DECLARE --(optional) -- Variables, cursors, user-defined exceptions BEGIN --(mandatory) -- PL/SQL statements EXCEPTION --(optional) -- Actions to perform when errors occur END -- (mandatory)
You can define either anonymous or named blocks in your PL/SQL programs. This example creates an anonymous block that queries the employees
table and returns the data in a PL/SQL variable:
Command> SET SERVEROUTPUT ON; Command> DECLARE > v_fname VARCHAR2 (20); > BEGIN > SELECT first_name > INTO v_fname > FROM employees > WHERE employee_id = 100; > DBMS_OUTPUT.PUT_LINE (v_fname); > END; > / Steven PL/SQL procedure successfully completed.
You can define variables and constants in PL/SQL and then use them in procedural statements and in SQL anywhere an expression can be used.
For example:
Command> DECLARE > v_hiredate DATE; > v_deptno NUMBER (2) NOT NULL := 10; > v_location VARCHAR2 (13) := 'San Francisco'; > c_comm CONSTANT NUMBER := 1400;
You can use the %TYPE
attribute to declare a variable according to either a TimesTen column definition or another declared variable. For example, use %TYPE
to create variables emp_lname
and min_balance
:
Command> DECLARE > emp_lname employees.last_name%TYPE; > balance NUMBER (7,2); > min_balance balance%TYPE:= 1000; > BEGIN > SELECT last_name INTO emp_lname FROM employees WHERE employee_id = 100; > DBMS_OUTPUT.PUT_LINE (emp_lname); > DBMS_OUTPUT.PUT_LINE (min_balance); > END; > / King 1000 PL/SQL procedure successfully completed.
You can assign a value to a variable in the following ways.
With the assignment operator (:=
) (Example 2-2).
By selecting or fetching values into it (Example 2-3 following).
By passing the variable as an OUT
or IN OUT
parameter to a subprogram (procedure or function) and then assigning the value inside the subprogram (Example 2-4 following).
Note: TheDBMS_OUTPUT package used in these examples is supplied with TimesTen. For information on this and other supplied packages, refer to Chapter 8, "TimesTen Supplied PL/SQL Packages". |
Example 2-2 Assigning values to variables with the assignment operator
Command> DECLARE -- Assign values in the declarative section > wages NUMBER; > hours_worked NUMBER := 40; -- Assign 40 to hours_worked > hourly_salary NUMBER := 22.50; -- Assign 22.50 to hourly_salary > bonus NUMBER := 150; -- Assign 150 to bonus > country VARCHAR2(128); > counter NUMBER := 0; -- Assign 0 to counter > done BOOLEAN; > valid_id BOOLEAN; > emp_rec1 employees%ROWTYPE; > emp_rec2 employees%ROWTYPE; > TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER; > comm_tab commissions; > BEGIN -- Assign values in the executable section > wages := (hours_worked * hourly_salary) + bonus; > country := 'France'; > country := UPPER('Canada'); > done := (counter > 100); > valid_id := TRUE; > emp_rec1.first_name := 'Theresa'; > emp_rec1.last_name := 'Bellchuck'; > emp_rec1 := emp_rec2; > comm_tab(5) := 20000 * 0.15; > END; > / PL/SQL procedure successfully completed.
Note: This example uses records, which are composite data structures that have fields with different data types. You can use the%ROWTYPE attribute, as shown, to declare a record that represents a row in a table or a row from a query result set. Records are further discussed under "PL/SQL composite data types". |
Example 2-3 Using SELECT INTO to assign values to variables
Select 10% of an employee's salary into the bonus
variable:
Command> DECLARE > bonus NUMBER(8,2); > emp_id NUMBER(6) := 100; > BEGIN > SELECT salary * 0.10 INTO bonus FROM employees > WHERE employee_id = emp_id; > DBMS_OUTPUT.PUT_LINE (bonus); > END; > / 2400 PL/SQL procedure successfully completed.
Example 2-4 Assigning values to variables as parameters of a subprogram
Declare the variable new_sal
and then pass the variable as a parameter (sal
) to procedure adjust_salary
. Procedure adjust_salary
computes the average salary for employees with job_id='ST_CLERK'
and then updates sal
. After the procedure is executed, the value of the variable is displayed to verify that the variable was correctly updated.
Command> DECLARE > new_sal NUMBER(8,2); > emp_id NUMBER(6) := 126; > PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS > emp_job VARCHAR2(10); > avg_sal NUMBER(8,2); > BEGIN > SELECT job_id INTO emp_job FROM employees > WHERE employee_id = emp_id; > SELECT AVG(salary) INTO avg_sal FROM employees > WHERE job_id = emp_job; > DBMS_OUTPUT.PUT_LINE ('The average salary for ' || emp_job > || ' employees: ' || TO_CHAR(avg_sal)); > sal := (sal + avg_sal)/2; > DBMS_OUTPUT.PUT_LINE ('New salary is ' || sal); > END; > BEGIN > SELECT AVG(salary) INTO new_sal FROM employees; > DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: ' > || TO_CHAR(new_sal)); > adjust_salary(emp_id, new_sal); > DBMS_OUTPUT.PUT_LINE ('Salary should be same as new salary ' || > new_sal); > END; > / The average salary for all employees: 6461.68 The average salary for ST_CLERK employees: 2785 New salary is 4623.34 Salary should be same as new salary 4623.34 PL/SQL procedure successfully completed.
Note: This example illustrates the ability to nest PL/SQL blocks within blocks. The outer anonymous block contains an enclosed procedure. ThisPROCEDURE statement is distinct from the CREATE PROCEDURE statement documented in "PL/SQL procedures and functions", which creates a subprogram that will remain stored in the user's schema. |
Most SQL functions are supported for calls directly from PL/SQL. In the first example that follows, the function RTRIM
is used as a PL/SQL function in a PL/SQL assignment statement. In the second example, it is used as a SQL function in a static SQL statement.
Example 2-5 Using the RTRIM function from PL/SQL
Use the TimesTen PL/SQL RTRIM
built-in function to remove the right-most "x" and "y" characters from the string. Note that RTRIM
is used in a PL/SQL assignment statement.
Command> DECLARE p_var VARCHAR2(30); > BEGIN > p_var := RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy'); > DBMS_OUTPUT.PUT_LINE (p_var); > END; > / RTRIM Example PL/SQL procedure successfully completed.
Example 2-6 Using the RTRIM function from SQL
Use the TimesTen SQL function RTRIM
to remove the right-most "x" and "y" characters from the string. Note that RTRIM
is used in a static SQL statement.
Command> DECLARE tt_var VARCHAR2 (30); > BEGIN > SELECT RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy') > INTO tt_var FROM DUAL; > DBMS_OUTPUT.PUT_LINE (tt_var); > END; > / RTRIM Example PL/SQL procedure successfully completed.
You can refer to information about SQL functions in TimesTen under "Expressions" in Oracle TimesTen In-Memory Database SQL Reference. See "SQL Functions in PL/SQL Expressions" in Oracle Database PL/SQL Language Reference for information about support for SQL functions in PL/SQL.
Control structures are among the PL/SQL extensions to SQL. Oracle TimesTen In-Memory Database supports the same control structures as Oracle Database.
The following control structures are discussed here:
The IF-THEN-ELSE
and CASE
constructs are examples of conditional control. In Example 2-7, the IF-THEN-ELSE
construct is used to determine the salary raise of an employee based on the current salary. The CASE
construct is also used to choose the course of action to take based on the job_id
of the employee.
Example 2-7 Using the IF-THEN-ELSE and CASE constructs
Command> DECLARE > jobid employees.job_id%TYPE; > empid employees.employee_id%TYPE := 115; > sal employees.salary%TYPE; > sal_raise NUMBER(3,2); > BEGIN > SELECT job_id, salary INTO jobid, sal from employees > WHERE employee_id = empid; > CASE > WHEN jobid = 'PU_CLERK' THEN > IF sal < 3000 THEN sal_raise := .12; > ELSE sal_raise := .09; > END IF; > WHEN jobid = 'SH_CLERK' THEN > IF sal < 4000 THEN sal_raise := .11; > ELSE sal_raise := .08; > END IF; > WHEN jobid = 'ST_CLERK' THEN > IF sal < 3500 THEN sal_raise := .10; > ELSE sal_raise := .07; > END IF; > ELSE > BEGIN > DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid); > END; > END CASE; > DBMS_OUTPUT.PUT_LINE ('Original salary ' || sal); > -- Update > UPDATE employees SET salary = salary + salary * sal_raise > WHERE employee_id = empid; > END; > / Original salary 3100 PL/SQL procedure successfully completed.
An iterative control construct executes a sequence of statements repeatedly, as long as a specified condition is true. Loop constructs are used to perform iterative operations.
There are three loop types:
Basic loop
FOR
loop
WHILE
loop
The basic loop performs repetitive actions without overall conditions. The FOR
loop performs iterative actions based on a count. The WHILE
loops perform iterative actions based on a condition.
Example 2-8 Using a WHILE loop
Command> CREATE TABLE temp (tempid NUMBER(6), > tempsal NUMBER(8,2), > tempname VARCHAR2(25)); Command> DECLARE > sal employees.salary%TYPE := 0; > mgr_id employees.manager_id%TYPE; > lname employees.last_name%TYPE; > starting_empid employees.employee_id%TYPE := 120; > BEGIN > SELECT manager_id INTO mgr_id > FROM employees > WHERE employee_id = starting_empid; > WHILE sal <= 15000 LOOP -- loop until sal > 15000 > SELECT salary, manager_id, last_name INTO sal, mgr_id, lname > FROM employees WHERE employee_id = mgr_id; > END LOOP; > INSERT INTO temp VALUES (NULL, sal, lname); > -- insert NULL for tempid > COMMIT; > EXCEPTION > WHEN NO_DATA_FOUND THEN > INSERT INTO temp VALUES (NULL, NULL, 'Not found'); > -- insert NULLs > COMMIT; > END; > / PL/SQL procedure successfully completed. Command> SELECT * FROM temp; < <NULL>, 24000, King > 1 row found.
The CONTINUE
statement was added to Oracle Database in the Oracle 11g release and is also supported by TimesTen. It enables you to transfer control within a loop back to a new iteration.
Example 2-9 Using the CONTINUE statement
In this example, the first v_total
assignment is executed for each of the 10 iterations of the loop. The second v_total
assignment is executed for the first five iterations of the loop. The CONTINUE
statement transfers control within a loop back to a new iteration, so for the last five iterations of the loop, the second v_total
assignment is not executed. The end v_total
value is 70.
Command> DECLARE > v_total SIMPLE_INTEGER := 0; > BEGIN > FOR i IN 1..10 LOOP > v_total := v_total + i; > DBMS_OUTPUT.PUT_LINE ('Total is : ' || v_total); > CONTINUE WHEN i > 5; > v_total := v_total + i; > DBMS_OUTPUT.PUT_LINE ('Out of loop Total is: ' || v_total); > END LOOP; > END; > / Total is : 1 Out of loop Total is: 2 Total is : 4 Out of loop Total is: 6 Total is : 9 Out of loop Total is: 12 Total is : 16 Out of loop Total is: 20 Total is : 25 Out of loop Total is: 30 Total is : 36 Total is : 43 Total is : 51 Total is : 60 Total is : 70 PL/SQL procedure successfully completed.
TimesTen supports execution of PL/SQL from client applications using ODBC, OCI, Pro*C/C++, 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. |
This section covers the following topics for passing data between an application and PL/SQL:
Refer to "Bind Arguments" in Oracle Database PL/SQL Language Reference for additional information.
You can use ":
var
" notation for bind variables to be passed between your application (such as a C or Java application) and PL/SQL. The term bind variable (or sometimes host variable) is used equivalently to how the term parameter has historically been used in TimesTen, and bind variables from an application would correspond to the parameters declared in a PL/SQL procedure or function specification.
Here is a simple example using ttIsql
in to call a PL/SQL procedure that retrieves the name and salary of the employee corresponding to a specified employee ID. In this example, ttIsql
essentially acts as the calling application, and the name and salary are output from PL/SQL:
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
See "Examples using input and output parameters and bind variables" for the complete example.
See "PL/SQL procedures and functions" for how to create and define procedures and functions.
See "Binding parameters and executing statements" in Oracle TimesTen In-Memory Database C Developer's Guide and "Preparing SQL statements and setting input parameters" in Oracle TimesTen In-Memory Database Java Developer's Guide for additional information and examples for those languages.
Parameter modes define whether parameters declared in a PL/SQL subprogram (procedure or function) specification are used for input, output, or both. The three parameter modes are IN
(the default), OUT
, and IN OUT
.
An IN
parameter lets you pass a value to the subprogram being invoked. Inside the subprogram, an IN
parameter acts like a constant and cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN
parameter.
An OUT
parameter returns a value to the caller of a subprogram. Inside the subprogram, an OUT
parameter acts like a variable. You can change its value and reference the value after assigning it.
An IN OUT
parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. Typically, an IN OUT
parameter is a string buffer or numeric accumulator that is read inside the subprogram and then updated. The actual parameter that corresponds to an IN OUT
formal parameter must be a variable, not a constant or an expression.
See "Examples using input and output parameters and bind variables".
PL/SQL is tightly integrated with the TimesTen database through the SQL language. This section covers use of the following SQL features in PL/SQL:
From within PL/SQL, you can execute the following as static SQL:
DML statements: INSERT
, UPDATE
, DELETE
, and MERGE
Queries: SELECT
Transaction control: COMMIT
and ROLLBACK
Notes:
|
For information on these SQL statements, refer to "SQL S tatements" in Oracle TimesTen In-Memory Database SQL Reference.
Example 2-10 shows how to execute a query. For additional examples using TimesTen SQL in PL/SQL, see Chapter 5, "Examples Using TimesTen SQL in PL/SQL".
Example 2-10 Retrieving data with SELECT...INTO
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.
This example retrieves hire_date
and salary
for the employee with employee_id=100
from the employees
table of the HR
schema.
Command> run selectinto.sql DECLARE v_emp_hiredate employees.hire_date%TYPE; v_emp_salary employees.salary%TYPE; BEGIN SELECT hire_date, salary INTO v_emp_hiredate, v_emp_salary FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(v_emp_hiredate || ' ' || v_emp_salary); END; / 1987-06-17 24000 PL/SQL procedure successfully completed.
You can use native dynamic SQL to accomplish any of the following:
Execute a DML statement such as INSERT
, UPDATE
, or DELETE
.
Execute a DDL statement such as CREATE
or ALTER
. For example, you can use ALTER SESSION
to change a PL/SQL first connection attribute.
Call a TimesTen built-in procedure. (See "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.)
In particular, one use case is if you do not know the full text of your SQL statement until execution time. For example, during compilation you may not know the name of the column to use in the WHERE
clause of your SELECT
statement. In such a situation, you can use the EXECUTE IMMEDIATE
statement.
Another use case for dynamic SQL is for DDL, which cannot be executed in static SQL from within PL/SQL.
To call a TimesTen built-in procedure that returns a result set, create a record type and use EXECUTE IMMEDIATE
with BULK COLLECT
to fetch the results into an array.
Example 2-11 below provides an example of EXECUTE IMMEDIATE
. For additional examples, see "Examples using EXECUTE IMMEDIATE".
Notes:
|
Example 2-11 Using the EXECUTE IMMEDIATE statement to create a table
Consider a situation where you do not know your table definition at compilation. By using the EXECUTE IMMEDIATE
statement, you can create your table at execution time. This example creates 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.
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 *)
Bulk binding is a powerful feature used in the execution of SQL statements from PL/SQL to move large amounts of data between SQL and PL/SQL. (Do not confuse this with binding parameters from an application program to PL/SQL.) With bulk binding, you bind arrays of values in a single operation rather than using a loop to perform FETCH
, INSERT
, UPDATE
, and DELETE
operations multiple times. Oracle TimesTen In-Memory Database supports bulk binding, which can result in significant performance improvement.
Use the FORALL
statement to bulk-bind input collections before sending them to the SQL engine. Use BULK COLLECT
to bring back batches of results from SQL. You can bulk-collect into any type of PL/SQL collection, such as a varray, nested table, or associative array (index-by table). For additional information on collections, refer to "Using collections".
You can use the %BULK_EXCEPTIONS
cursor attribute and the SAVE EXCEPTIONS
clause with FORALL
statements. SAVE EXCEPTIONS
allows an UPDATE
, INSERT
, or DELETE
statement to continue executing after it issues an exception (for example, a constraint error). Exceptions are collected into an array that you can examine using %BULK_EXCEPTIONS
after the statement has executed. When you use SAVE EXCEPTIONS
, if exceptions are encountered during the execution of the FORALL
statement, then all rows in the collection are processed. When the statement finishes, an error is issued to indicate that at least one exception occurred. If you do not use SAVE EXCEPTIONS
, then when an exception is issued during a FORALL
statement, the statement returns the exception immediately and no other rows are processed.
Refer to "Using FORALL and BULK COLLECT Together" in Oracle Database PL/SQL Language Reference for more information on these features.
Example 2-12 shows basic use of bulk binding and the FORALL
statement. For more information and examples on bulk binding, see "Examples using FORALL and BULK COLLECT".
Example 2-12 Using the FORALL statement
In the following example, the PL/SQL program increases the salary for employees with IDs 100, 102, 104, or 110. The FORALL
statement bulk-binds the collection.
Command> CREATE OR REPLACE PROCEDURE raise_salary (p_percent NUMBER) IS > TYPE numlist_type IS TABLE OF NUMBER > INDEX BY BINARY_INTEGER; > v_id numlist_type; -- collection > BEGIN > v_id(1) := 100; v_id(2) := 102; v_id (3) := 104; v_id (4) := 110; > -- bulk-bind the associative array > FORALL i IN v_id.FIRST .. v_id.LAST > UPDATE employees > SET salary = (1 + p_percent/100) * salary > WHERE employee_id = v_id (i); > END; > / Procedure created.
Find out salaries before executing the raise_salary
procedure:
Command> SELECT salary FROM employees WHERE employee_id = 100 OR employee_id = 102 OR employee_id = 104 OR employee_id = 100; < 24000 > < 17000 > < 6000 > 3 rows found.
Execute the procedure and verify results as follows.
Command> EXECUTE raise_salary (10); PL/SQL procedure successfully completed. Command> SELECT salary FROM employees WHERE employee_id = 100 or employee_id = 102 OR employee_id = 104 OR employee_id = 100; < 26400 > < 18700 > < 6600 > 3 rows found. Command> ROLLBACK;
You can use a RETURNING INTO
clause, sometimes referred to as DML returning, with an INSERT
, UPDATE
, or DELETE
statement to return specified columns or expressions, optionally including rowids, from rows that were affected by the action. This eliminates the need for a subsequent SELECT
statement and separate round trip, in case, for example, you want to confirm what was affected or want the rowid after an insert or update.
A RETURNING INTO
clause can be used with dynamic SQL (with EXECUTE IMMEDIATE
) or static SQL.
Through the PL/SQL BULK COLLECT
feature, the clause can return items from a single row into either a set of parameters or a record, or can return columns from multiple rows into a PL/SQL collection such as a varray, nested table, or associative array (index-by table). Parameters in the INTO
part of the clause must be output only, not input/output. For information on collections, refer to "Using collections". For BULK COLLECT
, see "FORALL and BULK COLLECT operations" and "Examples using FORALL and BULK COLLECT".
SQL syntax and restrictions for the RETURNING INTO
clause in TimesTen are documented as part of the "INSERT", "UPDATE", and "DELETE" documentation in Oracle TimesTen In-Memory Database SQL Reference.
Also see "Examples using RETURNING INTO".
Refer to "RETURNING INTO Clause" in Oracle Database PL/SQL Language Reference for additional information about DML returning.
When PL/SQL programs execute SQL statements, the SQL statements are processed by TimesTen in the same manner as when SQL is executed from applications written in other programming languages. All standard behaviors of TimesTen SQL apply. In an IMDB Cache environment, this includes the ability to use all cache features from PL/SQL. When PL/SQL accesses tables in cache groups, the normal rules for those tables apply. For example, issuing a SELECT
statement against a cache instance in a dynamic cache group may cause the instance to be automatically loaded into TimesTen from Oracle Database.
In particular, the following points should be made about this functionality:
When you use static SQL in PL/SQL, any tables accessed must exist in TimesTen or the PL/SQL will not compile successfully. In the following example, ABC
must exist in TimesTen:
begin insert into abc values(1, 'Y'); end;
In an IMDB Cache environment, there is the capability to use the TimesTen passthrough facility to automatically route SQL statements from TimesTen to Oracle Database. (See "Setting a passthrough level" in Oracle In-Memory Database Cache User's Guide for details of the passthrough facility.)
With passthrough=1
, a statement can be passed through to Oracle Database if any accessed table does not exist in TimesTen. In PL/SQL, however, the statement would have to be executed using dynamic SQL.
Updating the preceding example, the following TimesTen PL/SQL block could be used to access ABC
in Oracle Database with passthrough=1
:
begin execute immediate 'insert into abc values(1, 'Y')'; end;
In this case, TimesTen PL/SQL can compile the block because the SQL statement is not examined at compile time.
While PL/SQL can be executed in TimesTen, in the current release the TimesTen passthrough facility cannot be used to route PL/SQL blocks from TimesTen to Oracle Database. For example, when using IMDB Cache with passthrough=3
, statements executed on a TimesTen connection will be routed to Oracle Database in most circumstances. In this scenario, you may not execute PL/SQL blocks from your application program, because TimesTen would attempt to forward them to Oracle Database, which is not supported. (In the passthrough=1
example, it is just the SQL statement being routed to Oracle, not the block as a whole.)
Important: PL/SQL procedures and functions can use any of the following cache operations with either definer's rights or invoker's rights: loading or refreshing a cache group with commit everyn rows, DML on AWT cache groups, DML on non-propagated cache groups (user managed cache groups without PROPAGATE enabled), SELECT on cache group tables that do not invoke passthrough or dynamic load, or UNLOAD CACHE GROUP .
PL/SQL procedures or functions that use any of the following cache operations must use invoker's rights ( |
A cursor, either explicit or implicit, is used to handle the result set of a SELECT
statement. As a programmer, you can declare an explicit cursor to manage queries that return multiple rows of data. PL/SQL declares and opens an implicit cursor for any SELECT
statement that is not associated with an explicit cursor.
Important: Be aware that in TimesTen, any operation that ends your transaction closes all cursors associated with the connection. This includes anyCOMMIT or ROLLBACK statement. This also includes any DDL statement executed within PL/SQL, because the DDLCommitBehavior connection must be set to 0 if PL/SQL is enabled, resulting in autocommits of DDL statements. |
Example 2-13 shows basic use of a cursor. See "Examples using cursors" for additional information and examples. Also see "PL/SQL REF CURSORs".
Example 2-13 Using a cursor to retrieve information about an employee
Declare a cursor c1
to retrieve the last name, salary, hire date, and job class for the employee whose employee ID is 120.
Command> DECLARE > CURSOR c1 IS > SELECT last_name, salary, hire_date, job_id FROM employees > WHERE employee_id = 120; > --declare record variable that represents a row > --fetched from the employees table > employee_rec c1%ROWTYPE; > BEGIN > -- open the explicit cursor > -- and use it to fetch data into employee_rec > OPEN c1; > FETCH c1 INTO employee_rec; > DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name); > CLOSE c1; > END; > / Employee name: Weiss PL/SQL procedure successfully completed.
Procedures and functions are PL/SQL blocks that have been defined with a specified name.
Standalone subprograms (stored procedures or functions) are created 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.
Also see "How to execute PL/SQL procedures and functions".
Notes:
|
Example 2-14 Create and execute a procedure with OUT parameters
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 aCALL statement:
Command> CALL GET_EMPLOYEE(120, :v_salary, :v_job); |
Example 2-15 Create and call a function
This 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 theCALL statement shown in the preceding example, you could use an anonymous block:
Command> begin > :n := get_sal(100); > end; > / |
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.
To create a private synonym for procedure foo
in your schema:
CREATE SYNONYM synfoo FOR foo;
To create a public synonym for foo
:
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 "Required privileges for PL/SQL statements and operations".
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.
Example 2-16 Use a synonym for a procedure
In the following example, USER1
creates a procedure in his 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.
This section discusses how to create and use PL/SQL packages.
For information about PL/SQL packages provided with TimesTen, refer to Chapter 8, "TimesTen Supplied PL/SQL Packages."
A package is a database object that groups logically related PL/SQL types, variables, and subprograms. You specify the package and then define its body in separate steps.
The package specification is the interface to the package, declaring the public types, variables, constants, exceptions, cursors, and subprograms that are visible outside the immediate scope of the package. The body defines the objects declared in the specification, as well as queries for the cursors, code for the subprograms, and private objects that are not visible to applications outside the package.
TimesTen stores the package specification separately from the package body in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body.
Note: The syntax for creating packages and package bodies is the same as in Oracle Database; however, while Oracle documentation mentions that you must run a script namedDBMSSTDX.SQL , this does not apply to TimesTen. |
To create packages and store them permanently in the database, use the CREATE PACKAGE
and CREATE PACKAGE BODY
statements.
To create a new package, do the following:
Create the package specification with the CREATE PACKAGE
statement.
You can declare program objects in the package specification. Such objects are referred to as public objects and can be referenced outside the package, and by other objects in the package.
Optionally use CREATE OR REPLACE PACKAGE
if you want the package specification to be replaced if it already exists.
Create the package body with the CREATE PACKAGE BODY
(or CREATE OR REPLACE PACKAGE BODY
) statement.
You can declare and define program objects in the package body.
You must define public objects declared in the package specification.
You can declare and define additional package objects, referred to as private objects. Private objects are declared in the package body rather than in the package specification, so they can be referenced only by other objects in the package. They cannot be referenced outside the package.
Use ALTER PACKAc1GE
to explicitly compile the member procedures and functions of a package or modify the compilation options.
For more information on the CREATE PACKAGE
, CREATE PACKAGE BODY
, and ALTER PACKAGE
statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.
Note: See "Showing errors in ttIsql" for how to get information when you encounter errors in compiling a package. |
Example 2-17 Create and use a package
Consider the case where you want to add a row to the employees tables when you hire a new employee and delete a row from the employees table when an employee leaves your company. The following example creates two procedures to accomplish these tasks and bundles the procedures in a package. The package also contains a function to return the count of employees with a salary greater than that of a specific employee. The example then executes the function and procedures and verifies the results.
Command> CREATE OR REPLACE PACKAGE emp_actions AS > PROCEDURE hire_employee (employee_id NUMBER, > last_name VARCHAR2, > first_name VARCHAR2, > email VARCHAR2, > phone_number VARCHAR2, > hire_date DATE, > job_id VARCHAR2, > salary NUMBER, > commission_pct NUMBER, > manager_id NUMBER, > department_id NUMBER); > PROCEDURE remove_employee (emp_id NUMBER); > FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER; > END emp_actions; > / Package created. Command> -- Package body: > CREATE OR REPLACE PACKAGE BODY emp_actions AS > -- Code for procedure hire_employee: > PROCEDURE hire_employee (employee_id NUMBER, > last_name VARCHAR2, > first_name VARCHAR2, > email VARCHAR2, > phone_number VARCHAR2, > hire_date DATE, > job_id VARCHAR2, > salary NUMBER, > commission_pct NUMBER, > manager_id NUMBER, > department_id NUMBER) IS > BEGIN > INSERT INTO employees VALUES (employee_id, > last_name, > first_name, > email, > phone_number, > hire_date, > job_id, > salary, > commission_pct, > manager_id, > department_id); > END hire_employee; > -- Code for procedure remove_employee: > PROCEDURE remove_employee (emp_id NUMBER) IS > BEGIN > DELETE FROM employees WHERE employee_id = emp_id; > END remove_employee; > -- Code for function num_above_salary: > FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS > emp_sal NUMBER(8,2); > num_count NUMBER; > BEGIN > SELECT salary INTO emp_sal FROM employees > WHERE employee_id = emp_id; > SELECT COUNT(*) INTO num_count FROM employees > WHERE salary > emp_sal; > RETURN num_count; > END num_above_salary; > END emp_actions; > / Package body created. Command> BEGIN > /* call function to return count of employees with salary > greater than salary of employee with employee_id = 120 > */ > DBMS_OUTPUT.PUT_LINE > ('Number of employees with higher salary: ' || > TO_CHAR(emp_actions.num_above_salary(120))); > END; > / Number of employees with higher salary: 33 PL/SQL procedure successfully completed.
Verify the count of 33.
Command> SELECT salary FROM employees WHERE employee_id = 120; < 8000 > 1 row found. Command> SELECT COUNT (*) FROM employees WHERE salary > 8000; < 33 > 1 row found.
Now add an employee and verify results. Then, remove the employee and verify that the employee was deleted from the employees
table.
Command> BEGIN > emp_actions.hire_employee(300, > 'Belden', > 'Enrique', > 'EBELDEN', > '555.111.2222', > '31-AUG-04', > 'AC_MGR', > 9000, > .1, > 101, > 110); > END; > / PL/SQL procedure successfully completed. Command> SELECT * FROM employees WHERE employee_id = 300; < 300, Belden, Enrique, EBELDEN, 555.111.2222, 2004-08-31 00:00:00, AC_MGR, 9000 , .1, 101, 110 > 1 row found. Command> BEGIN > emp_actions.remove_employee (300); > END; > / PL/SQL procedure successfully completed. Command> SELECT * FROM employees WHERE employee_id = 300; 0 rows found.
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.
To create a private synonym for package foopkg
in your schema:
CREATE SYNONYM synfoopkg FOR foopkg;
To create a public synonym for foopkg
:
CREATE PUBLIC SYNONYM pubfoopkg FOR foopkg;
Also see "Using synonyms for procedures and functions" and "Required privileges for PL/SQL statements and operations".
Note: You cannot create synonyms for individual member subprograms of a package.This is valid: create or replace public synonym pubtestpkg for testpkg; This is not valid: create or replace public synonym pubtestproc for testpkg.testproc; |
Wrapping is the process of hiding PL/SQL source code. You can wrap PL/SQL source code with the wrap
utility, processes an input SQL file and wraps only the PL/SQL units in the file, such as a package specification, package body, function, or procedure.
Consider the following example that creates a file named wrap_test.sql
, the purpose of which is to create a procedure named wraptest
. It then uses the wrap
utility to process wrap_test.sql
. The procedure is created with the source code hidden and executes successfully. As a final step, the ALL_OBJECTS
view is queried to see the wrapped source code.
$ cat wrap_test.sql CREATE OR REPLACE PROCEDURE wraptest IS TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; all_emps emp_tab; BEGIN SELECT * BULK COLLECT INTO all_emps FROM employees; FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).employee_id); END LOOP; END; / $ wrap iname=wrap_test.sql PL/SQL Wrapper: Release 11.1.0.7.0- Production on Thu Sep 11 23:27:04 2008 Copyright (c) 1993, 2009, Oracle. All rights reserved. Processing wrap_test.sql to wrap_test.plb $ cat wrap_test.plb CREATE OR REPLACE PROCEDURE wraptest wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 7 109 124 88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM xKpGTRsHj7Al9eLe4pAutkqgVVDBveT5RrLRnKoGp79VjbFXinShf9huGTE9mnPh2CJgUw== / $ ttIsql SampleDatabase Copyright (c) 1996-2009, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=SampleDatabase"; Connection successful: ... PermSize=32;TypeMode=0;PLSQL_MEMORY_ADDRESS=20000000; (Default setting AutoCommit=1) Command> @wrap_test.plb CREATE OR REPLACE PROCEDURE wraptest wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 7 109 124 88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM Command> SET SERVEROUTPUT ON; Command> BEGIN > wraptest (); > END; > / Emp Id: 100 Emp Id: 101 Emp Id: 102 Emp Id: 103 Emp Id: 104 Emp Id: 105 Emp Id: 106 Emp Id: 107 Emp Id: 108 Emp Id: 109 PL/SQL procedure successfully completed. Command> SELECT text FROM all_source WHERE name = 'WRAPTEST'; < PROCEDURE wraptest wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 7 109 124 88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM xKpGTRsHj7Al9eLe4pAutkqgVVDBveT5RrLRnKoGp79VjbFXinShf9huGTE9mnPh2CJgUw== > 1 row found.
In TimesTen, any operation that ends your transaction closes all cursors associated with the connection. This includes the following:
Any COMMIT
or ROLLBACK
statement
Any DDL statement in PL/SQL
This is because when PL/SQL is enabled (the PLSQL
first connection attribute is set to 1), the TimesTen DDLCommitBehavior
general connection attribute must be set to 0 for Oracle mode (autocommit DDL).
For example, consider the following scenario, where you want to recompile a set of procedures. This would not work, because the first time ALTER PROCEDURE
is executed, the cursor (pnamecurs
) would be closed:
declare cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%'; begin for rec in pnamecurs loop execute immediate 'alter procedure ' || rec.object_name || ' compile'; end loop; end; /
Instead, you can do something like the following, which fetches all the procedure names into an internal table then executes ALTER PROCEDURE
on them with no active cursor:
declare cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%'; type tbl is table of c%rowtype index by binary_integer; myprocs tbl; begin open pnamecurs; fetch pnamecurs bulk collect into myprocs; close pnamecurs; for i in 1..myprocs.count loop execute immediate 'alter procedure ' || myprocs(i).object_name || ' compile'; end loop; end; /
Copyright © 1994-2016, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.
If this document is in preproduction status:
This documentation is in preproduction status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.
If this document is in private preproduction status:
The information contained in this document is for informational sharing purposes only and should be considered in your capacity as a customer advisory board member or pursuant to your beta trial agreement only. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described in this document remains at the sole discretion of Oracle.
This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc
.
Access to Oracle Support
Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info
or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs
if you are hearing impaired.