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.