1 – bind-site-clause
Syntax options:
BIND ON CLIENT SITE | BIND ON SERVER SITE
Selects the execution model and environment for external routine
execution.
CLIENT site binding causes the external routine to be activated
and executed in the OpenVMS database client (application)
process. This is the default binding. This binding offers
the most efficient execution characteristics, allows sharing
resources such as I/O devices, and allows debugging of external
routines as if they were part of the client application. However,
this binding may suffer from address space limitations. Because
it shares virtual memory with the database buffers, this binding
is restricted to the client process system user environment, and
prohibits external routine execution in cases of an application
running with elevated privileges.
SERVER site binding causes the external routine to be activated
in a separate process from the database client and server. The
process is started on the same node at the database process.
This binding offers reasonable execution characteristics, a
larger address space, a true session user environment, and has
no restrictions regarding client process elevated privileges.
However, this binding does not permit sharing resources such
as I/O devices with the client (in particular, there is no
connection to the client interactive terminal), and debugging
of routines is generally not possible.
2 – bind-scope-clause
Syntax options:
BIND SCOPE CONNECT | BIND SCOPE TRANSACTION
Defines the scope during which an external routine is activated
and at what point the external routine is deactivated. The
default scope is CONNECT.
o CONNECT
An active routine is deactivated when you detach from the
database (or exit without detaching).
o TRANSACTION
An active routine is deactivated when a transaction is
terminated (COMMIT or ROLLBACK). In the event that a
transaction never occurs, the scope reverts to CONNECT.
3 – COMMENT_IS
A description about the nature of the parameter or external
routine. SQL displays the text of the comment when you execute
a SHOW FUNCTION or SHOW PROCEDURE statement. Enclose the comment
in single quotation marks (') and separate multiple lines in a
comment with a slash (/).
4 – 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.
5 – 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.
6 – external-body-clause
Identifies key characteristics of the routine: its name, where
the executable image of the routine is located, the language in
which the routine is coded, and so forth.
7 – external-body-name
The name of the external routine. If you do not specify a name,
SQL uses the name you specify in the external-routine-name
clause.
This name defines the routine entry address that is called for
each invocation of the routine body. The named routine must exist
in the external routine image selected by the location clause.
Unquoted names are converted to uppercase characters.
8 – external-location-clause
Syntax options:
DEFAULT LOCATION
LOCATION 'image-location'
A file specification referencing the image that contains the
routine body and optional notify entry points.
9 – external-routine-name
The name of the external routine. The name must be unique among
external and stored routines in the schema and can be qualified
with an alias or, in a multischema database, a schema name.
10 – FUNCTION
Creates an external function definition.
A function optionally accepts a list of IN parameters, always
returns a value, and is referenced by name as an element of a
value expression.
11 – GENERAL_PARAMETER_STYLE
This is synonymous with PARAMETER STYLE GENERAL and is
deprecated.
12 – language-name
The name of the host language in which the external routine
was coded. You can specify ADA, C, COBOL, FORTRAN, PASCAL, or
GENERAL. The GENERAL keyword allows you to call routines written
in any language.
13 – LANGUAGE_SQL
Names the language that calls the routine.
14 – mechanism-clause
Defines the passing mechanism. The following list describes the
passing mechanisms.
o BY DESCRIPTOR
Allows passing character data with any parameter access mode
to routines compiled by language compilers that implement the
OpenVMS calling standard.
o BY LENGTH
The LENGTH passing mechanism is the same as the DESCRIPTOR
passing mechanism.
o BY REFERENCE
Allows passing data with any parameter access mode as a
reference to the actual data.
This is the default passing mechanism for parameters. This
is also the default passing mechanism for a function value
returning character data.
o BY VALUE
Allows passing data with the IN parameter access mode to a
routine as a value and allows functions to return a value.
This is the default passing mechanism for a function value
returning noncharacter data.
15 – notify-clause
Specifies the name of a second external routine called (notified)
when certain external routine or database-related events occur.
This name defines the routine entry address that is called, for
each invocation of the notify routine. The named routine must
exist in the external routine image selected by the location
clause.
The events of interest to the notify routine are ON BIND, ON
CONNECT, and ON TRANSACTION. Multiple events can be specified.
The following describes the events and scope of each event:
BIND Routine activation to routine deactivation
CONNECT Database attach to database disconnect
TRANSACTION Start transaction to commit or roll back
transaction
16 – parameter-list
The optional parameters of the external routine. For each
parameter you can specify a parameter access mode (IN, OUT, and
INOUT), a parameter name, a data type, and a passing mechanism
(by DESCRIPTOR, LENGTH, REFERENCE, or VALUE).
The parameter access mode (IN, OUT, and INOUT) is optional and
specifies how the parameter is accessed (whether it is read,
written, or both). IN signifies read only, OUT signifies write
only, and INOUT signifies read and write. The parameter access
mode defaults to IN.
Only the IN parameter access mode may be specified with
parameters to an external function. Any of the parameter access
modes (IN, OUT, and INOUT) may be specified with parameters to an
external procedure.
The optional parameter name is prefixed with a colon (:).
The parameter name must be unique within the external routine
parameters.
The data type is required and describes the type of parameter
using either an SQL data type or a domain name.
You cannot declare a parameter as the LIST OF BYTE VARYING data
type.
17 – PARAMETER_STYLE_GENERAL
Passes arguments and returns values in a manner similar to the
OpenVMS convention for passing arguments and returning function
values.
18 – PROCEDURE
Creates an external procedure definition.
A procedure optionally accepts a list of IN, OUT, or INOUT
parameters, and is referenced by name in a CALL statement.
19 – RETURNS
Describes a function (returned) value. You can specify a data
type and a passing mechanism (BY DESCRIPTOR, LENGTH, REFERENCE,
or VALUE). The function value is, by definition, an OUT access
mode value.
The data type is required and describes the type of parameter
using either an SQL data type or a domain name.
You cannot declare a function value as the LIST OF BYTE VARYING
data type.
20 – STORED_NAME_IS
The name that Oracle Rdb uses to access the routine when defined
in a multischema database. The stored name allows you to access
multischema definitions using interfaces that do not recognize
multiple schemas in one database. You cannot specify a stored
name for a routine in a database that does not allow multiple
schemas. For more information about stored names, see Stored
Names.
21 – 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.
22 – 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.