0 Replies Latest reply: Jun 15, 2012 10:11 AM by 943610 RSS

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

    943610
      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