CREATE PROCEDURE (external procedures)

Syntax

CREATE [ OR REPLACE ] PROCEDURE procedure_name
 ( [ parameter_name
 { parameter_type | ARRAY OF parameter_type 
    | ROW OF database_name.table_name },... ] )
 EXECUTABLE 'executable_name'
 HOST 'host_name'
 USER user_id
 GROUP group_id
 [ ARGUMENTS expression,... ] [;]

If there is a possibility that a procedure already exists with the same name as the one you want to create, use the optional OR REPLACE keywords. If the procedure exists, it is replaced by the one you are creating. If the procedure does not exist, a new one is created.

The procedure_name must be unique within the ObjectServer and comply with the ObjectServer naming conventions.

After the procedure_name, include the parameter declaration within parentheses ( ), to specify the parameters that can be passed into the external procedure. You must include parentheses after the procedure_name even if the procedure has no parameters. Each parameter_name must be unique within the procedure and must comply with the ObjectServer naming conventions.

The parameter_type defines the type of data that the parameter can pass into the procedure. The data type can be any valid ObjectServer data type, except VARCHAR or INCR.

The executable_name is the path to an executable program on a local or remote file system.

The host_name is the name of the host on which to run the executable program for the procedure.

The user_id is the effective user ID under which to run the executable program.

The group_id is the effective group ID under which to run the executable program.

The arguments are those passed to the executable. Only spaces can be used to separate arguments. For example: cool tool is interpreted as cool tool, whereas cool'tool or cool"tool is interpreted as cooltool.

Example

The following external procedure calls a program called nco_mail, which sends e-mail about unacknowledged critical alerts:

create or replace procedure send_email
 (in node character(255), in severity integer, in subject character(255),
 in email character(255), in summary character(255), in hostname character(255))
 executable '$NCHOME/omnibus/utils/nco_mail'
 host 'localhost'
 user 0
 group 0
 arguments '\'' + node + '\'', severity, '\'' + subject + '\'',
  '\'' + email + '\'', '\"\''+summary+'\'\"';

This example also shows how to pass text strings to an executable program. You must enclose strings in quotation marks, and escape the quotation marks with backslashes. All quotation marks in this example are single quotation marks.