Allows attributes to be changed for a procedure that was created
using the CREATE MODULE statement or the CREATE PROCEDURE
statement.
It can be used to:
o Force a stored (SQL) procedure to be compiled (COMPILE option)
o Modify attributes of an external procedure
o Change the comment on a procedure
1 – Environment
You can use the ALTER PROCEDURE 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 PROCEDURE <proc-name> ---++-> COMMENT IS +-> '<string>' -------+++->
|| +------- / <----------+||
|+-> COMPILE --------------------------+|
|+-> NAME <external-body-name> --------+|
|+-> external-location-clause ---------+|
|+-> LANGUAGE language-name -----------+|
|+-> notify-clause --------------------+|
|+-> bind-site-clause -----------------+|
|+-> bind-scope-clause ----------------+|
|+-> RENAME TO <new-procedure-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 procedure. SQL displays the text of the
comment when it executes a SHOW PROCEDURES 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 PROCEDURE statement.
3.4 – COMPILE
The COMPILE option forces the Oracle Rdb server to recompile the
stored (SQL) procedure. External procedures are not affected.
Use COMPILE when a procedure 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 – 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.6 – 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.7 – 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.8 – 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.9 – 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.10 – RENAME_TO
Changes the name of the procedure being altered. See the RENAME
for further discussion. If the new name is the name of a synonym
then an error will be raised.
4 – Examples
Example 1: Using ALTER PROCEDURE to target a new routine and
sharable image
This example shows ALTER PROCEDURE updating the location, routine
name and language for an external procedure.
SQL> show procedure SEND_MAIL
Information for procedure SEND_MAIL
Procedure ID is: 261
External Location is: SYS$SHARE:SENDMAILSHR.EXE
Entry Point is: SEND_MAIL
Language is: COBOL
GENERAL parameter passing style used
Number of parameters is: 2
Parameter Name Data Type Domain or Type
-------------- --------- --------------
USR CHAR(30)
Parameter position is 1
Parameter is IN (read)
Parameter is passed by reference
TXT VARCHAR(1000)
Parameter position is 2
Parameter is IN (read)
Parameter is passed by reference
SQL> /*
***> The routine has been rewritten. Use ALTER PROCEDURE
***> to retarget the external routine to use the new
***> implementation, instead of using DROP/CREATE
***> */
SQL>
SQL> set quoting rules 'SQL99';
SQL>
SQL> alter procedure SEND_MAIL
cont> name "send_mail_ext"
cont> location 'SYS$SHARE:SENDMAILSHR30.EXE'
cont> language C
cont> comment 'Use new V3.0 interface routine';
SQL>