Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations

[vincent r. <vin...@ya...>]

Hi,

the syntax "select .... into ....." is wrong.

Bulk operation are related to binds variables, not selected columns.

What you want to do, is "array fetching". You want to fetch more than one
row at a time. No problem.... That what OCILIB already does internally.
When OCILIB fetches data (using OCI_FetchNext() for example), it fetches by
default 20 rows from the DB. Next calls to COI_FetchNext() does not do
servers round trips and local data is returned. Once you've called
OCI_FechNext 20 times, then the next call fetches another 20 rows from DB
and so on.

The number of rows that OCILIB internally fetches is by default 20 but can
be modified using OCI_SetFetchSize().

So, you don't have to provide arrays to OCILIB. You just call
OCI_FetchNext() and OCI_Getxxxxx() to retrieve values.

If you really want to copy values from OCILIB resultset to your own arrays
you could do like this :


OCI_SetFecthSize(pts);
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 into :out_d1, :out_d2, :out_d3,
:out_i1 from my_table where report_name = :in_report_name and edge = 'a'");
OCI_BindString(st, ":in_report_name", report_name, 60);
OCI_Execute(st);

rs = OCI_GetResultset(st);

while (OCI_FetchNext(rs))
{
    out_d1[i] = OCI_GetDouble(rs, 1);
    out_d2[i] = OCI_GetDouble(rs, 2);
    out_d3[i] = OCI_GetDouble(rs, 3;
    out_i1[i] = OCI_GetInt(rs, 4);

    i++;
}



On Thu, Apr 5, 2012 at 6:48 AM, <mod...@co...> wrote:

> Hi, I've been successfully using OCILIB to access PL/SQL stored procedures
> in Oracle 11.2 database from C program. Now I need to download a lot of
> array data, so I thought I'd use bulk operations for a fast download. Since
> I know my connection is good, I must be getting the syntax wrong in my
> code. I've tried to follow the user manual and any examples I can find
> online, but there's nothing covering my exact situation. I was hoping
> someone could give me a sanity check on my OCILIB implementation below. I'm
> using gcc compiler with -std=C89 switch.
>
>
> OCI_Connection *cn;
> OCI_Statement  *st;
>
> char report_name[60];
> double * d1, * d2, * d3;
> int * i1;
> unsigned int pts = 9; /* pts is actually determined when the program
> executes, but I set it to 9 here to simply the code for example only */
>
> ...
>
> /* allocate array memory */
> d1 = malloc( sizeof(double) * pts );
> d2 = malloc( sizeof(double) * pts );
> d3 = malloc( sizeof(double) * pts );
> i1 = malloc( sizeof(int) * pts );
>
> ...
>
> if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
>        return EXIT_FAILURE;
> cn = OCI_ConnectionCreate("<removed>", "myschema", "myuser",
> OCI_SESSION_DEFAULT);
> st = OCI_StatementCreate(cn);
>
> ...
>
> /* database table 'my_table' has columns:
>  dd1 is binary_double
>  dd2 is binary_double
>  dd3 is binary_double
>  ii1 is number(8,0)
>  report_name is varchar2(60 bytes)
>  edge is char(1 byte) */
> OCI_Prepare(st, "select dd1, dd2, dd3, ii1 into :out_d1, :out_d2, :out_d3,
> :out_i1 from my_table where report_name = :in_report_name and edge = 'a'");
> OCI_BindString(st, ":in_report_name", report_name, 60);
> OCI_BindArraySetSize(st, pts);
> OCI_BindArrayOfDoubles(st, ":out_d1", (double*) d1, 0);
> OCI_BindArrayOfDoubles(st, ":out_d2", (double*) d2, 0);
> OCI_BindArrayOfDoubles(st, ":out_d3", (double*) d3, 0);
> OCI_BindArrayOfInts(st, ":out_apron_tie", (int*) i1, 0);
> OCI_Execute(st);
>
>
> /* TEST */
> printf("\nd1[0] = %g ", d1[0]);
> printf("\nd1[1] = %g ", d1[1]);
>
>
> OCI_Cleanup();
> return EXIT_SUCCESS;
>
> Thanks for any comments
>
>
> ------------------------------------------------------------------------------
> Better than sec? Nothing is better than sec when it comes to
> monitoring Big Data applications. Try Boundary one-second
> resolution app monitoring today. Free.
> http://p.sf.net/sfu/Boundary-dev2dev
> _______________________________________________
> Orclib-users mailing list
> Orc...@li...
> https://lists.sourceforge.net/lists/listinfo/orclib-users
>



-- 
Vincent Rogier