OraDescribeSP Function

Action

Describes the parameters of a PL/SQL procedure or function that is stored in an Oracle database.

Include file

Ora.bdh

Syntax

OraDescribeSP( in    hConnection : number,
               inout sObjName    : string,
               inout nArraySize  : number optional,
               out   sName       : array of string(30) optional,
               out   nDatatype   : array of number optional,
               out   nMode       : array of number optional, 
               out   nSize       : array of number optional ): boolean;

Return value

  • true if successful

  • false otherwise. In this case, you can use the OraOciError function to retrieve the Oracle OCI error code

Example

Parameter Description
hConnection Handle to an Oracle database connection
sObjName Name of the PL/SQL procedure or function, including optional schema and package name. Quoted names are accepted. Synonyms are also accepted and are translated.
nArraySize Array size of the sName, nDatatype, nMode and nSize parameter. This parameter receives the actual number of array elements described.
sName

String array receiving the name of each parameter of the PL/SQL procedure or function.

Note: The size of each string buffer must be exactly 30 characters.
nDatatype Array receiving the data type of each parameter of the procedure or function. See Oracle data types for a list of all data types.
nMode Array receiving the mode of each parameter of the procedure or function. Zero (0) indicates an IN parameter, one (1) an OUT parameter, and two (2) an IN/OUT parameter.
nSize Array receiving the size of each parameter of the procedure or function (in bytes). Character data types return the size of the parameter. For number types, 22 is returned.

Example

var
  hConnect : number;
  cCursor  : cursor;

dcltrans
  transaction TMain
  const
    MAXARR := 100;
  var
    nMaxArr, i : number;
    sParam     : array[MAXARR] of string(30);
  begin
    OraLogon(hConnect, "username/password@orclnet2");
    OraOpen(cCursor, hConnect);
    nMaxArr := MAXARR;
    OraDescribeSP(hConnect, "sp", nMaxArr, sParam);
    for i := 1 to nMaxArr do
      write("Param: "); write(sParam[i]); writeln;
    end;
    ...
  end TMain;

Output

Param: NO
Param: OPENING
Param: TITLE
Param: FIRSTNAME
Param: LASTNAME
Param: ALINE1
Param: ALINE2
Param: ZIP
Param: DISTRICT

See also

Oracle's Programmer's Guide to Oracle Call Interface for Wrapped Oracle function: odessp