Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)
ISBN: 0596514468
EAN: 2147483647
EAN: 2147483647
Year: 2004
Pages: 234
Pages: 234
Authors: Steven Feuerstein, Bill Pribyl
| Chapter 21 External Procedures |
Consider for a moment the problems of exchanging data between PL/SQL and C. PL/SQL has its own set of datatypes that are only somewhat similar to those you find in 3GLs. PL/SQL variables can be NULL and subject to three-valued truth table logic; C variables have no equivalent concept. Your C library might not know which national language character set you're using to express alphanumeric values. And should your C functions expect a given argument by value, or by reference (pointer)?
Given these hurdles, it would be easy to conclude that the job is impossible or, at best, difficult. The good news, though, is that Oracle has thought of all these issues already, and has built a lot of options into the PARAMETERS clause to cover the possibilities. So the programmer's key task is to figure out how to apply the options to a given situation.
Let's look first at the issue of datatype conversions. Oracle has kindly provided a useful set of default type conversions. Each PL/SQL datatype maps to an "external datatype," which in turn maps to an allowed set of C types as illustrated below:
| PL/SQL types External types C types |
The external datatypes, which are included in the PARAMETERS clause, are case-insensitive. In some cases, the external datatypes have the same name as the C type, but in some others, they do not. For example, the STRING external datatype maps to a char * in C.
As another example, if you pass a PL/SQL variable of type PLS_INTEGER, the corresponding default external type is INT, which maps to an int datatype in C. Or if you prefer, you can override this conversion with an explicit mapping to other external types such as SHORT (maps to short in C) or UNSIGNED INT (maps to unsigned int in C).
Table 21.1 lists all the default datatype conversions, as well as alternative conversions, allowed by Oracle's PL/SQL to C interface.For brevity, in the cases where the external datatype and the C datatype are the same except for case sensitivity, we have listed the type name only once, in lowercase. Note that the allowable conversions depend on both the datatype and the mode of the PL/SQL formal parameter.
| C Datatype if PL/SQL Formal Parameters are... | |||
|---|---|---|---|
| PL/SQL Datatype | IN or RETURN | IN BY REFERENCE or RETURN BY REFERENCE | IN OUT or OUT |
| "Long" integer family: BINARY_INTEGER, BOOLEAN, PLS_INTEGER | int, char, unsigned char, short, unsigned short, unsigned int, long, unsigned long, sb1, ub1, sb2, ub2, sb4, ub4, size_t | Same list of types as at left, but use a pointer (for example, the default is int * rather than int) | Same list of types as at far left, but use a pointer (for example, the default is int * rather than int) |
| "Short" integer family: NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE | Same as above, except default is unsigned int | Same as above, except default is unsigned int * | Same as above, except default is unsigned int * |
| "Character" family: VARCHAR2, CHAR, LONG, VARCHAR, CHARACTER, ROWID | STRING[6] | STRING[6] | STRING[6] |
| DOUBLE PRECISION | Double | Double | Double |
| FLOAT, REAL | Float | Float | Float |
| RAW, LONG RAW | RAW[7] | RAW[7] | RAW[7] |
| BFILE, BLOB, CLOB | OCILOBLOCATOR[8] | OCILOBLOCATOR | OCILOBLOCATOR |
| "User-defined" family: records, collections, objects, cursor variables | Disallowed in this Oracle release | Disallowed in this Oracle release | Disallowed in this Oracle release |
[6] In the PARAMETERS clause, use the external datatype STRING, but in the C specification, use char *.
[7] In the PARAMETERS clause, use the external datatype RAW, but in the C specification, use unsigned char *.
[8] In the PARAMETERS clause, use the external datatype OCILOBLOCATOR; in the C specification, use OciLobLocator * for parameters of mode IN, RETURN, IN BY REFERENCE, or RETURN BY REFERENCE; use OciLobLocator ** for IN OUT or OUT.
In some simple cases where you are passing only numeric arguments and where the defaults are acceptable, you can omit the PARAMETERS clause entirely.
By contrast, any time you return a character parameter from an external procedure, you may need to include an explicit PARAMETERS clause. This is so that you can supply a "property" parameter such as INDICATOR, LENGTH, or MAXLEN that will tell PL/SQL the actual and maximum size of the character buffer. These properties apply both to arguments and to function return values. As it turns out, LENGTH is only needed for RAW datatypes since strings are null-terminated.
For example, if you had a generic procedure which accepted an operating system command and returned output from that command, your procedure body might look like this (notice the PARAMETERS clause):
PROCEDURE run_command (command IN VARCHAR2, result OUT VARCHAR2) IS EXTERNAL LIBRARY libshell_l LANGUAGE C PARAMETERS (command STRING, result STRING, result INDICATOR, result MAXLEN);
INDICATOR and MAXLEN are two of five properties with which we can pass supplemental information for any given PL/SQL parameter. We pass the "indicator" in addition to the variable if it's important to detect whether the value is null. Once we specify that the indicator should be included, Oracle sets and interprets this value properly on the PL/SQL side. Our C application, though, will need to get and set this value programmatically. MAXLEN, on the other hand, is a read-only property that gets set automatically by the PL/SQL environment; MAXLEN communicates to the C program the maximum storage that can be used for an IN OUT, OUT, or RETURN parameter.
Each piece of supplemental information we want to exchange will be passed as a parameter, and will appear both in the PARAMETERS clause and in the C language function specification.
Three types of entries may appear in the PARAMETERS clause:
Formal parameters of the PL/SQL module
The function return value
A placeholder for the context area
The syntax you use to map a PL/SQL formal parameter to a C parameter is:
<parameter name> [<property>] [BY REFERENCE] [<external datatype>]
For function return values, you use the keyword RETURN in lieu of a parameter name. RETURN must appear in the last position in the PARAMETERS clause:
RETURN <property> [BY REFERENCE] [<external datatype>]
Use the third variation of the external PARAMETER clause when you have specified WITH CONTEXT. In this case, the parameter is simply
CONTEXT
By convention, if you have specified WITH CONTEXT, you should make CONTEXT the first argument. That is its default location if you default all of the parameter mappings.
Parameter entries have the following meanings:
The name of the parameter as specified in the formal parameter list of the PL/SQL module. This name is not necessarily the name of the formal parameter in the C language routine. However, parameters in the PL/SQL parameter list must match one-for-one, in order, those in the C language specification.
One of the following: INDICATOR, LENGTH, MAXLEN, CHARSETID, or CHARSETFORM. These are described in Section 21.4.3, "Properties".
Pass the parameter by reference. In other words, the module in the shared library is expecting a pointer to the parameter rather than its value. BY REFERENCE only has meaning for scalar IN parameters that are not strings, such as BINARY_INTEGER, PLS_INTEGER, FLOAT, DOUBLE PRECISION, and REAL. All others (IN OUT and OUT parameters, as well as IN parameters of type STRING) are always passed by reference, and the corresponding C prototype must specify a pointer.
WARNING: The documentation for Oracle 8.0.3 erroneously states that the syntax for this option is "BY REF".
The C language datatype. If this is omitted, the external datatype will default as indicated in Table 21.1. Most conversions that make sense are legal; see the table for additional details.
TIP: When you are mapping parameters, you must use positional notation in the PARAMETERS clause. That is, the parameters you supply in this clause must match those in the C language function specification one-for-one, and must appear in the same order.
This section describes each possible property you can specify in a PARAMETERS clause.
"Flag" to denote whether the parameter is null
short (the default), int, long
All scalars
IN, IN OUT, OUT, RETURN
By value for IN parameters (unless BY REFERENCE specified), and by reference for IN OUT, OUT, and RETURN variables
You can apply this property to any parameter, in any mode, including RETURNs. If you omit an indicator, PL/SQL is supposed to think that your external routine will always be non-null (but it's not that simple; see the sidebar the sidebar "Indicating Without Indicators?").
When you send an IN variable to the external procedure, and you've associated an indicator, Oracle will set its value automatically. However, if your C module is returning a value in a RETURN or OUT parameter and an indicator, your C code must set the indicator value.
For an IN parameter, an example of the indicator parameter in your C function might be:
sb2 pIndicatorFoo
Or for an IN OUT parameter, the indicator might be:
sb2 *pIndicatorFoo
In the body of your C function, you should use the #define constants OCI_IND_NOTNULL and OCI_IND_NULL supplied in oro.h as values for the NOT NULL and NULL values. These are defined in oro.h as:
#define OCI_IND_NOTNULL 0 /* not NULL */ #define OCI_IND_NULL (-1) /* NULL */
What happens if you don't specify an indicator variable for a string and then return an empty C string? We wrote a short test program to find out:
void myfunc(char *outbuff) { outbuff[0] = '\0'; }When invoked as an external procedure, PL/SQL interprets this parameter value as a NULL! The reason appears to be that the STRING external type is special; you can also indicate a NULL value to Oracle by passing a string of length 2 where the first byte is '\0'. (This only works if you omit a LENGTH parameter.)
But don't rely on this little-known behavior. Always use an indicator!
Statistic indicating the number of characters in a character parameter
int (the default), short, unsigned short, unsigned int, long, unsigned long
VARCHAR2, CHAR, RAW, LONG RAW
IN, IN OUT, OUT, RETURN
By value for IN parameters (unless BY REFERENCE specified), and by reference for IN OUT, OUT, and RETURN variables
The Oracle documentation states that you must include the LENGTH property for CHAR, RAW, LONG RAW, or VARCHAR2 parameters. In fact, LENGTH is only mandatory for RAW and LONGRAW. CHAR and VARCHAR2 are, in fact, passed on as STRING parameters for which the LENGTH parameter is redundant (since STRINGs follow null-termination semantics). For the external RAW datatype, a LENGTH parameter is necessary to read the length of the RAW data for IN and IN OUT variable modes, and to tell PL/SQL the length of the raw data for IN OUT, OUT, and RETURN variable modes.
For an IN parameter, an example of the indicator parameter in your C function might be:
int pLenFoo
Or for an OUT or IN OUT parameter:
int *pLenFoo
Statistic indicating the maximum number of characters in a string parameter
int (the default), short, unsigned short, unsigned int, long, unsigned long
VARCHAR2, CHAR, RAW, LONG RAW
IN OUT, OUT, RETURN
By reference
MAXLEN is applied to IN OUT or OUT parameters and to no other mode. If you attempt to use it for an IN, you'll get a compile-time error "PLS-00250: Incorrect Usage of MAXLEN in parameters clause."
Unlike the LENGTH parameter, the MAXLEN data is always passed by reference.
An example of the C formal parameter follows:
int *pMaxLenFoo
Flags denoting information about the character set
unsigned int (the default), unsigned short, unsigned long
VARCHAR2, CHAR, CLOB
IN, IN OUT, OUT, RETURN
By reference
If you are passing data to the external procedure which is expressed in a nondefault character set, these properties will let you communicate its ID and form to the called C program. The values are read-only and should not be modified by the called program. For more information about national language support and how to accommodate it in an OCI program, refer to Oracle's Programmer's Guide to the Oracle Call Interface.
With one exception, every parameter-property combination that you list in the PARAMETERS clause must have an entry in the C function specification. For example, if you had the following body:
CREATE OR REPLACE PACKAGE BODY ext_utils AS PROCEDURE my_foo (foo IN OUT VARCHAR2) IS EXTERNAL LIBRARY foobar_l PARAMETERS (foo STRING, foo MAXLEN, foo LENGTH); END ext_utils;
then the C prototype would look like this:
void myFunction (char *pFoo, int *pMaxLenFoo, int *pLenFoo );
Notice that myFunction is declared void, which is appropriate when mapping to a PL/SQL procedure rather than a function. Also, since this PARAMETERS clause includes no explicit datatypes, we will get the default type mapping:
STRING → char * MAXLEN → int * LENGTH → int *
Char is typedefined as an unsigned char in oratypes.h.
The exception to the one-to-one correspondence rule occurs when explicitly declaring properties of the function return value. As an example, look at the parameter list below:
PARAMETERS (RETURN INT)
The corresponding C prototype could be:
int someFunction();
(OK, it's not really an exception; it's more a question of semantics.)
| 21.3 Syntax for External Procedures | 21.5 OCI Service Routines |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.