Defines a module as an object in an Oracle Rdb database. Stored
with the module are its functions and procedures. A function or
procedure written in SQL that resides with the data in a database
is called a stored function or stored procedure. Likewise, a
module stored in a database is called a stored module. A stored
routine refers to either a stored procedure or stored function.
You invoke a stored procedure with the CALL statement from a
simple statement procedure in embedded SQL, SQL module language,
or interactive SQL or with the CALL statement from within a
compound statement.
You invoke a stored function by specifying the function name in a
value expression.
SQL uses the concept of a module as its mechanism for storing,
showing, deleting, and granting and revoking privileges on stored
routines within a database. This means you cannot store, delete,
or grant and revoke privileges on individual stored routines.
Should you need to remove a stored routine, use the DROP FUNCTION
routine-name CASCADE or DROP PROCEDURE routine-name CASCADE
syntax.
In general, SQL operates on modules, not stored routines.
However, there are a few exceptions: DROP FUNCTION, DROP
PROCEDURE, RENAME, SHOW FUNCTION, SHOW PROCEDURE, and CALL. The
SHOW FUNCTION statement displays information about functions.
The SHOW PROCEDURE statement displays individual procedures in a
stored module. The CALL statement can invoke only a single stored
procedure.
1 – Environment
You can use the CREATE MODULE statement in a simple statement
procedure:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mCREATE[m[1m [1;4mMODULE[m[1m q> <module-name> wqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m mq> [1;4mSTORED[m[1m [1;4mNAME[m[1m IS <stored-name> qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqqqqqqqqwqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqk [m
[1m mq> [1;4mLANGUAGE[m[1m [1;4mSQL[m [1mqqqj[m [1m mq> [1;4mAUTHORIZATION[m[1m <auth-id> qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqwqqqqqqqqk [m
[1m mq> [1;4mCOMMENT[m[1m [1;4mIS[m[1m qwq> 'string' qqqwqj mq> declare-clause qqj x [m
[1m mqqqqqq / <qqqqqj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqwq> routine-clause qqqwqq> [1;4mEND[m[1m [1;4mMODULE[m[1m qqq> [m
[1m mqqqqqqqq <qqqqqqqqqqqj [m
[1m [m
(B)0[m[1mdeclare-clause = [m
[1m [m
[1mqqwqwq> declare-transaction-statement qqqqqqqqqqqwqwqq> [m
[1m x tq> declare-local-temporary-table-statement qu x [m
[1m x mq> declare-variable-statement qqqqqqqqqqqqqqj x [m
[1m mqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1mroutine-clause = [m
[1m [m
[1mqw> [1;4mPROCEDURE[m[1m <procedure-name> wqwqqqqqqqqqqqqqqqqqqq> qqqqqqqqqqqwk [m
[1m m> [1;4mFUNCTION[m[1m <function-name> qqj m> [1;4mSTORED[m[1m [1;4mNAME[m[1m IS <stored-name> qjx [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqqqwqqqqqqqqqqqqqqqqqqqq> qqqqqqqqqqqqqqqqqqqqqwqqqk [m
[1m mqq> ( qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq ) qj x [m
[1m mwq> parameter-decl[m [1mqqqqqqqqwj x [m
[1m mqqqqqqqqqqq , <qqqqqqqqqqqj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqk[m
[1m m> [1;4mRETURNS[m[1m result-data-type qjm[mqqq> [1mfunction-attr qqqj[m [1m [m [1m x[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqj[m
[1mmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ; qwq> compound-statement qqqqqw> ; qq>[m
[1mm> routine-attr [mqqqqqqqqqqqqqqqqq[1mj[m [1m [m [1mtq> compound-use-statement qu [m
[1m [m [1mmq> external-body-clause qqqj[m
(B)0[m[1mparameter-decl = [m
[1m [m
[1mqqwqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqwwq> data-type qqqqqwqqqk [m
[1m tq> [1;4mIN[m[1m qqqqumq> <parameter-name> qjmq> <domain-name> qj x [m
[1m tq> [1;4mOUT[m[1m qqqu[m [1m [m [1m [m [1m [m [1mx[m [1m [m
[1m mq> [1;4mINOUT[m[1m qj[m [1m [m [1mx [m [1m [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmqwqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqk[m
[1mmq> mechanism-clause qj[m [1mmq> [1;4mDEFAULT[m[1m value-expr qj[m [1mx[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1m mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqq> [m
[1m mq> [1;4mCOMMENT[m[1m [1;4mIS[m[1m qwq> 'string' qqqwj [m
[1m [m [1mmqqqqqq / <qqqqqj[m
(B)0[m[1mfunction-attr =[m
[1m [m
[1mqwwqqqqqqqqwqwq> [1;4mVARIANT[m[1m qqqqqqqwwq> [m
[1mxmq> [1;4mNOT[m[1m qj mq> [1;4mDETERMINISTIC[m[1m qjx[m
[1m mqq> [1;4mLANGUAGE[m[1m [1;4mSQL[m[1m qqqqqqqqqqqqqqj[m
[1m [m
(B)0[m[1mroutine-attr =[m
[1mqqwq> [1;4mCOMMENT[m[1m IS qqwq> 'string' qqwqwqq>[m
[1mx[m [1mmqqqqqq[m [1m,[m [1m<qqqqj[m [1mx[m
[1mmq> [1;4mUSAGE[m[1m IS qwq> [1;4mLOCAL[m [1mqqqwqqqqqqj[m
[1mmq>[m [1;4mGLOBAL[m[1m qqj[m
3 – Arguments
3.1 – AUTHORIZATION auth id
A name that identifies the definer of a module and is used to
perform privilege validation for the module. the User_Supplied_
Names HELP topic for information about using authorization
identifiers.
3.2 – COMMENT_IS
Adds a comment about the module, routine, and parameter. SQL
displays the text of the comment when it executes a SHOW MODULE,
SHOW FUNCTION, or SHOW PROCEDURE statement. Enclose the comment
in single quotation marks (') and separate multiple lines in a
comment with a slash mark (/).
3.3 – compound-statement
Allows you to include more than one SQL statement in a stored
routine. See the Compound_Statement HELP topic for more
information.
3.4 – compound-use-statement
Allows you to include one SQL statement in a stored routine. See
the Compound_Statement HELP topic for more information.
If you are defining a stored function, the simple statement must
be the RETURNS clause.
3.5 – data-type
A valid SQL data type. Specifying an explicit data type is an
alternative to specifying a domain name. See the Data_Types HELP
topic for more information on data types.
3.6 – decl-local-temp-table-statement
Declares a local temporary table for the module. See the DECLARE
LOCAL_TEMPORARY_TABLE statement for more information.
3.7 – declare-transaction-statement
Declares a transaction for the module. Only one declare-
transaction-statement is permitted for each module. If omitted,
an implicit DECLARE TRANSACTION READ WRITE is used. See the
DECLARE TRANSACTION statement for more information.
3.8 – declare-variable-statement
Declares a global variable for the module. See the statement
DECLARE Variable for more information.
3.9 – DEFAULT value-expr
Specifies the default value of a parameter for a function or
procedure defined with mode IN. If you omit this parameter or if
the Call statement argument list or function invocation specifies
the DEFAULT keyword, then the value-expr specified with this
clause is used. The parameter uses NULL as the default if you do
not specify a value expression explicitly.
3.10 – DETERMINISTIC
Syntax options:
DETERMINISTIC | NOT DETERMINISTIC
The clause controls the evaluation of an external function in the
scope of a query:
o NOT DETERMINISTIC
Specifying the NOT DETERMINISTIC clause forces evaluation
of corresponding functions (in scope of a single query)
every time the function appears. If a function can return a
different result each time it is invoked, you should use the
DETERMINISTIC clause.
o DETERMINISTIC
Specifying the DETERMINISTIC clause can result in a single
evaluation of corresponding function expressions (in scope
of a single query), and the resulting value is used in all
occurrences of the corresponding function expression. When you
use the DETERMINISTIC clause, Oracle Rdb evaluates whether or
not to invoke the function each time it is used.
For example:
SELECT * FROM T1 WHERE F1() > 0 AND F1() < 20;
If you define the F1 function as DETERMINISTIC, the function
F1() may be evaluated just once depending on the optimizer. If
you define the F1 function as NOT DETERMINISTIC, the function
F1() is evaluated twice.
DETERMINISTIC is the default.
The DETERMINISTIC or NOT DETERMINISTIC clause is not allowed on
procedure definitions.
3.11 – domain-name
The name of a domain created in a CREATE DOMAIN statement. For
more information about domains, see the CREATE DOMAIN statement.
3.12 – DEFAULT value-expr
Specifies the default value of a parameter for a function or
procedure defined with mode IN. If you omit this parameter or if
the Call statement argument list or function invocation specifies
the DEFAULT keyword, then the value-expr specified with this
clause is used. The parameter uses NULL as the default if you do
not specify a value expression explicitly.
3.13 – FUNCTION function-name
A user-supplied name that you give to a function in a module. The
name you specify must be unique within the database definition.
3.14 – IN parameter name
Specifies the parameter modes used in the procedure. The IN
parameter names the parameter that is read into the routine,
however is never set and cannot be written to.
The IN parameter is the only mode allowed for functions.
Each parameter name must be unique within the routine.
3.15 – INOUT parameter name
Specifies the parameter modes used in the procedure. The INOUT
parameter names a parameter that inputs data (is read) as well as
receives data (is set). The INOUT parameter is a parameter that
is modified.
Each parameter name must be unique within the procedure.
You cannot use the INOUT parameter mode for stored functions.
3.16 – LANGUAGE_SQL
The LANGUAGE keyword and the SQL argument signify that the
procedures in a module are to be invoked by SQL statements, not a
host language program.
With unstored procedures, the LANGUAGE keyword specifies the name
of a host language; this identifies the host language in which
the program calling a module's procedures is written.
Beginning with Oracle Rdb Release 7.1, this clause is optional.
3.17 – module-name
A user-supplied name that you assign to a module.
See the User_Supplied_Names HELP topic for more information on
user-supplied names.
3.18 – OUT parameter name
Specifies the parameter modes used in the procedure. The OUT
parameter names the parameter into which data is being sent. The
OUT parameter is set, but never read.
Each parameter name must be unique within the procedure.
You cannot use the OUT parameter mode for stored functions.
3.19 – parameter-decl
Specifies the parameters and parameter modes used in a stored or
external routine.
3.20 – PROCEDURE procedure-name
A user-supplied name that you give to a procedure in a module.
The name you specify must be unique within the database
definition.
3.21 – RETURNS result data type
Specifies the data type or domain of the result of the function
invocation. This clause is only valid when defining a function.
You can only use the RETURNS clause when defining a function.
3.22 – routine-clause
The definition of a stored function or stored procedure created
in a module.
3.23 – STORED_NAME_IS
Specifies a name that Oracle Rdb uses to access a module
procedure or function created in a multischema database. Or
specifies a name that Oracle Rdb uses to access a procedure or
a function created in a stored module.
3.24 – USAGE_IS
Specifies how the function or procedure can be called:
o USAGE IS GLOBAL indicates that the function or procedure can
be called outside the current module. This is the default.
o USAGE IS LOCAL specifies that the routine is restricted to
references within the module. This clause is provided for
compatibility with CREATE MODULE but is not allowed for CREATE
FUNCTION or CREATE PROCEDURE.
3.25 – VARIANT
Syntax options:
VARIANT | NOT VARIANT
These clauses are synonyms for the DETERMINISTIC and NOT
DETERMINISTIC clauses. The DETERMINISTIC clause indicates that
the same inputs to the function will generate the same output.
It is the same as the NOT VARIANT clause. The NOT DETERMINISTIC
clause indicates that the output of the function does not depend
on the inputs. It is the same as the VARIANT clause.
This clause is deprecated. Use DETERMINISTIC instead.
3.26 – variant-clause
Syntax options:
DETERMINISTIC | NOT DETERMINISTIC
The clause controls the evaluation of an external function in the
scope of a query:
o NOT DETERMINISTIC
Specifying the NOT DETERMINISTIC clause forces evaluation
of corresponding functions (in scope of a single query)
every time the function appears. If a function can return a
different result each time it is invoked, you should use the
DETERMINISTIC clause.
o DETERMINISTIC
Specifying the DETERMINISTIC clause can result in a single
evaluation of corresponding function expressions (in scope
of a single query), and the resulting value is used in all
occurrences of the corresponding function expression. When you
use the DETERMINISTIC clause, Oracle Rdb evaluates whether or
not to invoke the function each time it is used.
For example:
SELECT * FROM T1 WHERE F1() > 0 AND F1() < 20;
If you define the F1 function as DETERMINISTIC, the function
F1() may be evaluated just once depending on the optimizer. If
you define the F1 function as NOT DETERMINISTIC, the function
F1() is evaluated twice.
DETERMINISTIC is the default.
The DETERMINISTIC or NOT DETERMINISTIC clause is not allowed on
procedure definitions.
4 – Examples
Example 1: Creating a stored module and stored procedure
The following example shows how to create a stored module and
stored procedure using interactive SQL:
SQL> CREATE MODULE testmod LANGUAGE SQL
cont> PROCEDURE testproc;
cont> COMMIT;
cont> END MODULE;
SQL> SHOW MODULE testmod
Module name is: TESTMOD
Source:
TESTMOD LANGUAGE SQL
Owner is:
Module ID is: 1
Example 2: Creating a stored module with SQL module language
The following code fragment shows how to create a stored module
as part of a procedure in a nonstored module:
PROCEDURE create_them
SQLCODE;
CREATE MODULE my LANGUAGE SQL AUTHORIZATION smith
PROCEDURE p1 ( :x CHAR(5) );
BEGIN
INSERT INTO s (snum) VALUES (:x);
END;
PROCEDURE p2 ( :y SMALLINT );
BEGIN
SELECT STATUS INTO :y FROM s LIMIT TO 1 ROW;
END;
PROCEDURE p3 (:x INT, :y SMALLINT );
BEGIN
INSERT INTO s (snum) VALUES (:x);
SELECT STATUS INTO :y FROM s WHERE snum = :x;
END;
PROCEDURE p4 (:x CHAR(5), :y CHAR(20) );
BEGIN
INSERT INTO s (snum,sname) VALUES (:x, :y);
SELECT sname INTO :y FROM s WHERE snum = :x;
END;
END MODULE;
Example 3: Creating a stored module containing a stored routines
SQL> CREATE MODULE utility_functions
cont> LANGUAGE SQL
cont> --
cont> -- Define a stored procedure.
cont> --
cont> PROCEDURE trace_date (:dt DATE);
cont> BEGIN
cont> TRACE :dt;
cont> END;
cont> --
cont> FUNCTION mdy (IN :dt DATE) RETURNS CHAR(10)
cont> COMMENT 'Returns the date in month/day/year format';
cont> BEGIN
cont> IF :dt IS NULL THEN
cont> RETURN '**/**/****';
cont> ELSE
cont> CALL trace_date (:dt);
cont> RETURN CAST(EXTRACT(MONTH FROM :dt) AS VARCHAR(2)) || '/' ||
cont> CAST(EXTRACT(DAY FROM :dt) AS VARCHAR(2)) || '/' ||
cont> CAST(EXTRACT(YEAR FROM :dt) AS VARCHAR(4));
cont> END IF;
cont> END;
cont> END MODULE;
Example 4: Using a stored function in a SELECT statement
SQL> SELECT mdy(job_end), job_end
cont> FROM job_history WHERE employee_id = '00164';
JOB_END
**/**/**** NULL
9/20/1981 20-Sep-1981
2 rows selected
Example 5: Using declared local temporary tables in stored
procedures
SQL> -- The following table must exist in order to execute the following
SQL> -- queries.
SQL> --
SQL> CREATE TABLE payroll
cont> (employee_id CHAR(5),
cont> hours_worked INTEGER,
cont> hourly_sal REAL,
cont> week_date CHAR(10));
SQL> COMMIT;
SQL> --
SQL> -- Create the module containing a declared local temporary table.
SQL> --
SQL> CREATE MODULE paycheck_decl_mod
cont> LANGUAGE SQL
cont> DECLARE LOCAL TEMPORARY TABLE module.paycheck_decl_tab
cont> (employee_id ID_DOM,
cont> last_name CHAR(14) ,
cont> hours_worked INTEGER,
cont> hourly_sal INTEGER(2),
cont> weekly_pay INTEGER(2))
cont> ON COMMIT PRESERVE ROWS
cont> --
cont> -- Create the procedure to insert rows.
cont> --
cont> PROCEDURE paycheck_ins_decl;
cont> BEGIN
cont> INSERT INTO module.paycheck_decl_tab
cont> (employee_id, last_name, hours_worked, hourly_sal, weekly_pay)
cont> SELECT p.employee_id, e.last_name,
cont> p.hours_worked, p.hourly_sal,
cont> p.hours_worked * p.hourly_sal
cont> FROM employees e, payroll p
cont> WHERE e.employee_id = p.employee_id
cont> AND p.week_date = '1995-08-01';
cont> END;
cont> --
cont> -- Create the procedure to count the low hours.
cont> --
cont> PROCEDURE low_hours_decl (:cnt INTEGER);
cont> BEGIN
cont> SELECT COUNT(*) INTO :cnt FROM module.paycheck_decl_tab
cont> WHERE hours_worked < 40;
cont> END;
cont> END MODULE;
SQL> --
SQL> -- Call the procedure to insert the rows.
SQL> --
SQL> CALL paycheck_ins_decl();
SQL> --
SQL> -- Declare a variable and call the procedure to count records with
SQL> -- low hours.
SQL> --
SQL> DECLARE :low_hr_cnt integer;
SQL> CALL low_hours_decl(:low_hr_cnt);
LOW_HR_CNT
2
SQL> --
SQL> -- Because the table is a declared local temporary table, you cannot
SQL> -- access it from outside the stored module that contains it.
SQL> --
SQL> SELECT * FROM module.paycheck_decl_tab;
%SQL-F-RELNOTDCL, Table PAYCHECK_DECL_TAB has not been declared in module or
environment
Example 6: Creating a stored procedure containing a simple
statement
SQL> CREATE MODULE a
cont> LANGUAGE SQL
cont> PROCEDURE new_salary_proc
cont> (:id CHAR (5),
cont> :new_salary INTEGER (2));
cont> UPDATE salary_history
cont> SET salary_end = CURRENT_TIMESTAMP
cont> WHERE employee_id = :id;
cont> END MODULE;
Example 7: Declaring a Global Variable to Exchange Information
Between Two Routines
SQL> CREATE MODULE sample
cont> LANGUAGE SQL
cont> DECLARE :iter_count INTEGER
cont> PROCEDURE set_iter (IN :val INTEGER)
cont> COMMENT IS 'Validate the iteration count and assign'
cont> / 'to a global variable.';
cont> BEGIN
cont> IF (:val IS NULL) OR (:val < 1) THEN
cont> SIGNAL 'XXXXX'; --illegal value
cont> ELSE
cont> SET :iter_count =:val;
cont> TRACE 'Iteration count set to ', :val;
cont> END IF;
cont> END;
cont> FUNCTION GET_ITER ()
cont> RETURNS INTEGER
cont> COMMENT IS 'Trace the value used and then return the'
cont> / 'value from the global variable.';
cont> BEGIN
cont> TRACE 'Using iteration count ', :iter_count;
cont> RETURN :iter_count;
cont> END;
cont> END MODULE;
Example 8: Using a cursor implemented by external routines
This example uses multiple external routines to manage a table
cursor in the external routine database environment. This
management includes the OPEN, FETCH and CLOSE of a single cursor.
Several domains are defined so that parameter data types can be
consistently defined in the database that contain the application
and also the database upon which the cursor is open.
create domain SQLSTATE_T char(5);
create domain STATUS_CODE char(1);
create domain STATUS_NAME char(8);
create domain STATUS_TYPE char(14);
The external function interface is contained within a single
CREATE MODULE statement. This module also contains the
application in a single stored SQL procedure.
create module EX
language SQL
-- These procedure define the interface to the external
-- routines that implement the transaction and cursor operations
--
procedure EX_START_READ_TXN
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
comment is 'start a READ ONLY transaction';
procedure EX_COMMIT
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
procedure EX_OPEN_CURSOR
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
comment is 'find all rows in WORK_STATUS order by STATUS_CODE';
procedure EX_CLOSE_CURSOR
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
procedure EX_FETCH_CURSOR
(inout :ss sqlstate_t,
out :s_code STATUS_CODE, out :s_code_ind integer,
out :s_name STATUS_NAME, out :s_name_ind integer,
out :s_type STATUS_TYPE, out :s_type_ind integer);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
-- This SQL procedures implements a simple application
--
procedure WORK_STATUS
comment is 'Use an external cursor to fetch all rows in the'
/ 'WORK_STATUS table';
begin
declare :s_code STATUS_CODE;
declare :s_name STATUS_NAME;
declare :s_type STATUS_TYPE;
declare :s_code_ind, :s_name_ind, :s_type_ind integer;
declare :ss sqlstate_t;
-- start a read-only transaction on the PERSONNEL database
call EX_START_READ_TXN (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- open the cursor on the work-status table
call EX_OPEN_CURSOR (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- now loop and fetch all the rows
FETCH_LOOP:
loop
call EX_FETCH_CURSOR (:ss,
:s_code, :s_code_ind,
:s_name, :s_name_ind,
:s_type, :s_type_ind);
case :ss
when '02000' then
-- no more rows to fetch
leave FETCH_LOOP;
when '00000' then
begin
-- we have successfully fetched a row, so display it
trace 'Status Code: ', case when :s_code_ind < 0
then 'NULL'
else :s_code
end;
trace 'Status Name: ', case when :s_name_ind < 0
then 'NULL'
else :s_name
end;
trace 'Status Type: ', case when :s_type_ind < 0
then 'NULL'
else :s_type
end;
trace '***';
end;
else
-- signal will implicitly leave the stored procedure
SIGNAL :ss;
end case;
end loop;
-- close the cursor
call EX_CLOSE_CURSOR (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- commit the transaction
call EX_COMMIT (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
end;
end module;
The external procedures for this this example are written using
the SQL module language. However, any language with embedded SQL,
such as C, could have been used.
module EX
language GENERAL
parameter colons
-- EX: Sample application
-- Process the WORK_STATUS table using a table cursor
--
declare alias filename 'PERSONNEL'
declare c cursor for
select status_code, status_name, status_type
from WORK_STATUS
order by status_code
procedure EX_START_READ_TXN
(sqlstate);
begin
-- abort any stray transactions
rollback;
-- start a READ ONLY transaction
set transaction read only;
end;
procedure EX_COMMIT
(sqlstate);
commit work;
procedure EX_ROLLBACK
(sqlstate);
rollback work;
procedure EX_OPEN_CURSOR
(sqlstate);
open c;
procedure EX_CLOSE_CURSOR
(sqlstate);
close c;
procedure EX_FETCH_CURSOR
(sqlstate,
:s_code STATUS_CODE,
:s_code_ind integer,
:s_name STATUS_NAME,
:s_name_ind integer,
:s_type STATUS_TYPE,
:s_type_ind integer);
fetch c
into :s_code indicator :s_code_ind,
:s_name indicator :s_name_ind,
:s_type indicator :s_type_ind;
procedure EX_DISCONNECT
(sqlstate);
disconnect default;
When run the application calls the external procedures to open
the cursor and fetch the rows and display them using the TRACE
statement.
SQL> set flags 'trace';
SQL>
SQL> call WORK_STATUS ();
~Xt: Status Code: 0
~Xt: Status Name: INACTIVE
~Xt: Status Type: RECORD EXPIRED
~Xt: ***
~Xt: Status Code: 1
~Xt: Status Name: ACTIVE
~Xt: Status Type: FULL TIME
~Xt: ***
~Xt: Status Code: 2
~Xt: Status Name: ACTIVE
~Xt: Status Type: PART TIME
~Xt: ***
SQL>
Oracle recommends that the cursors be closed, and the external
routines database environment be disconnected before the calling
session is disconnected. This can be achieved by using NOTIFY
routines.
For example, the external procedure that starts the transaction
could be modified as shown below to declare a NOTIFY routine (EX_
RUNDOWN) that when called would close the cursors, rollback the
transaction and disconnect from the database.
procedure EX_START_READ_TXN
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
notify EX_RUNDOWN on BIND
comment is 'start a READ ONLY transaction';
The BIND notification ensures that EX_RUNDOWN will be called
during the DISCONNECT of the caller and allow the transaction to
be rolled back and the session disconnected. ROLLBACK or COMMIT
will implicitly close any open cursors, unless the cursor were
defined as WITH HOLD. In this case it is important to also close
that cursor. Code similar to the following (in C) could implement
this rundown routine.
#include <string.h>
#include <stdio.h>
#define RDB$K_RTX_NOTIFY_ACTV_END 2
#define SQLSTATE_LEN 5
void sql_signal ();
void EX_CLOSE_CURSOR (char sqlstate [SQLSTATE_LEN]);
void EX_DISCONNECT (char sqlstate [SQLSTATE_LEN]);
void EX_ROLLBACK (char sqlstate [SQLSTATE_LEN]);
extern void EX_RUNDOWN
(int *func_code,
int *u1, /* U1, U2, U3 are currently unused */
int *u2, /* and are reserved for future use */
int *u3)
{
char sqlstate [SQLSTATE_LEN];
if (*func_code == RDB$K_RTX_NOTIFY_ACTV_END)
{
/* we are running down this external routine, so
* close the cursor
*/
EX_CLOSE_CURSOR (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0
&& memcmp ("24000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 24000 (bad cursor state)
*/
sql_signal ();
/* rollback the transaction
*/
EX_ROLLBACK (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0
&& memcmp ("25000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 25000 (bad transaction state)
*/
sql_signal ();
/* disconnect from the database
*/
EX_DISCONNECT (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 25000 (bad transaction state)
*/
sql_signal ();
}
}
The application can be compiled and built using this fragment of
DCL code:
$ create ex.opt
symbol_vector = (EX_START_READ_TXN = procedure)
symbol_vector = (EX_COMMIT = procedure)
symbol_vector = (EX_ROLLBACK = procedure)
symbol_vector = (EX_OPEN_CURSOR = procedure)
symbol_vector = (EX_CLOSE_CURSOR = procedure)
symbol_vector = (EX_FETCH_CURSOR = procedure)
symbol_vector = (EX_DISCONNECT = procedure)
symbol_vector = (EX_RUNDOWN = procedure)
psect_attr = RDB$MESSAGE_VECTOR,noshr
psect_attr = RDB$DBHANDLE,noshr
psect_attr = RDB$TRANSACTION_HANDLE,noshr
sql$user/library
$
$ cc EX_RUNDOWN
$ sql$mod EX
$ link/share EX,EX_RUNDOWN,EX/option