Allows attributes to be changed for a function that was created
using the CREATE MODULE statement or the CREATE FUNCTION
statement.
It can be used to:
o Force a stored (SQL) function to be compiled (COMPILE option)
o Modify attributes of external functions
o Change the comment on a function
1 – Environment
You can use the ALTER FUNCTION statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
ALTER FUNCTION <function-name> ++-> COMMENT IS +-> '<string>' -------+++-+->
|| +------- / <----------+|| |
|+-> COMPILE --------------------------+| |
|+-> NAME <external-body-name> ---------+ |
|+-> external-location-clause ----------+ |
|+-> LANGUAGE language-name ------------+ |
|+-> notify-clause ---------------------+ |
|+-> RETURNS NULL ON NULL INPUT --------+ |
|+-> CALLED ON NULL INPUT --------------+ |
|+-> bind-site-clause ------------------+ |
|+-> bind-scope-clause -----------------+ |
|+-+--------+--+-------------------+----+ |
|| +-> NOT -+ +-> VARIANT --------+ | |
|| +-> DETERMINISTIC --+ | |
|+> RENAME TO <new-function-name> ------+ |
+--------------<--------------------------+
external-location-clause =
---+--> DEFAULT LOCATION ------------+-------------------------+
+--> LOCATION '<image-location>' -+ |
+-------------------------------<-----------------------------+
+-+----------------------------->--------------------------+-->
+--> WITH --+-> ALL -----+--> LOGICAL_NAME TRANSLATION --+
+-> SYSTEM --+
notify-clause =
-> NOTIFY notify-entry-name --> ON -+-+-> BIND ---------+-+->
| +-> CONNECT ------+ |
| +-> TRANSACTION --+ |
+--------- , <--------+
bind-site-clause =
--> BIND ON --+--> CLIENT --+--> SITE --->
+--> SERVER --+
bind-scope-clause =
---> BIND SCOPE --+-> CONNECT ------+-->
+-> TRANSACTION --+
3 – Arguments
3.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.
3.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.3 – COMMENT IS string
Adds a comment about the function. SQL displays the text of the
comment when it executes a SHOW FUNCTIONS statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
This clause is equivalent to the COMMENT ON FUNCTION statement.
3.4 – COMPILE
The COMPILE option forces the Oracle Rdb server to recompile the
stored (SQL) function. External functions are not affected.
Use COMPILE when a function has been made invalid by the
execution of a DROP . . . CASCADE operation. This mechanism is
preferred over the SET FLAGS 'VALIDATE_ROUTINE' method available
in previous versions.
3.5 – DETERMINISTIC
Syntax options:
DETERMINISTIC | NOT DETERMINISTIC
These clauses are synonyms for the VARIANT and NOT VARIANT
clauses for conformance to the SQL/PSM standard.
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.
3.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.
3.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.
3.8 – external-location-clause
Syntax options:
DEFAULT LOCATION
LOCATION 'image-location'
A default or specific location for the external routine image.
The resulting file specification must include the type .exe.
This can be an image file specification or merely a logical name.
SQL selects a routine based on a combination of factors:
o Image string
The location defaults to DEFAULT LOCATION, which represents
the file specification string RDB$ROUTINES.
o Logical name translation
The WITH ALL LOGICAL_NAME TRANSLATION and the WITH SYSTEM
LOGICAL_NAME TRANSLATION clauses specify how logical names in
the location string are to be translated.
If no translation option is specified, or if WITH ALL LOGICAL_
NAME TRANSLATION is specified, logical names are translated in
the default manner.
If WITH SYSTEM LOGICAL_NAME TRANSLATION is specified, any
logical names in the location string are expanded using only
EXECUTIVE_MODE logical names from the SYSTEM logical name
table.
3.9 – LANGUAGE 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.
3.10 – 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
3.11 – RENAME_TO
Changes the name of the function being altered. See the RENAME
for further discussion. If the new name is the name of a synonym
then an error will be raised.
3.12 – ON_NULL_INPUT
Syntax options:
RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
These clauses control how an external function is invoked when
one or more of the function arguments is NULL. The CALLED ON
NULL INPUT clause specifies that the function should be executed
normally. A normal execution when the PARAMETER STYLE GENERAL
clause is specified means that SQL should return a run-time error
when the NULL value is detected.
The RETURNS NULL ON NULL INPUT clause instructs Oracle Rdb to
avoid the function call and just return a NULL result. This
option is valuable for library functions such as SIN, COS,
CHECKSUM, SOUNDEX, and so on, that usually return an UNKNOWN
result if an argument is NULL.
The CALLED ON NULL INPUT clause is the default.
3.13 – VARIANT
Syntax options:
VARIANT | NOT VARIANT
These clauses are synonyms for the DETERMINISTIC and NOT
DETERMINISTIC clauses for conformance to the SQL/PSM standard.
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.
4 – Examples
Example 1: Changing a function to be NOT DETERMINISTIC
When a function is created it is assumed to be DETERMINISTIC.
That is, given the same input values it should return the same
result. When a routine has no parameters, such as the GET_TIME
function shown below, then there is never any variation in the
input. In this case the function should have been defined as NOT
DETERMINISTIC to ensure that the Rdb optimizer calls it for each
row processed, instead of using the previously returned result
for each row.
Although DROP FUNCTION and CREATE FUNCTION could have performed
the same function, ALTER FUNCTION preserves the dependencies that
exist in the database.
SQL> alter function GET_TIME
cont> not deterministic
cont> comment 'Fetch time from clock'
cont> / 'Every call must be executed, so change to be'
cont> / 'NOT DETERMINISTIC';
SQL>
SQL> show function GET_TIME;
Information for function GET_TIME
Function is Not Deterministic (variant)
Function ID is: 262
External Location is: SYS$SHARE:CLOCKSHR.EXE
Entry Point is: GET_TIME
Comment: Fetch time from clock
Every call must be executed, so change to be
NOT DETERMINISTIC
Language is: COBOL
GENERAL parameter passing style used
Number of parameters is: 0
Parameter Name Data Type Domain or Type
-------------- --------- --------------
TIME(2)
Function result datatype
Return value is passed by value