Sql*Net roundtrips with array<->varray copying in pro*c

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Tue, 11 Nov 2003 22:48:17 GMT
Message-ID: <3fb164a8.1585273843@nyc.news.speakeasy.net>

We are trying to build an array in pl/sql and copy it back to pro*c.

Oracle has sent us a sample.. but when we run it, we get too many roundtrips and too much CPU used.

The two tests we did:
1) plain select * from a table
2) write a pl/sql that populates an array and pass it back to pro*c.

Is there are a way to copy the array, it seems like the stuff they sent doesn't actually copy the array, but references is from pro*c (and processes records one by one).

oracle sample attached:

• You MUST define the types (objects and Varrays) outside the scope of the Package.
• The following defines the Varray of Numbers. Create type t_VarrayNumber is VARRAY(3) of Number(5); /
• The following defines the Object used within the Varray of Objects. Create type t_EmpObj as object (name VARCHAR2(20), empno Number(5)); /
• The following defines the Varray of Objects. Create type t_VarrayEmpObj is VARRAY(3) of t_EmpObj; /
• The following is the Package Spec. Here the three variables are defined as well
• as procedure used to populate the variables with data. Create or Replace Package VArray_Test IS v_Number Number(5); v_VarrayNumber t_VarrayNumber; v_VarrayEmpObj t_VarrayEmpOBj; Procedure PopulateWithData; END; /

--The following is the Package Body.
Create or Replace Package Body VArray_Test IS

   Procedure PopulateWithData IS
   Begin

      v_Number := 45678;
      v_VarrayNumber := t_VarrayNumber(20, 21, 22);
      v_VarrayEmpObj := t_VarrayEmpOBj(t_EmpObj('John Doe', 804),
                                       t_EmpObj('Jane Doe', 19530),
                                       t_EmpObj('John Thompson',

1921));

    End;
END;
/

--Once the above types are defined you need to prepare for precompiling the code.
--The following creates the in.typ file used to build the header file for the code.
in.typ

---

case=lower

type t_empobj
type t_varrayempobj
type t_varraynumber

--Execute the OTT.
ott intype=in.typ outtype=out.typ hfile=mytypes.h user=scott/tiger_at_v816 code=c

Here is an example of the out.typ file produced by the ott

---

CASE = LOWER TYPE JIM.T_EMPOBJ AS t_empobj
  VERSION = "$8.0"
  HFILE = mytypes.h

TYPE JIM.T_VARRAYEMPOBJ AS t_varrayempobj   VERSION = "$8.0"
  HFILE = mytypes.h

TYPE JIM.T_VARRAYNUMBER AS t_varraynumber   VERSION = "$8.0"
  HFILE = mytypes.h

Here is an example of the mytypes.h produced by the ott

---

#ifndef MYTYPES_ORACLE
# define MYTYPES_ORACLE

#ifndef OCI_ORACLE
# include <oci.h>
#endif

typedef OCIRef t_empobj_ref;
typedef OCIArray t_varrayempobj;
typedef OCIArray t_varraynumber;

struct t_empobj
{

   OCIString * name;
   OCINumber empno;
};
typedef struct t_empobj t_empobj;

struct t_empobj_ind
{

   OCIInd _atomic;
   OCIInd name;
   OCIInd empno;
};
typedef struct t_empobj_ind t_empobj_ind;

#endif

Here is some information for the Precompiler settings:

---

InType Filename:
<Path>\in.typ

System Include Directory:
<Path to VC header files>

Include Directory:
<Oracle Home>\oci\include;<Path mytypes.h file created>

SQLCheck = Semantics

Parse = Full

/****************************************************************
             Here is the code for the PC file.
****************************************************************/


#include <stdio.h> 

#include <oci.h> 

#include <sqlcpr.h>
#include <mytypes.h>

int main(void)
{

   EXEC SQL BEGIN DECLARE SECTION;

      //Defined for returning the single number.
      int simpleNumber;

      //Defined for returning the Varray of numbers.
      t_varraynumber *varrayNumber;
      int VarrayNumber;

      //Defined for returning the Varray of an object.
      t_varrayempobj *varrayObject;
      t_empobj *empObj;
      char b_name[20];
      int b_empno;

      //The connect string.
      char *connString = "jim/tiger_at_v816test";

   EXEC SQL END DECLARE SECTION;    EXEC SQL CONNECT :connString;    

   EXEC SQL EXECUTE
   BEGIN
      Varray_Test.PopulateWithData;
   END;
   END-EXEC;

/***************************************************************** 
           The following returns the single number. 
*****************************************************************/

   EXEC SQL EXECUTE
   BEGIN
      :simpleNumber := Varray_Test.v_Number;    END;
   END-EXEC;
   printf("The single number is ---> %d\n\n", simpleNumber);

   EXEC SQL ALLOCATE :varrayNumber;
   EXEC SQL ALLOCATE :varrayObject;
   EXEC SQL ALLOCATE :empObj;

/***************************************************************** 
       The following returns the Varray of Numbers.
*****************************************************************/

   EXEC SQL EXECUTE
   BEGIN
      :varrayNumber := Varray_Test.v_VarrayNumber;    END;
   END-EXEC;        printf("Varray of numbers:\n");

   EXEC SQL COLLECTION GET :varrayNumber INTO :VarrayNumber;    printf("Value 1 ---> %d\n", VarrayNumber);

   EXEC SQL COLLECTION GET :varrayNumber INTO :VarrayNumber;    printf("Value 2 ---> %d\n", VarrayNumber);

   EXEC SQL COLLECTION GET :varrayNumber INTO :VarrayNumber;    printf("Value 3 ---> %d\n", VarrayNumber);

/***************************************************************** 
      The following returns the Varray of an object.
*****************************************************************/

   EXEC SQL EXECUTE
   BEGIN
      :varrayObject := Varray_Test.v_VarrayEmpOBj;    END;
   END-EXEC;    printf("\nVarray of an Object:\n");

   EXEC SQL COLLECTION GET :varrayObject INTO :empObj;    EXEC SQL OBJECT GET name, empno from :empObj INTO :b_name, :b_empno;

   printf("Object 1:\n");
   printf("Name ---> %15s Employee # ---> %d\n", b_name, b_empno);

   EXEC SQL COLLECTION GET :varrayObject INTO :empObj;    EXEC SQL OBJECT GET name, empno from :empObj INTO :b_name, :b_empno;

   printf("Object 2:\n");
   printf("Name ---> %15s Employee # ---> %d\n", b_name, b_empno);

   EXEC SQL COLLECTION GET :varrayObject INTO :empObj;    EXEC SQL OBJECT GET name, empno from :empObj INTO :b_name, :b_empno;

   printf("Object 3:\n");
   printf("Name ---> %15s Employee # ---> %d\n", b_name, b_empno); return(1);
}   

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Tue Nov 11 2003 - 16:48:17 CST