Release 8.0
A54654_01
 Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference Release 8.0 A54654_01 |
|
Well, if I called the wrong number, why did you answer the phone?
James Thurber
PL/SQL is a powerful development tool; you can use it for almost any purpose. But, it is specialized for SQL transaction processing. So, some tasks are more quickly or easily done in a lower-level language such as C, which is more efficient at machine-precision calculations. For example, a Fast Fourier Transform (FFT) routine written in C runs faster than one written in PL/SQL.
To support such special-purpose processing, PL/SQL provides an interface for calling routines written in other languages. This makes the strengths and capabilities of those languages available to you. No longer are you restricted to one language with its inherent limitations.
What Is an External Procedure?
Registering an External Procedure
Calling an External Procedure
How PL/SQL Calls an External Procedure
Passing Parameters to an External Procedure
Using Service Routines
Doing Callbacks
Debugging External Procedures
An external procedure is a third-generation-language routine stored in a dynamic link library (DLL), registered with PL/SQL, and called by you to do special-purpose processing. At run time, PL/SQL loads the library dynamically, then calls the routine as if it were a PL/SQL subprogram. To safeguard your database, the routine runs in a separate address space.
External procedures promote reusability, efficiency, and modularity. DLLs already written and available in other languages can be called from PL/SQL programs. The DLLs are loaded only when needed, so memory is conserved. Moreover, the DLLs can be enhanced without affecting the calling programs.
Typically, external procedures are used to interface with embedded systems, solve scientific and engineering problems, analyze data, or control real-time devices and processes. For example, you might use external procedures to send instructions to a robot, solve partial differential equations, process signals, analyze time series, or create animation on a video display.
Note: This feature is available on platforms that support DLLs (or shared libraries such as Solaris .so libraries, which load dynamically). Currently, any routine callable from C code is supported.
A DLL is an operating-system file that stores external procedures. For safety, your DBA controls access to the DLL. Using the CREATE LIBRARY statement, the DBA creates a schema object called an alias library, which represents the DLL. Then, if you are an authorized user, the DBA grants you EXECUTE privileges on the alias library. After registering an external procedure, you can call it from any PL/SQL program. It executes with the privileges granted to your userid.
If the DBA grants you CREATE ANY LIBRARY privileges, you can create your own alias libraries using the following syntax:
CREATE LIBRARY library_name {IS | AS} 'file_path';
You must specify the full path to the DLL because the linker cannot resolve references to just the DLL name. In the following example, you create alias library c_utils, which represents DLL utils.so:
create library c_utils as '/DLLs/utils.so';
Before you can call an external procedure, you must register it. That is, you must tell PL/SQL where to find the procedure, how to call it, and what to pass it. You do that by writing a special kind of PL/SQL standalone or packaged subprogram, which acts like a proxy for the external procedure. (By default, they have the same name.)
You write the PL/SQL stored subprogram in the usual way except that, in its body, instead of declarations and a BEGIN ... END block, you code the EXTERNAL clause. This clause records information about the external procedure such as its location, its name, the programming language in which it was written, and the calling standard under which it was compiled. The syntax follows:
EXTERNAL LIBRARY library_name
[NAME external_procedure_name]
[LANGUAGE language_name]
[CALLING STANDARD {C | PASCAL}]
[WITH CONTEXT]
[PARAMETERS (external_parameter[, external_prameter]...)];
where external_parameter stands for
{ CONTEXT
| {parameter_name | RETURN} [property] [BY_REF] [external_datatype]}
and property stands for
{INDICATOR | LENGTH | MAXLEN | CHARSETID | CHARSETFORM}
The EXTERNAL clause is the interface between PL/SQL and an external procedure. The following subclauses tell PL/SQL where to find the procedure, how to call it, and what to pass it. (Only the LIBRARY subclause is required.)
Specifies a local alias library. (You cannot use a database link to specify a remote library.) The library name is a PL/SQL identifier. So, if you enclose the name in double quotes, it becomes case sensitive. (By default, the name is stored in upper case.) You must have EXECUTE privileges on the alias library.
Specifies the external procedure to be called. If you enclose the procedure name in double quotes, it becomes case sensitive. (By default, the name is stored in upper case.) If you omit this subclause, the procedure name defaults to the upper-case name of the PL/SQL subprogram.
Specifies the third-generation language in which the external procedure was written. Currently, only the language name C is allowed. If you omit this subclause, the language name defaults to C.
Specifies the Windows NT calling standard (C or Pascal) under which the external procedure was compiled. (Under the Pascal Calling Standard, arguments are reversed on the stack and the called function must pop the stack.) If you omit this subclause, the calling standard defaults to C.
Specifies that a context pointer will be passed to the external procedure. The context data structure is opaque to the external procedure but is available to service routines called by the external procedure. For more information, see "Using the WITH CONTEXT Clause" on page 10-14.
Specifies the positions and datatypes of parameters passed to the external procedure. It can also specify parameter properties such as current length and maximum length, and the preferred parameter passing method (by value or by reference). For more information, see "Using the PARAMETERS Clause" on page 10-10.
Assume that C routine c_gcd, which finds the greatest common divisor of two numbers, is stored in DLL utils.so and that you have EXECUTE privileges on alias library c_utils. The C prototype for c_gcd follows:
int c_gcd(int x_val, int y_val);
In the following example, you write a PL/SQL standalone function named gcd that registers C routine c_gcd as an external function:
CREATE FUNCTION gcd (
-- find greatest common divisor of x and y
x BINARY_INTEGER,
y BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY c_utils
NAME "c_gcd" -- quotes preserve lower case
LANGUAGE C;
You do not call an external procedure directly. Instead, you call the PL/SQL subprogram that registered the external procedure. Such calls, which you code in the usual way, can appear in
Caution: To call a packaged function from SQL statements, you must use the pragma RESTRICT_REFERENCES, which asserts the purity level of the function (the extent to which the function is free of side effects). PL/SQL cannot check the purity level of the corresponding external routine. So, make sure the routine does not violate the pragma. Otherwise, you might get unexpected results. For more information about the pragma, see Oracle8 Server Application Developer's Guide.
Any PL/SQL block or subprogram executing on the server side or on the client side (for example, in a Developer/2000 tool such as Oracle Forms) can call an external procedure. The only requirement is that you call the external procedure from C code.
On the server side, the external procedure runs in a separate process address space, which safeguards your database. Figure 10-1 shows how Oracle8 and an external procedure interact.
In the last example, you wrote PL/SQL function gcd, which registered external procedure c_gcd, as follows:
CREATE FUNCTION gcd (
-- find greatest common divisor of x and y
x BINARY_INTEGER,
y BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY c_utils
NAME "c_gcd" -- quotes preserve lower case
LANGUAGE C;
In the example below, you call PL/SQL function gcd from an anonymous block. PL/SQL passes the two integer parameters to external function c_gcd, which returns their greatest common divisor.
DECLARE
g BINARY_INTEGER;
a BINARY_INTEGER;
b BINARY_INTEGER;
...
BEGIN
...
g := gcd(a, b); -- call function
IF g IN (2,4,8) THEN ...
To call an external procedure, PL/SQL must know in which DLL it resides. So, PL/SQL looks up the alias library in the EXTERNAL clause of the subprogram that registered the external procedure, then has Oracle look up the DLL in the data dictionary.
Next, PL/SQL alerts a Listener process, which in turn spawns (launches) a session-specific agent named extproc. Then, the Listener hands over the connection to extproc. PL/SQL passes to extproc the name of the DLL, the name of the external procedure, and any parameters.
Then, extproc loads the DLL and runs the external procedure. Also, extproc handles service calls (such as raising an exception) and callbacks to the Oracle server. Finally, extproc passes to PL/SQL any values returned by the external procedure. Figure 10-2 shows the flow of control.
Note: The Listener must start extproc on the machine that runs the Oracle Server. Starting extproc on a different machine is not supported.
After the external procedure completes, extproc remains active throughout your Oracle session. (When you log off, extproc is killed.) So, you incur the cost of spawning extproc only once no matter how many calls you make. Still, you should call an external procedure only when the computational benefits outweigh the cost.
For information about administering extproc and external procedure calls, see Oracle8 Server Administrator's Guide.
Passing parameters to an external procedure is complicated by several circumstances:
In the following sections, you learn how to specify a parameter list that deals with these circumstances.
You do not pass parameters to an external procedure directly. Instead, you pass them to the PL/SQL subprogram that registered the external procedure. So, you must specify PL/SQL datatypes for the parameters. For guidance, see Table 10-1. Each PL/SQL datatype maps to a default external datatype. (In turn, each external datatype maps to a C datatype.)
In some cases, you can use the PARAMETERS clause to override the default datatype mappings. For example, you can re-map the PL/SQL datatype BOOLEAN from external datatype INT to external datatype CHAR.
To avoid errors when declaring C prototype parameters, refer to Table 10-2, which shows the C datatype to specify for a given external datatype and PL/SQL parameter mode. For example, if the external datatype of an OUT parameter is STRING, specify the datatype char * in your C prototype.
Generally, the PL/SQL subprogram that registers an external procedure declares a list of formal parameters, as the following example shows:
CREATE FUNCTION interp (
-- find the value of y at x degrees using Lagrange interpolation
x IN FLOAT,
y IN FLOAT)
RETURN FLOAT AS EXTERNAL
NAME "interp"
LIBRARY mathlib
LANGUAGE C;
Each formal parameter declaration specifies a name, parameter mode, and PL/SQL datatype (which maps to the default external datatype). That might be all the information the external procedure needs. If not, you can provide more information using the PARAMETERS clause, which lets you specify
For every formal parameter, there must be a corresponding parameter in the PARAMETERS clause. If you include the WITH CONTEXT clause, you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list. Also, if the external routine is a function, you must specify the parameter RETURN in the last position.
You can also use the PARAMETERS clause to pass additional information about PL/SQL formal parameters and function results to an external procedure. You do that by specifying the following properties:
INDICATOR
LENGTH
MAXLEN
CHARSETID
CHARSETFORM
Table 10-3 shows the external datatypes, PL/SQL datatypes, and PL/SQL parameter modes allowed for a given property. Notice that MAXLEN cannot be applied to an IN parameter.
In the following example, we use the PARAMETERS clause to specify properties for the PL/SQL formal parameters and function result:
CREATE FUNCTION parse (
x IN BINARY_INTEGER,
Y IN OUT CHAR)
RETURN CHAR AS EXTERNAL
LIBRARY c_utils
NAME "c_parse"
LANGUAGE C
CALLING STANDARD PASCAL
PARAMETERS (
x, -- stores value of x
x INDICATOR, -- stores null status of x
y, -- stores value of y
y LENGTH, -- stores current length of y
y MAXLEN, -- stores maximum length of y
RETURN INDICATOR,
RETURN);
With this PARAMETERS clause, the C prototype becomes
char * c_parse(int x, short x_ind, char *y, int *y_len,
int *y_maxlen, short *retind);
The additional parameters in the C prototype correspond to the INDICATOR, LENGTH, and MAXLEN parameters in the PARAMETERS clause. The parameter RETURN corresponds to the C function identifier, which stores the result value.
An indicator is a parameter whose value "indicates" whether or not another parameter is null. PL/SQL does not need indicators because the RDBMS concept of nullity is built into the language. However, an external procedure might need to know if a parameter or function result is null. Also, an external procedure might need to signal the server that a returned "value" is actually a null, and should be treated accordingly.
In such cases, you can use the property INDICATOR to associate an indicator with a formal parameter. If the PL/SQL subprogram is a function, you can also associate an indicator with the function result.
To check the value of an indicator, you can use the constants OCI_IND_NULL and OCI_IND_NOTNULL. If the indicator equals OCI_IND_NULL, the associated parameter or function result is null. If the indicator equals OCI_IND_NOTNULL, the parameter or function result is not null.
Suggestion: Associate an indicator with every formal parameter. Otherwise, PL/SQL assumes that the parameters cannot return nulls.
In PL/SQL, there is no standard way to indicate the length of a raw or string parameter. However, in many cases, you want to pass the length of a parameter to and from an external procedure. Using the properties LENGTH and MAXLEN, you can specify parameters that store the current length and maximum length of a formal parameter.
Note: With parameters of type CHAR, LONG RAW, RAW, or VARCHAR2, you must use the property LENGTH.
With CHAR, CLOB, and VARCHAR2 parameters, you can use the properties CHARSETID and CHARSETFORM to pass a character set ID and form to an external procedure. The OCI attribute names for these properties are OCI_ATTR_CHARSET_ID and OCI_ATTR_CHARSET_FORM.
Remember, each formal parameter must have a corresponding parameter in the PARAMETERS clause. Their positions can differ because PL/SQL associates them by name, not by position. However, the PARAMETERS clause and the C prototype for the external procedure must have the same number of parameters in the same order.
In C, you can pass IN scalar parameters by value (the value of the parameter is passed) or by reference (a pointer to the value is passed). When an external procedure expects a pointer to a scalar, specify the BY REF phrase to pass the parameter by reference. An example follows:
CREATE PROCEDURE find_root (
x IN REAL, ...)
AS EXTERNAL
LIBRARY c_utils
NAME "c_find_root"
PARAMETERS (
x BY REF, ...);
In this case, the C prototype would be
void c_find_root(float *x, ...);
rather than the default
void c_find_root(float x, ...);
By including the WITH CONTEXT clause, you can give an external procedure access to information about parameters, exceptions, memory allocation, and the user environment. The WITH CONTEXT clause specifies that a context pointer will be passed to the external procedure. For example, if you write the following PL/SQL function
CREATE FUNCTION get_num (
x IN REAL)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY c_utils
NAME "c_get_num"
LANGUAGE C
WITH CONTEXT
PARAMETERS (
CONTEXT,
x BY REF,
RETURN INDICATOR);
then the C prototype would be
int c_get_num(
OCIExtProcContext *with_context,
float *x,
short *retind);
The context data structure is opaque to the external procedure but is available to service routines called by the external procedure.
If you also include the PARAMETERS clause, you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list. If you omit the PARAMETERS clause, the context pointer is the first parameter passed to the external procedure.
When called from an external procedure, a service routine can raise exceptions, allocate memory, and get OCI (Oracle Call Interface) handles for callbacks to the server. To use the functions, you must specify the WITH CONTEXT clause, which lets you pass a context structure to the external procedure. The context structure is declared in header file ociextp.h as follows:
typedef struct OCIExtProcContext OCIExtProcContext;
Now, let us see how service routines use the context information.
This service routine allocates n bytes of memory for the duration of the external procedure call. Any memory allocated by the function is freed as soon as control returns to PL/SQL.
Note: Do not use any other function to allocate or free memory.
The C prototype for this function follows:
dvoid *OCIExtProcAllocCallMemory(
OCIExtProcContext *with_context,
size_t amount);
The parameters with_context and amount are the context pointer and number of bytes to allocate, respectively. The function returns an untyped pointer to the allocated memory. A return value of zero indicates failure.
In SQL*Plus, suppose you register external function concat, as follows:
SQL> CREATE FUNCTION concat (
2 str1 IN VARCHAR2,
3 str2 IN VARCHAR2)
4 RETURN VARCHAR2 AS EXTERNAL
5 NAME "concat"
6 LIBRARY stringlib
7 WITH CONTEXT
8 PARAMETERS (
9 CONTEXT,
10 str1 STRING,
11 str1 INDICATOR short,
12 str2 STRING,
13 str2 INDICATOR short,
14 RETURN INDICATOR short,
15 RETURN LENGTH short,
16 RETURN STRING);
When called, concat concatenates two strings, then returns the result. If either string is null, the result is also null. As the following example shows, concat uses OCIExtProcAllocCallMemory to allocate memory for the result string:
char *concat(ctx, str1, str1_i, str2, str2_i, ret_i, ret_l)
OCIExtProcContext *ctx;
char *str1;
short str1_i;
char *str2;
short str2_i;
short *ret_i;
short *ret_l;
{
char *tmp;
short len;
/* Check for null inputs. */
if ((str1_i == OCI_IND_NULL) || (str2_i == OCI_IND_NULL))
{
*ret_i = (short)OCI_IND_NULL;
/* PL/SQL has no notion of a null ptr, so
return a zero-byte string. */
tmp = OCIExtProcAllocCallMemory(ctx, 1);
tmp[0] = '\0';
return(tmp);
}
/* Allocate memory for result string, including null terminator. */
len = strlen(str1) + strlen(str2);
tmp = OCIExtProcAllocCallMemory(ctx, len + 1);
strcpy(tmp, str1);
strcat(tmp, str2);
/* Set null indicator and length. */
*ret_i = (short)OCI_IND_NOTNULL;
*ret_l = len;
/* Return pointer, which PL/SQL frees later. */
return(tmp);
}
This service routine raises a predefined exception, which must have a valid Oracle error number in the range 1 .. 32767. After doing any necessary cleanup, the external procedure must return immediately. (No values are assigned to OUT or IN OUT parameters.) The C prototype for this function follows:
int OCIExtProcRaiseExcp(
OCIExtProcContext *with_context,
size_t error_number);
The parameters with_context and error_number are the context pointer and Oracle error number. The return values OCIEXTPROC_SUCCESS and OCIEXTPROC_ERROR indicate success or failure.
In SQL*Plus, suppose you register external procedure divide, as follows:
SQL> CREATE PROCEDURE divide (
2 dividend IN BINARY_INTEGER,
3 divisor IN BINARY_INTEGER,
4 result OUT FLOAT)
5 AS EXTERNAL
6 NAME "divide"
7 LIBRARY mathlib
8 WITH CONTEXT
9 PARAMETERS (
10 CONTEXT,
11 dividend int,
12 divisor int,
13 result float);
When called, divide finds the quotient of two numbers. As the following example shows, if the divisor is zero, divide uses OCIExtProcRaiseExcp to raise the predefined exception ZERO_DIVIDE:
void divide (ctx, dividend, divisor, result)
OCIExtProcContext *ctx;
int dividend;
int divisor;
float *result;
{
/* Check for zero divisor. */
if (divisor == (int)0)
{
/* Raise exception ZERO_DIVIDE, which is Oracle error 1476. */
if (OCIExtProcRaiseExcp(ctx, (int)1476) == OCIEXTPROC_SUCCESS)
{
return;
}
else
{
/* Incorrect parameters were passed. */
assert(0);
}
}
*result = (float)dividend / (float)divisor;
}
This service routine raises a user-defined exception and returns a user-defined error message. The C prototype for this function follows:
int OCIExtProcRaiseExcpWithMsg(
OCIExtProcContext *with_context,
size_t error_number,
text *error_message,
size_t len);
The parameters with_context, error_number, and error_message are the context pointer, Oracle error number, and error message text. The parameter len stores the length of the error message. If the message is a null-terminated string, len is zero. The return values OCIEXTPROC_SUCCESS and OCIEXTPROC_ERROR indicate success or failure.
In the previous example, you registered external procedure divide, as follows:
SQL> CREATE PROCEDURE divide (
2 dividend IN BINARY_INTEGER,
3 divisor IN BINARY_INTEGER,
4 result OUT FLOAT)
5 AS EXTERNAL
6 NAME "divide"
7 LIBRARY mathlib
8 WITH CONTEXT
9 PARAMETERS (
10 CONTEXT,
11 dividend int,
12 divisor int,
13 result float);
In the example below, you use a different version of divide. With this version, if the divisor is zero, divide uses OCIExtProcRaiseExcpWithMsg to raise a user-defined exception:
void divide (ctx, dividend, divisor, result)
OCIExtProcContext *ctx;
int dividend;
int divisor;
float *result;
/* Check for zero divisor. */
if (divisor == (int)0)
{
/* Raise a user-defined exception, which is Oracle error 20100,
and return a null-terminated error message. */
if (OCIExtProcRaiseExcpWithMsg(ctx, (int)20100,
"divisor is zero", 0) == OCIEXTPROC_SUCCESS)
{
return;
}
else
{
/* Incorrect parameters were passed. */
assert(0);
}
}
*result = dividend / divisor;
}
This service routine enables OCI callbacks to the database during an external procedure call. Use the OCI handles obtained by this function only for callbacks. If you use them for standard OCI calls, the handles establish a new connection to the database and cannot be used for callbacks in the same transaction. In other words, during an external procedure call, you can use OCI handles for callbacks or a new connection but not for both.
The C prototype for this function follows:
sword OCIExtProcGetEnv(
OCIExtProcContext *with_context,
OCIEnv **envh,
OCISvcCtx **svch,
OCIError **errh);
The parameter with_context is the context pointer, and the parameters envh, svch, and errh are the OCI environment, service, and error handles, respectively. The return values OCIEXTPROC_SUCCESS and OCIEXTPROC_ERROR indicate success or failure.
The next section shows how OCIExtProcGetEnv might be used in callbacks. For a working example, see "Demo Program" on page 10-21.
An external procedure executing on the Oracle Server can call a service routine to obtain OCI environment and service handles. With the OCI, you can use callbacks to execute SQL statements and PL/SQL subprograms, fetch data, and manipulate LOBs. Moreover, callbacks and external procedures operate in the same user session and transaction context. So, they have the same user privileges.
In SQL*Plus, suppose you run the following script:
CREATE TABLE emptab (empno NUMBER(10))
/
CREATE PROCEDURE insert_emptab (
empno BINARY_INTEGER)
AS EXTERNAL
NAME "insert_emptab"
LIBRARY insert_lib
WITH CONTEXT
PARAMETERS (
CONTEXT,
empno LONG)
/
Later, you might call service routine OCIExtProcGetEnv from external procedure insert_emptab, as follows:
#include <stdio.h>
#include <stdlib.h>
#include <oratypes.h>
#include <oci.h>
...
void insert_emptab (ctx, empno)
OCIExtProcContext *ctx;
long empno;
{
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIError *errhp;
int err;
...
err = OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp);
...
}
With callbacks, the following SQL commands and OCI routines are not supported:
OCIEnvInit
OCIInitialize
OCIPasswordChange
OCIServerAttach
OCIServerDetach
OCISessionBegin
OCISessionEnd
OCISvcCtxToLda
OCITransCommit
OCITransDetach
OCITransRollback
OCITransStart
Also, with OCI routine OCIHandleAlloc, the following handle types are not supported:
OCI_HTYPE_SERVER
OCI_HTYPE_SESSION
OCI_HTYPE_SVCCTX
OCI_HTYPE_TRANS
Usually, when an external procedure fails, its C prototype is faulty. That is, the prototype does not match the one generated internally by PL/SQL. This can happen if you specify an incompatible C datatype. For example, to pass an OUT parameter of type REAL, you must specify float *. Specifying float, double *, or any other C datatype will result in a mismatch.
In such cases, you might get a lost RPC connection to external procedure agent error, which means that agent extproc terminated abnormally because the external procedure caused a core dump. To avoid errors when declaring C prototype parameters, refer to Table 10-2 on page 10-9.
To help you debug external procedures, PL/SQL provides the utility package DEBUG_EXTPROC. To install the package, run the script dbgextp.sql, which you can find in the PL/SQL demo directory. (For the location of the directory, see your Oracle installation or user's guide.)
To use the package, follow the instructions in dbgextp.sql. Your Oracle account must have EXECUTE privileges on the package and CREATE LIBRARY privileges.
Note: DEBUG_EXTPROC works only on platforms with debuggers that can attach to a running process.
Also in the PL/SQL demo directory is the script extproc.sql, which demonstrates the calling of an external procedure. The companion file extproc.c contains the C source code for the external procedure.
To run the demo, follow the instructions in extproc.sql. You must use the SCOTT/TIGER account, which must have CREATE LIBRARY privileges.
In future releases, extproc might be a multi-threaded process. So, be sure to write thread-safe external procedures. That way, they will continue to run properly if extproc becomes multi-threaded. In particular, avoid using static variables, which can be shared by routines running in separate threads. Otherwise, you might get unexpected results.
For help in creating a dynamic link library, look in the RDBMS subdirectory /public, where a template makefile can be found.
When calling external procedures, never write to IN parameters or overflow the capacity of OUT parameters. (PL/SQL does no runtime checks for these error conditions.) Likewise, never read an OUT parameter or a function result. Also, always assign a value to IN OUT and OUT parameters and to function results. Otherwise, your external procedure will not return successfully.
If you include the WITH CONTEXT and PARAMETERS clauses, you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list. If you omit the PARAMETERS clause, the context pointer is the first parameter passed to the external procedure.
If you include the PARAMETERS clause and the external routine is a function, you must specify the parameter RETURN (not RETURN property) in the last position.
For every formal parameter, there must be a corresponding parameter in the PARAMETERS clause. Also, make sure that the datatypes of parameters in
the PARAMETERS clause are compatible with those in the C prototype because no implicit conversions are done.
A parameter for which you specify INDICATOR or LENGTH has the same parameter mode as the corresponding formal parameter. But, a parameter for which you specify MAXLEN, CHARSETID, or CHARSETFORM is always treated like an IN parameter, even if you also specify BY REF.
With a parameter of type CHAR, LONG RAW, RAW, or VARCHAR2, you must use the property LENGTH. Also, if that parameter is IN OUT or OUT and null, you must set the length of the corresponding C parameter to zero.
Currently, the following restrictions apply to external procedures:
