21.4.1 Datatype Conversion

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:

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.

[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.

21.4.2 More Syntax: The PARAMETERS Clause

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:

parameter name

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.

property

One of the following: INDICATOR, LENGTH, MAXLEN, CHARSETID, or CHARSETFORM. These are described in Section 21.4.3, "Properties".

BY REFERENCE

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".

external datatype

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.

21.4.3 Properties

This section describes each possible property you can specify in a PARAMETERS clause.

21.4.4 Correct Declaration of Properties

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.)