External Routines

Brought to you by the Oracle Rdb Server Technology Group
Ian Smith, Consulting Engineer, Oracle Corporation New England Development Center

I tend to reuse existing routines instead of writing my own, usually by writing the SQL interface to one of these routines.
Oracle Rdb has provided external routines (or 3GL language callouts) since release 6.0. The SQL language interface is based on the current SQL92 database language standard, and is described by the Persistent Stored Module component (Database Language SQL - Part 4: ISO/IEC 9075-4:1996).

To access such a routine, simply define the name and arguments to a routine written in any OpenVMS standard language (even SQL module language routines), using enough information to allow Rdb to pass in and to receive data from the routine. External functions allow only IN parameters, with a single returned value for the function. Typical examples include mathematics routines (SIN, COS, TAN, SQRT, etc), user written business functions, interprocess communication, or specialized logging.

Oracle Rdb7 added support for external procedures that accept both IN and OUT arguments. This allows access to a much large range of existing routines from within the SQL environment.

Technical Tips: Using external routines in Rdb7 to pass parameters to interactive SQL scripts

As a programmer, I have been using OpenVMS for nearly 20 years. As I am very familiar with the rich set of routines in the various runtime libraries provided with this operating system, I tend to reuse existing routines instead of writing my own. I do this by adding an external function or procedure to Oracle Rdb, which usually consists of writing just the SQL interface to one of these routines.

I have often been asked how to pass parameters to SQL script that would drive the queries inside. This month's tip shows how to do this using the OpenVMS runtime library (LIBRTL).

First, we create a set of domains to provide consistent data types:

create domain SYMBOL_DTYPE varchar(31);
create domain STRING_DTYPE varchar(50);

create procedure LIB$GET_SYMBOL
(in :symbol SYMBOL_DTYPE by descriptor,
out :resultant_string STRING_DTYPE by descriptor,
inout :resultant_length smallint by reference);
external location 'SYS$SHARE:LIBRTL.EXE'
language GENERAL
GENERAL parameter style
not variant
comment is 'Fetches string associated with DCL symbol. '
/ 'The resultant_length isn't used except to '
/ 'detect a failed call.';

create module GET_DCL_DATA
language SQL
comment is 'This module contains routine to process DCL symbols'

function GET_SYMBOL
(in :symbol_name SYMBOL_DTYPE)
comment 'Translate DCL symbol and return the text';
declare :sy_len smallint = 0;
declare :sy_string STRING_DTYPE = '';

-- translate the passed DCL symbol name
call LIB$GET_SYMBOL (:symbol_name, :sy_string, :sy_len);

if :sy_len = 0 then
-- if the length is zero then the translation failed
-- so signal the error
signal 'X0001';
return TRIM (:sy_string);
end if;
end module;

$ employee_id = "00164"
$ SQL$ @display_employee
$ exit

select last_name, first_name, employee_id
from employees
where employee_id = GET_SYMBOL ('EMPLOYEE_ID');