Example 1: System provided integer absolute value routine
SQL> CREATE FUNCTION IABS (IN INTEGER BY REFERENCE)
cont> RETURNS INTEGER BY VALUE;
cont> EXTERNAL NAME MTH$JIABS
cont> LOCATION 'SYS$SHARE:DPML$SHR.EXE'
cont> LANGUAGE GENERAL
cont> PARAMETER STYLE GENERAL
cont> NOT DETERMINISTIC;
SQL> --
SQL> SELECT IABS(-33) FROM JOBS LIMIT TO 1 ROW;
33
1 row selected
Example 2: Using the NOT DETERMINISTIC clause, instead of the
DETERMINISTIC clause
The first CREATE FUNCTION statement in the following example
creates a function with the DETERMINISTIC clause. The
DETERMINISTIC clause indicates to Oracle Rdb that the function
would return the same result no matter how many times it is
called. Because the argument is a string literal (and could never
change), Oracle Rdb optimizes the entire function call so that it
is not called in subsequent select statements.
SQL> -- Create a function with a DETERMINISTIC clause.
SQL> CREATE function DO_COM (IN VARCHAR(255) BY DESCRIPTOR)
cont> RETURNS INTEGER;
cont> EXTERNAL NAME LIB$SPAWN
cont> LOCATION 'SYS$SHARE:LIBRTL.EXE'
cont> LANGUAGE GENERAL
cont> PARAMETER STYLE GENERAL
cont> DETERMINISTIC;
SQL> --
SQL> -- Use a SELECT statement to pass a string literal to the function.
SQL> --
SQL> -- Because Oracle Rdb optimizes functions with the DETERMINISTIC
SQL> -- clause, and the function is passed a string literal,
SQL> -- Oracle Rdb does not call the function from subsequent
SQL> -- statements.
SQL> --
SQL> SELECT DO_COM('WRITE SYS$OUTPUT "HELLO"'), employee_id FROM employees
cont> LIMIT TO 5 ROWS;
HELLO
DO_COM EMPLOYEE_ID
1 00164
1 00165
1 00166
1 00167
1 00168
5 rows selected
SQL> --
SQL> -- Use the NOT DETERMINISTIC clause to create the function:
SQL> --
SQL> CREATE function DO_COM (IN VARCHAR(255) BY DESCRIPTOR)
cont> RETURNS INTEGER;
cont> EXTERNAL NAME lib$SPAWN
cont> LOCATION 'SYS$SHARE:LIBRTL.EXE'
cont> LANGUAGE GENERAL
cont> PARAMETER STYLE GENERAL
cont> NOT DETERMINISTIC;
SQL> SELECT DO_COM('WRITE SYS$OUTPUT "HELLO"'), EMPLOYEE_ID FROM EMPLOYEES
cont> LIMIT TO 5 ROWS;
HELLO
HELLO
DO_COM EMPLOYEE_ID
1 00164
HELLO
1 00165
HELLO
1 00166
HELLO
1 00167
1 00168
5 rows selected
Example 3: External function and external procedure definition
The following example demonstrates:
o An external function and an external procedure
o Both CLIENT SITE and SERVER SITE binding
o BIND SCOPE
o A NOTIFY routine and events
o SQL callback using embedded SQL and SQL module language
o SQL$PRE options required when using callback
o Linker options required when using callback
In this example, a new column is added to the EMPLOYEES table in
the MF_PERSONNEL database. External routines are used to set this
column to spaces and to the SOUNDEX value corresponding to the
various employee names. Transaction control at the application
level (in this instance, in SQL) in conjunction with a notify
routine demonstrates how the actions of the external routines can
be affected by actions of the application.
The space-filling is performed by an external function, CLEAR_
SOUNDEX, (written in C) containing embedded SQL, which opens
another instance of the MF_PERSONNEL database and leaves it open
until deactivated.
The SOUNDEX name-setting is performed by an external procedure
(written in FORTRAN), assisted by a notify routine (written in
FORTRAN) which performs the database connection and transaction
control. All the database operations are performed by SQL module
language routines. The procedure also opens another instance
of the MF_PERSONNEL database, which is disconnected by the
notify routine when the routine is deactivated at the end of the
transaction. Display statements executed by the notify routine
serve to demonstrate the progress of the database operations.
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> -- Add the new column SOUNDEX_NAME to the EMPLOYEES table.
SQL> --
SQL> ALTER TABLE EMPLOYEES ADD SOUNDEX_NAME CHAR(4);
SQL> --
SQL> -- Define the CLEAR_SOUNDEX function.
SQL> --
SQL> CREATE FUNCTION CLEAR_SOUNDEX ()
cont> RETURNS INTEGER BY VALUE;
cont> EXTERNAL NAME CLEAR_SOUNDEX
cont> LOCATION 'CLEAR_SOUNDEX.EXE'
cont> LANGUAGE C PARAMETER STYLE GENERAL NOT DETERMINISTIC
cont> BIND ON SERVER SITE BIND SCOPE CONNECT;
SQL> --
SQL> -- Define the ADD_SOUNDEX_NAME procedure.
SQL> --
SQL> CREATE PROCEDURE ADD_SOUNDEX_NAME
cont> (INOUT INTEGER BY REFERENCE);
cont> EXTERNAL NAME ADD_SOUNDEX_NAME
cont> LOCATION 'ADD_SOUNDEX.EXE'
cont> LANGUAGE FORTRAN PARAMETER STYLE GENERAL
cont> BIND ON CLIENT SITE BIND SCOPE TRANSACTION
cont> NOTIFY ADD_SOUNDEX_NOTIFY ON BIND, TRANSACTION;
SQL> --
SQL> COMMIT;
SQL> DISCONNECT ALL;
SQL> EXIT;
Example 4: The CLEAR_SOUNDEX.SC program written in C
/* Set the soundex_name column to spaces, return any error as function value */
static int state = 0;
extern int clear_soundex () {
exec sql include sqlca ;
exec sql declare alias filename MF_PERSONNEL;
if (state == 0) {
exec sql attach 'filename MF_PERSONNEL';
state = 1;
}
exec sql set transaction read write;
if (SQLCA.SQLCODE < 0)
return SQLCA.SQLCODE;
exec sql update employees set soundex_name = ' ';
if (SQLCA.SQLCODE < 0)
return SQLCA.SQLCODE;
exec sql commit;
if (SQLCA.SQLCODE < 0)
return SQLCA.SQLCODE;
return 0;
}
Example 5: Compiling, creating a linker options file, and linking
the CLEAR_SOUNDEX program
$ SQL$PRE/CC/NOLIST/SQLOPT=ROLLBACK_ON_EXIT CLEAR_SOUNDEX.SC
$ CREATE CLEAR_SOUNDEX.OPT
SYMBOL_VECTOR = (CLEAR_SOUNDEX=PROCEDURE)
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
$ LINK/SHARE=CLEAR_SOUNDEX.EXE -
CLEAR_SOUNDEX.OBJ, SQL$USER:/LIBRARY, -
CLEAR_SOUNDEX.OPT/OPT
SYMBOL_VECTOR = (CLEAR_SOUNDEX=PROCEDURE)
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
Example 6: The ADD_SOUNDEX.FOR program written in FORTRAN
C Set the soundex values, returning any error in the IN/OUT parameter
SUBROUTINE ADD_SOUNDEX_NAME (ERROR)
CHARACTER ID*5,LAST*14,SX_NAME*4
INTEGER ERROR
ERROR = 0
ID = '00000'
10 CALL GET_NAME (ID, LAST, ERROR)
IF (ERROR .NE. 0) GO TO 80
CALL MAKE_SOUNDEX_NAME (LAST, SX_NAME)
CALL SET_SOUNDEX_NAME (ID, SX_NAME, ERROR)
IF (ERROR .EQ. 0) GO TO 10
80 IF (ERROR .EQ. 100) ERROR = 0
90 RETURN
END
C Perform database connection and transaction operations for notify events
SUBROUTINE ADD_SOUNDEX_NOTIFY (FUNC, RSV1, RSV2, RSV3)
INTEGER FUNC, RSV1, RSV2, RSV3, SQLCODE
SQLCODE = 0
GO TO (10,20,5,5,30,40,50),FUNC
5 TYPE *,'*** ADD_SOUNDEX_NOTIFY bad func ***'
GO TO 90
10 TYPE *,'*** ADD_SOUNDEX_NOTIFY activate ***'
CALL ATTACH_DB (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
20 TYPE *,'*** ADD_SOUNDEX_NOTIFY deactivate ***'
CALL DETACH_DB (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
30 TYPE *,'*** ADD_SOUNDEX_NOTIFY start tran ***'
CALL START_TRAN (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
40 TYPE *,'*** ADD_SOUNDEX_NOTIFY commit tran ***'
CALL COMMIT_TRAN (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
50 TYPE *,'*** ADD_SOUNDEX_NOTIFY rollback tran ***'
CALL ROLLBACK_TRAN (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
80 CALL SQL_SIGNAL ()
90 RETURN
END
C A 'substitute' SOUNDEX routine for demonstration purposes only
SUBROUTINE MAKE_SOUNDEX_NAME (NAME, SOUNDEX_NAME)
CHARACTER NAME*(*),SOUNDEX_NAME*4
SOUNDEX_NAME(1:1)=NAME(1:1)
IV = ICHAR(NAME(1:1))+22
SOUNDEX_NAME(2:2)=CHAR(MOD(IV,10)+48)
SOUNDEX_NAME(3:3)=CHAR(MOD(IV/10,10)+48)
SOUNDEX_NAME(4:4)=CHAR(IV/100+48)
RETURN
END
Example 7: The ADD_SOUNDEXM.SQLMOD module
-- Support for set soundex routine
MODULE ADD_SOUNDEX
LANGUAGE FORTRAN
PARAMETER COLONS
PROCEDURE ATTACH_DB (SQLCODE);
ATTACH 'FILENAME MF_PERSONNEL';
PROCEDURE DETACH_DB (SQLCODE);
DISCONNECT DEFAULT;
PROCEDURE START_TRAN (SQLCODE);
SET TRANSACTION READ WRITE;
PROCEDURE COMMIT_TRAN (SQLCODE);
COMMIT;
PROCEDURE ROLLBACK_TRAN (SQLCODE);
ROLLBACK;
PROCEDURE GET_NAME (:ID CHAR(5), :LASTNAME CHAR(14), SQLCODE);
SELECT EMPLOYEE_ID, LAST_NAME INTO :ID, :LASTNAME
FROM EMPLOYEES WHERE EMPLOYEE_ID > :ID LIMIT TO 1 ROW;
PROCEDURE SET_SOUNDEX_NAME (:ID CHAR(5), :SX_NAME CHAR(4), SQLCODE);
UPDATE EMPLOYEES SET SOUNDEX_NAME = :SX_NAME WHERE EMPLOYEE_ID = :ID;
Example 8: Compiling, creating the linker options file, and
linking the FORTRAN and SQL module language programs
$ FORTRAN/NOLIST ADD_SOUNDEX.FOR
$ SQL$MOD ADD_SOUNDEXM.SQLMOD
$ CREATE ADD_SOUNDEX.OPT
SYMBOL_VECTOR = (ADD_SOUNDEX_NAME=PROCEDURE)
SYMBOL_VECTOR = (ADD_SOUNDEX_NOTIFY=PROCEDURE)
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
$ LINK/SHARE=ADD_SOUNDEX.EXE -
ADD_SOUNDEX.OBJ, ADD_SOUNDEXM.OBJ, SQL$USER:/LIBRARY, -
ADD_SOUNDEX.OPT/OPT
SYMBOL_VECTOR = ADD_SOUNDEX_NAME
SYMBOL_VECTOR = ADD_SOUNDEX_NOTIFY
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
Example 9: Using the routines with interactive SQL
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> DECLARE :ERROR INTEGER;
SQL> --
SQL> SELECT EMPLOYEE_ID,SOUNDEX_NAME FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
EMPLOYEE_ID SOUNDEX_NAME
00165 NULL
00190 NULL
00187 NULL
3 rows selected
SQL> COMMIT;
SQL> --
SQL> BEGIN
cont> SET :ERROR = CLEAR_SOUNDEX ();
cont> END;
SQL> PRINT :ERROR;
ERROR
0
SQL> --
SQL> SELECT EMPLOYEE_ID,SOUNDEX_NAME FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
EMPLOYEE_ID SOUNDEX_NAME
00165
00190
00187
3 rows selected
SQL> COMMIT;
SQL> --
SQL> SET TRANSACTION READ ONLY;
SQl> BEGIN
cont> SET :ERROR = 0;
cont> CALL ADD_SOUNDEX_NAME (:ERROR);
cont> END;
*** ADD_SOUNDEX_NOTIFY activate ***
*** ADD_SOUNDEX_NOTIFY start tran ***
SQL> PRINT :ERROR;
ERROR
0
SQL> COMMIT;
*** ADD_SOUNDEX_NOTIFY commit tran ***
*** ADD_SOUNDEX_NOTIFY deactivate ***
SQL> --
SQL> SELECT EMPLOYEE_ID,SOUNDEX_NAME FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
EMPLOYEE_ID SOUNDEX_NAME
00165 S501
00190 O101
00187 L890
3 rows selected
SQL> COMMIT;
SQL> --
SQL> BEGIN
cont> SET :ERROR = CLEAR_SOUNDEX ();
cont> END;
SQL> PRINT :ERROR;
ERROR
0
SQL> --
SQL> SET TRANSACTION READ ONLY;
SQL> BEGIN
cont> SET :ERROR = 0;
cont> CALL ADD_SOUNDEX_NAME (:ERROR);
cont> END;
*** ADD_SOUNDEX_NOTIFY activate ***
*** ADD_SOUNDEX_NOTIFY start tran ***
SQL> PRINT :ERROR;
ERROR
0
SQL> ROLLBACK;
*** ADD_SOUNDEX_NOTIFY rollback tran ***
*** ADD_SOUNDEX_NOTIFY deactivate ***
SQL> --
SQL> SELECT EMPLOYEE_ID,SOUNDEX_NAME FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
EMPLOYEE_ID SOUNDEX_NAME
00165
00190
00187
3 rows selected
SQL> COMMIT;