Passing Data From PL/SQL Using OUT Parameters Within ttIsql

You can pass data back to applications from PL/SQL by using OUT parameters.

This example returns information about how full is a TimesTen database.

Create the tt_space_info PL/SQL procedure and use SQL to provide values for the permpct, permmaxpct, temppct, and tempmaxpct parameters.

Command> CREATE OR REPLACE PROCEDURE tt_space_info
(permpct    OUT PLS_INTEGER,
permmaxpct OUT PLS_INTEGER,
temppct    OUT PLS_INTEGER,
tempmaxpct OUT PLS_INTEGER) AS
monitor    sys.monitor%ROWTYPE;
BEGIN
SELECT * INTO monitor FROM sys.monitor;
permpct := monitor.perm_in_use_size * 100 / 
monitor.perm_allocated_size;
permmaxpct := monitor.perm_in_use_high_water * 100 / 
monitor.perm_allocated_size;
temppct := monitor.temp_in_use_size * 100 / 
monitor.temp_allocated_size;
tempmaxpct := monitor.temp_in_use_high_water * 100 / 
monitor.temp_allocated_size;
END;
/

Procedure created.

Declare the variables and call tt_space_info. The parameter values are passed back to ttIsql so they can be printed:

Command> VARIABLE permpct NUMBER
Command> VARIABLE permpctmax NUMBER
Command> VARIABLE temppct NUMBER
Command> VARIABLE temppctmax NUMBER
Command> BEGIN
tt_space_info(:permpct, :permpctmax, :temppct, :temppctmax);
END;
/

PL/SQL procedure successfully completed.

Command> PRINT permpct;
PERMPCT              : 4

Command> PRINT permpctmax;
PERMPCTMAX           : 4

Command> PRINT temppct;
TEMPPCT              : 11

Command> PRINT temppctmax;
TEMPPCTMAX           : 11

You can also pass back a statement handle that can be run by a PL/SQL statement with an OUT refcursor parameter. The PL/SQL statement can choose the query associated with the cursor. The following example opens a refcursor, which randomly chooses between ascending or descending order.

Command> VARIABLE ref REFCURSOR;
Command> BEGIN
IF (mod(dbms_random.random(), 2) = 0) THEN
open :ref for select object_name from SYS.ALL_OBJECTS order by 1 asc;
ELSE
open :ref for select object_name from SYS.ALL_OBJECTS order by 1 desc;
end if;
END;
/

PL/SQL procedure successfully completed.

To fetch the result set from the refcursor, use the PRINT command:

Command> PRINT ref 
REF           : 
< ACCESS$ >
< ALL_ARGUMENTS >
< ALL_COL_PRIVS >
< ALL_DEPENDENCIES >
...
143 rows found.

Or if the result set was ordered in descending order, the following would print:

Command> PRINT ref
REF          : 
< XLASUBSCRIPTIONS >
< WARNING_SETTINGS$ >
< VIEWS >
...
143 rows found.