Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news-out.triton.net!triton.net!newsfeeder.triton.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!small1.nntp.aus1.giganews.com!nntp2.aus1.giganews.com!intern1.nntp.aus1.giganews.com!nntp.giganews.com!nntp.speakeasy.net!news.speakeasy.net.POSTED!not-for-mail
NNTP-Posting-Date: Tue, 11 Nov 2003 16:31:16 -0600
From: andreyNSPAM@bookexchange.net (NetComrade)
Newsgroups: comp.databases.oracle.server
Subject: Sql*Net roundtrips with array<->varray copying in pro*c
Date: Tue, 11 Nov 2003 22:48:17 GMT
Message-ID: <3fb164a8.1585273843@nyc.news.speakeasy.net>
X-Newsreader: Forte Free Agent 1.11/32.235
Lines: 241
NNTP-Posting-Host: 64.81.217.36
X-Trace: sv3-sYsg982WuPS8Cq/avxaQ8lsvbL3vZYKjhb/ruKVZ5KhxbNMVPDhAOqqcQiIsEUAw3ygZMVX3JbYGbDr!RHZohWc/kvE9nn9nL5Q1KIQ7pXwLjtmeGvWOOMuhHiIWoT54ryNLJQtgAibaekbph+kRLE1V6RZ9!G2r8IZw69E7b37/2tGFlxIojcX4=
X-Complaints-To: abuse@speakeasy.net
X-DMCA-Complaints-To: abuse@speakeasy.net
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.1
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:247477

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@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@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