Describe a database procedure

/*--------------------------------------------------------------------
 * Example Pro*C program to describe a database procedure and print
 * its arguments.
 *						Frank Naude - Sep 2000
 *--------------------------------------------------------------------
 */

#define MAX_PLSQL_PARAMETERS 30

#include <stdlib.h>
#include <stdio.h>

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

typedef char strz[31];
EXEC SQL TYPE strz IS STRING(31) REFERENCE;

/* Declare functions */
int ora_logon();
int ora_logoff();
int ora_error();
int plsql_desc(char *procname);

/*--------------------------------------------------------------------*/
int main() {
  ora_logon();
  printf("About to describe procedure DBMS_OUTPUT.PUT_LINE...\n");
  plsql_desc("DBMS_OUTPUT.PUT_LINE");
  ora_logoff();
  return 0;
}

/*--------------------------------------------------------------------
 * Login to the Oracle database
 *--------------------------------------------------------------------*/
int ora_logon() {

  EXEC SQL BEGIN DECLARE SECTION;
    char *oracleid = "monitor/oramon";
  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL CONNECT :oracleid;
  if (sqlca.sqlcode != 0) {
     printf("ERROR: Unable to login to Oracle\n%.*s",
            sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  }
}

/*--------------------------------------------------------------------
 * Logoff from the Oracle database
 *--------------------------------------------------------------------*/
int ora_logoff() {
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ALTER SESSION SET SQL_TRACE FALSE;
  EXEC SQL COMMIT WORK RELEASE;
}

/*--------------------------------------------------------------------
 * Handle Oracle errors
 *--------------------------------------------------------------------*/
int ora_error() {
  char errmsg[2000];
  unsigned int buf_len, msg_len;

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  buf_len = sizeof(errmsg);
  sqlglm(errmsg, &buf_len, &msg_len);
  printf("Oracle Error: %.*s\n", msg_len, errmsg);
  exit(8);
}

/*--------------------------------------------------------------------
 * Describe PL/SQL parameters
 *--------------------------------------------------------------------*/
int plsql_desc(char *procname) {

   int          overload      [MAX_PLSQL_PARAMETERS];
   int          position      [MAX_PLSQL_PARAMETERS];
   int          level         [MAX_PLSQL_PARAMETERS];
   static strz  argument_name [MAX_PLSQL_PARAMETERS];
   static short arg_name_ind  [MAX_PLSQL_PARAMETERS];
   static int   datatype      [MAX_PLSQL_PARAMETERS];
   int          default_value [MAX_PLSQL_PARAMETERS];
   int          in_out        [MAX_PLSQL_PARAMETERS];
   int          length        [MAX_PLSQL_PARAMETERS];
   int          precision     [MAX_PLSQL_PARAMETERS];
   int          scale         [MAX_PLSQL_PARAMETERS];
   int          radix         [MAX_PLSQL_PARAMETERS];
   int          spare         [MAX_PLSQL_PARAMETERS];
   int          i = 0;
   int          t = -1;       /* Default return type is -1 */

   for (i=0; i<MAX_PLSQL_PARAMETERS; i++) {
       overload[i] = -1;
   }

   EXEC SQL WHENEVER SQLERROR DO ora_error();
   EXEC SQL EXECUTE
      BEGIN
        dbms_describe.describe_procedure(:procname, NULL, NULL,
             :overload, :position, :level, :argument_name:arg_name_ind,
             :datatype, :default_value, :in_out, :length, :precision,
             :scale, :radix, :spare);
      END;
   END-EXEC;

   /* The datatypes and their numeric type codes are:
       0   placeholder for procedures with no arguments
       1   VARCHAR, VARCHAR, STRING
       2   NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL
       3   BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL
       8   LONG
       11  ROWID
       12  DATE
       23  RAW
       24  LONG RAW
       96  CHAR (ANSI FIXED CHAR), CHARACTER
       106 MLSLABEL
       250 PL/SQL RECORD
       251 PL/SQL TABLE
       252 PL/SQL BOOLEAN
   */

   /* Print parameters with types */
   i = 0;
   while ((overload[i] != -1) && (i < MAX_PLSQL_PARAMETERS)) {
      printf ("pos=%d overl=%d ", position[i], overload[i]);
      if (arg_name_ind[i] != -1) {
         printf("nam=%s ", argument_name[i]);
      }
      printf("lev=%d typ=%d len=%d ", level[i],datatype[i],length[i]); 
      printf("prec=%d scale=%d radix=%d\n",precision[i],scale[i],radix[i]);
      i++;
   }

}

/* EOF ****************************************************************/