This discussion is archived
0 Replies Latest reply: Jun 15, 2012 8:11 AM by 943610 RSS

Pro*C: select where field in( host array )

943610 Newbie
Currently Being Moderated
I wrote the example program that uses an array of strings is a query condition. However, it's too complex. Can someone assit in simplifying it?

Is it possible to fill a nested table on a client?
Can I get rid of PL/SQL and use "EXEC SQL DECLARE CURSOR" instead?
Can I mix "OCITable" and embedded SQL?
/* test-oracle-proc.h */

#include <stddef.h>

/* Pro*C VARCHAR has many restrictions
   typedef VARCHAR[len] and VARCHAR in *.h files not allowed;
   arrays of VARCHAR[len] can't be function arguments.
   The following macros aid in it.
  */

/* calculates the desired size of "arr" member. The size of the whole struct must divide by 4 */
#define _ARRAY_OF_VARCHAR_ARR_SIZE(_len) (((sizeof(((varchar *)0)->len) + (size_t)(_len) + 3) & (size_t)-4) -2)

/* {...} part of VARCHAR struct */
#define ARRAY_OF_VARCHAR_STRUCT_BODY(_len) { unsigned short len; unsigned char arr[ _ARRAY_OF_VARCHAR_ARR_SIZE(_len)]; }


/* all_objects fields */
#define FS_OWNER 100
#define FS_TYPE 100
#define FS_NAME 100

struct vc_name ARRAY_OF_VARCHAR_STRUCT_BODY(FS_NAME); /* declare the type of VARCHAR[FS_NAME] */

/* Performs the select:
     select owner, object_name, object_type
       from all_objects
       where OBJECT_NAME in( names );
   and prints the result.
 */
void print_object_types(struct vc_name* names, int nameslen);

/* END test-oracle-proc.h */











/* test-oracle-proc.pc: */

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

EXEC SQL INCLUDE sqlca;

EXEC SQL CONTEXT USE :ctx;

#define NO_FETCH        0
#define MUST_FETCH      1
#define ONE_FETCH       2

#define DB_SUCCESS            0
#define DB_NOTFOUND           1403
#define DB_NOTCONNECTED       1012
#define DB_TIMEOUT            2396
#define DB_DUPLICATE_KEYINDEX -1
#define DB_NOT_A_VALID_MONTH  -1843
#define DB_ROW_LOCKED         -54

void sqlglmt( sql_context, char *, size_t *, size_t * ); 
void sqlglm(char   *message_buffer, size_t *buffer_size, size_t *message_length); 
int sqlglst( sql_context, char *, size_t *, size_t * );
int sqlgls( char *, size_t *, size_t * );

static sql_context ctx;
struct sqlca sqlca;

static void ServiceDBFreeContext( sql_context ctx )
{
  struct sqlca sqlca;
  EXEC SQL ROLLBACK RELEASE;
  EXEC SQL CONTEXT FREE :ctx;
}

static int ServiceDBCheckSql(
  char *func_name,
  char *location, 
  int mask_out )
{
  char error_text[1000];
  size_t msg_len;
  size_t buf_len;
  struct sqlca* my_sqlca;

  my_sqlca = &sqlca;

  if ((my_sqlca->sqlcode == DB_SUCCESS)
      && ( (( (long)(my_sqlca->sqlerrd[2]) == 1) && (mask_out == ONE_FETCH))
           ||
           (mask_out != ONE_FETCH)
         )
     )
  {
    return 0;
  }

  if ((my_sqlca->sqlcode == (long)DB_NOTFOUND ) && (mask_out == NO_FETCH))
  {
    return 0;
  }
 
  if (my_sqlca->sqlcode != DB_SUCCESS)
  {
#define SQL_STMT_MAX_LEN (2048-200)
    size_t sqlfc = 0;
    size_t stmt_len = SQL_STMT_MAX_LEN;
    char stmt_text[SQL_STMT_MAX_LEN+1];
    int rc;
    rc = sqlglst( ctx, stmt_text, &stmt_len, &sqlfc);
    if (rc && stmt_len != 0)
    {
      if (stmt_text[stmt_len-1] == '\n' || stmt_text[stmt_len-1] == '\r') stmt_len--;
      fprintf(stderr, "%.*s\n", stmt_len, stmt_text);
      fprintf(stderr, "%*s^\n", my_sqlca->sqlerrd[4], "");
    }
    buf_len = sizeof(error_text);
    sqlglmt( ctx, error_text, &buf_len, &msg_len );

    if (error_text[msg_len-1] == '\n' || error_text[msg_len-1] == '\r') msg_len--;

    fprintf(stderr, "%.*s\n", msg_len, error_text);
  }
  else
  {
    if (mask_out == ONE_FETCH)
    {
      fprintf(stderr, "%s(%s): Exactly one row expected (ONE_FETCH) but %d rows returned\n", func_name, location, my_sqlca->sqlerrd[2] );
    }
  }

  fprintf(stderr, "%s(%s): ROLLBACK \n", func_name, location);
  EXEC SQL rollback;

  return 1;
}

static int ServiceDBConnect( const char *user )
{
  EXEC ORACLE OPTION (MAXOPENCURSORS=64);
  EXEC SQL CONNECT :user;

  return ServiceDBCheckSql("ServiceDBConnect", "CONNECT", NO_FETCH);
}

static int ServiceDBInit(const char *orauid )
{
  char* db_pool_size_str = NULL;
  int db_pool_size = 0;
  int rc;
  sql_context ctx_;

  if (orauid == NULL || orauid[0] == 0) {
    fprintf(stderr, "ServiceDBInit: orauid empty\n");
    return 1;
  }

  EXEC SQL CONTEXT ALLOCATE :ctx_;
  ctx = ctx_;

  return ServiceDBConnect( orauid );
}

static sql_error() 
{
    ServiceDBCheckSql("x", "x", 0);
    exit(1); 
}

EXEC SQL WHENEVER SQLERROR DO sql_error();

/* define the VARCHAR struct initializer: VCI("string") */
#ifndef ORA_PROC
# define stupid_sizeof sizeof /* Pro*C doesn't like sizeof() in VARCHAR struct initializer */
#endif
#undef VCI
#define VCI(s) {stupid_sizeof(s)-1, s}

/* usage: prog user/password@SID */
int main(int argc, char *argv[]) {
  struct vc_name names[] /* or VARCHAR names[2][FS_NAME] */
      = {
          VCI("ALL_OBJECTS")
          ,VCI("ALL_OBJECT_TABLES")
        };
  int nameslen = sizeof(names)/sizeof(names[0]);

  if (argc < 2)
    return 1;
  if (0 != ServiceDBInit(argv[1]))
    return 1;
  printf("Connected\n"); fflush(stdout);

  print_object_types((struct vc_name*)names, nameslen);

  return 0;
}

/* Number of ITEMS in the local array must be >= array length variable, but the size in bytes is limited too */
#define LOCAL_ARRAY_MAX_SIZE 60000000 /* bytes */

void print_object_types(struct vc_name* names, int nameslen)
{
#ifdef ORA_PROC /* don't need this big local variable in the final code */
  /* I don't know how to bind the SQL type "assoc array of VARCHAR2(FS_NAME)" to "names".
     That's why declare a local variable with this SQL type */
  VARCHAR names_local[LOCAL_ARRAY_MAX_SIZE/FS_NAME][FS_NAME];
  EXEC SQL ARRAYLEN names_local (nameslen) EXECUTE; /* set "nameslen" as array size */
#endif
#undef names_local
#define names_local names /* replace "names_local" with "names" */

  long SQLCODE;

  SQL_CURSOR  dyn_cursor;
  char object_name[FS_NAME+1], object_type[FS_TYPE+1], owner[FS_OWNER+1];

  EXEC SQL ALLOCATE :dyn_cursor;

  EXEC SQL EXECUTE 
    DECLARE
      type StrTabIdxTyp IS TABLE OF VARCHAR2(100) index by binary_integer; /* assoc array local type */
      nstt sys.dbms_debug_vc2coll; /* Oracle ships with this nested table system type */

      /* converts Assoc array to nested table */
      function convert(t StrTabIdxTyp) return sys.dbms_debug_vc2coll is
        rslt sys.dbms_debug_vc2coll;
        i int;
      BEGIN
        rslt:= sys.dbms_debug_vc2coll();
        rslt.EXTEND(t.count);
        i:= 1;
        for j in t.FIRST..t.LAST LOOP
          rslt(i):= t(j);
          i:= i+1;
        END LOOP;
        return rslt;
      end;

    BEGIN
      nstt:= convert(:names_local);
      open :dyn_cursor for select OWNER, OBJECT_NAME, OBJECT_TYPE
        from all_objects
        where OBJECT_NAME in( SELECT COLUMN_VALUE from table(nstt) );
    END;
  END-EXEC; 

  EXEC ORACLE OPTION (char_map=STRING);

  EXEC SQL WHENEVER NOT FOUND DO BREAK;
  for(;;) {
    EXEC SQL FETCH :dyn_cursor INTO :owner, :object_name, :object_type;
    printf("owner: %10s, object_name: %20s, object_type: %s\n", owner, object_name, object_type);
  }
  EXEC SQL WHENEVER NOT FOUND CONTINUE;

  EXEC SQL CLOSE :dyn_cursor;

#undef names_local
}
Edited by: 940607 on 15.06.2012 8:09

Edited by: 940607 on 15.06.2012 8:10

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points