| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Sql*Net roundtrips with array<->varray copying in pro*c
In Pro*C, the fastest way of reading multiple rows of a recordset into
an array is by batch fetching using a cursor:
int emp_number[20];
float salary[20];
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, sal FROM emp;
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND do break;
for (;;)
{
EXEC SQL FETCH emp_cursor
INTO :emp_number, :salary;
/* process batch of rows */
...
What this does is declaring a server-side cursor (i.e. a chunk of memory on the server side), executing a select statement, and storing the results of the select in the server-side cursor. OCI then fetches the results from the server-side cursor onto your client-side array(s) in batches (limited by the size of your client-side array(s)) and populate your array(s) with the columns of the batch of rows.
This is analogous to "bulk fetch" in PL/SQL, which calls the same OCI routine. The only difference is that the PL/SQL interpreter calls the OCI routine in-process (with the database process), whereas your client program calls the OCI routine out-of-process.
Your step of compiling the recordset into a PL/SQL array before passing back to the client is not necessary. The result is already in the server-side cursor and OCI can perfectly handle fetching the results from the server-side cursor to the client in batches. There's no need for the intermediate packaging with your PL/SQL code.
Here's the documentation for it:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_08arr.htm#512
HTH,
Dave
NetComrade wrote:
> 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 - 17:52:22 CST
 |
 |