orclib-users — Discussions/Questions about using OCILIB

[Orclib-users] possible bug in ocilib

[David N. <da...@qo...>]

Hi there,

I've found what appears to me to be a bug in ocilib - also in svn trunk.

The problem is in OCI_TypeInfoGet() - if the function fails to get a description for an object, the function returns NULL, however a list entry in con->tinfs is left there anyway - it happens like this (the line numbers below correspond to ocilib in svn trunk as of the time this email was written):

typeinfo.c:185: the list entry for the OCI_TypeInfo object is created:
	item = OCI_ListAppend(pOCILib, con->tinfs, sizeof(OCI_TypeInfo));

typeinfo.c:417: checks to see if a description was retrieved; if not, the return value is set to NULL, however the list entry is not removed
    if (res == FALSE)
    {
       OCI_TypeInfoFree2(pOCILib, typinf);
       typinf = NULL;
    }

typeinfo.c:160:  if OCI_TypeInfoGet() is called again, then the list entry with the invalid description is found and a non-NULL return value is returned:
    item = con->tinfs->head;

    /* walk along the list to find the type */

    while (item != NULL)
    {
        typinf = (OCI_TypeInfo *) item->data;
     
        if ((typinf != NULL) && (typinf->type == type))
        {
            if ((mtscasecmp(typinf->name,   obj_name  ) == 0) &&
                (mtscasecmp(typinf->schema, obj_schema) == 0))
            {
                found = TRUE;
                break;
            }
        }

        item = item->next;
    } 


my fix was to change the logic @ typeinfo:417 to remove the value from the list if the calls to get the object description failed and NULL is returned:
    if (res == FALSE)
    {
       OCI_TypeInfoFree2(pOCILib, typinf);
       /* delete item from item list */
       if (found == FALSE)
          OCI_ListRemove(pOCILib, con->tinfs, item->data);
       typinf = NULL;
    }

This scenario may not be important to some people - but it is in the way we use ocilib (as a helper library for the Oracle driver for Qore - qore.org)

unfortunately we cannot easily track ocilib trunk because we had to heavily modify ocilib to support multiple environment handles, however we try to sync upstream fixes, and will always try to feed back any issues we find to the upstream project.

thanks for making such a great library!

thanks,
David

Re: [Orclib-users] [Bulk] orclib wrapper

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

Hi Colin,

You"re welcome :)

Fell free to contact me for anything related to OCILIB and keep me up to
date with your project !

Best regards,

Vincent



On Wed, May 2, 2012 at 8:25 PM, Colin Doherty <col...@gm...>wrote:

> Hi,
>
> I'm a Pharo (smalltalk) user. The current solution (DBXTalk) for
> connecting to Oracle in Smalltalk is missing some functionality I need.
> Consequently, I have started to develop a FFI module that wraps your OCILIB
> as your library has the functionality I need. It's nearly there in terms of
> the functionality I need in the short term and I plan to release it to the
> Pharo/Squeak community in case anyone else finds it useful. Just wanted to
> check that you don't have any objections to me doing so ?
>
> Thanks
> Colin
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Orclib-users mailing list
> Orc...@li...
> https://lists.sourceforge.net/lists/listinfo/orclib-users
>
>


-- 
Vincent Rogier

[Orclib-users] orclib wrapper

[Colin D. <col...@gm...>]

Hi,

I'm a Pharo (smalltalk) user. The current solution (DBXTalk) for connecting
to Oracle in Smalltalk is missing some functionality I need. Consequently,
I have started to develop a FFI module that wraps your OCILIB as your
library has the functionality I need. It's nearly there in terms of the
functionality I need in the short term and I plan to release it to the
Pharo/Squeak community in case anyone else finds it useful. Just wanted to
check that you don't have any objections to me doing so ?

Thanks
Colin

Re: [Orclib-users] OCILIB usuage

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

Hi,

OCILIB can not requires oracle libs at compile time (if using
OCI_IMPORT_RUNTIME) but always needs oracles libs at runtime as it is an
OCI wrapper. It is indicated in the OCILIB docs and user guide

Regards,

Vincent

On Tue, Apr 17, 2012 at 6:31 AM, Rajesh Kumar <thi...@gm...> wrote:

> Hi to all,
>
>             Am using fedora based ARM processor Embedded unit. I have to
> access Oracle database through ODBC connectivity. So I configures unixodbc.
> But when am going to install OCILIB it is asking Oracle home, header files
> and libx. Is there any necessary to install Oracle client for OCILIB???
>
> --
> *
> Thanks & Regards,
> Rajesh Kumar P
> *
>
>
>
> ------------------------------------------------------------------------------
> 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

[Orclib-users] OCILIB usuage

[Rajesh K. <thi...@gm...>]

Hi to all,

            Am using fedora based ARM processor Embedded unit. I have to
access Oracle database through ODBC connectivity. So I configures unixodbc.
But when am going to install OCILIB it is asking Oracle home, header files
and libx. Is there any necessary to install Oracle client for OCILIB???

-- 
*
Thanks & Regards,
Rajesh Kumar P
*

Re: [Orclib-users] How to pass OCILIB connection to a function in C

[Michael F. <mic...@un...>]

On 2012-04-10 18:16, mod...@co... wrote:
> Nevermind... I got it working by declaring
>
> OCI_Connection *cn;
> OCI_Statement *st;
>
> in the header file. Then I declared in the main() file as global variables:
>
> OCI_Connection *cn = NULL; /* file scope, external linkage */
> OCI_Statement *st = NULL;  /* file scope, external linkage */
>
> The OCILIB connection is now seen by all functions and all files used by the program.
>
>
> ----- Original Message -----
> From: mod...@co...
> To: "vincent rogier"<vin...@ya...>
> Cc: orc...@li...
> Sent: Tuesday, April 10, 2012 8:04:29 AM
> Subject: [Orclib-users] How to pass OCILIB connection to a function in C?
>
> Hello, If I'm using OCILIB in the main function, such as:
>
> int main (void)
> {
> OCI_Connection *cn;
> OCI_Statement *st;
> ...
> OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
> OCI_EnableWarnings(TRUE);
> cn = OCI_ConnectionCreate(...);
> st = OCI_StatementCreate(cn);
> ...
> my_function(...);
> }
>
>
> When I create a new function, my_function, how do I pass the existing OCILIB connection into my_function for use? I want to avoid opening a new connection in my_function if it is not necessary.
>
> For example, I'd like to pass *cn and *st above into my_function.

i'd rather wrap those attributes into a struct being passed to the 
called function. see how it's done within icinga idoutils using ocilib.

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


-- 
DI (FH) Michael Friedrich

Vienna University Computer Center
Universitaetsstrasse 7 A-1010 Vienna, Austria

email:  mic...@un...
phone:  +43 1 4277 14359
mobile: +43 664 60277 14359
fax:    +43 1 4277 14338
web:    http://www.univie.ac.at/zid
         http://www.aco.net

Lead Icinga Core Developer
http://www.icinga.org

Re: [Orclib-users] How to pass OCILIB connection to a function in C

[<mod...@co...>]

Nevermind... I got it working by declaring 

OCI_Connection *cn; 
OCI_Statement *st; 

in the header file. Then I declared in the main() file as global variables: 

OCI_Connection *cn = NULL; /* file scope, external linkage */
OCI_Statement *st = NULL;  /* file scope, external linkage */

The OCILIB connection is now seen by all functions and all files used by the program. 


----- Original Message ----- 
From: mod...@co... 
To: "vincent rogier" <vin...@ya...> 
Cc: orc...@li... 
Sent: Tuesday, April 10, 2012 8:04:29 AM 
Subject: [Orclib-users] How to pass OCILIB connection to a function in C? 

Hello, If I'm using OCILIB in the main function, such as: 

int main (void) 
{ 
OCI_Connection *cn; 
OCI_Statement *st; 
... 
OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT); 
OCI_EnableWarnings(TRUE); 
cn = OCI_ConnectionCreate(...); 
st = OCI_StatementCreate(cn); 
... 
my_function(...); 
} 


When I create a new function, my_function, how do I pass the existing OCILIB connection into my_function for use? I want to avoid opening a new connection in my_function if it is not necessary. 

For example, I'd like to pass *cn and *st above into my_function. 



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

[Orclib-users] How to pass OCILIB connection to a function in C?

[<mod...@co...>]

Hello, If I'm using OCILIB in the main function, such as:

int main (void) 
{
    OCI_Connection *cn; 
    OCI_Statement  *st;
    ...
    OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
    OCI_EnableWarnings(TRUE); 
    cn = OCI_ConnectionCreate(...);
    st = OCI_StatementCreate(cn);
    ...
    my_function(...);
}


When I create a new function, my_function, how do I pass the existing OCILIB connection into my_function for use? I want to avoid opening a new connection in my_function if it is not necessary. 

For example, I'd like to pass *cn and *st above into my_function. 

 

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

[<mod...@co...>]

Hi Vincent, Thanks for the reply! I see your function prefers to have one flat array of characters in a contiguous memory block. I'm not able to get the code below working for some reason. It compiles fine, and executes without any errors, but nothing gets placed into the database table. However, if I run the program using gdb, then some garbage gets placed into the database (little question-mark symbols inside a diamond-shaped black background). Not sure what's going on, but I can compare the array "array_person_name" used here as an argument here,

OCI_BindArrayOfStrings(st, ":person_email", (char*) array_person_name, 100, 0 );

for working and non-working code: 

The working code declares the array sizes for the compiler (e.g. char array_person_name[3][101];). 


(gdb) p array_person_name
$1 = {"John Smith", '\000' <repeats 90 times>, "John Smith", '\000' <repeats 90 times>, "John Smith", '\000' <repeats 90 times>}

(gdb) p array_person_name[0]
$2 = "John Smith", '\000' <repeats 90 times>

(gdb) ptype array_person_name
type = char [3][101]

The non-working code (below), declares the array size at run time (e.g. char *array_person_name;).

(gdb) p array_person_name
$1 = 0x6cff10 "John Smith"

(gdb) p array_person_name[0]
$2 = 74 'J'

(gdb) ptype array_person_name
type = char *

(gdb) p *array_person_name@304
$3 = "John Smith", '\000' <repeats 91 times>, "John Smith", '\000' <repeats 91 times>, "John Smith", '\000' <repeats 91 times>


So it appears the argument "array_person_email" for OCI_BindArrayOfStrings for the working case (e.g. type=char [3][101]) is different than the non-working case (e.g. type = char *). I wonder if that may be causing a problem. If not, and the code looks good, then it must be something outside OCILIB. 

Here's the code I'm using:

#define COLSIZE 101 

OCI_Connection *cn; 
OCI_Statement *st; 
char *array_person_name; 
char person_name[COLSIZE] = ""; 

... 

/* create connection and statement */ 

OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT); 
OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT); 
st = OCI_StatementCreate(cn); 

/* binding */ 

OCI_Prepare(st, "INSERT INTO my_table values( :person_name )"); 
OCI_BindArraySetSize(st, 3); 
OCI_BindArrayOfStrings(st, ":person_name", (char*) array_person_name, 100, 0 ); 

/* filling arrays */ 

strncpy(person_name, "John Smith", COLSIZE); 
array_person_name = malloc(3 * COLSIZE); 

char *p = array_person_name; /* I get an error if this is inside the for() parenthesis.*/
for( i=0;i<3;i++ ) /* try 3 rows */ 
{ 
strncpy(p, person_name, 101 ); 
p+=COLSIZE; 
} 

if (!OCI_Execute(st)) { 
exit(41); 
} 

if (!OCI_Commit(cn)) { 
exit(100); 
} 
OCI_Cleanup(); 
return EXIT_SUCCESS;


----- Original Message ----- 
From: "vincent rogier" <vin...@ya...> 
To: mod...@co... 
Cc: orc...@li... 
Sent: Sunday, April 8, 2012 10:22:26 AM 
Subject: Re: [Bulk] Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

Hi, the solution is easy ! 


#define COLSIZE 101 

OCI_Connection *cn; 
OCI_Statement *st; 
char *array_person_name; 
char person_name[COLSIZE] = ""; 

... 

/* create connection and statement */ 

OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT); 
OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT); 
st = OCI_StatementCreate(cn); 

/* binding */ 

OCI_Prepare(st, "INSERT INTO my_table values( :person_name )"); 
OCI_BindArraySetSize(st, 3); 
OCI_BindArrayOfStrings(st, ":person_name", (char*) array_person_name, 100, 0 ); 

/* filling arrays */ 

strncpy(person_name, "John Smith", strlen("John Smith")+1 ); 
array_person_name = malloc(3 * COLSIZE); /* allocate memory for row pointers */ 
for(char *p =array_person_name; i=0;i<3;i++) /* try 3 rows */ 
{ 
strncpy(p, person_name, 100 ); 
p+=COLSIZE; 
} 

if (!OCI_Execute(st)) { 
exit(41); 
} 

if (!OCI_Commit(cn)) { 
exit(100); 
} 
OCI_Cleanup(); 
return EXIT_SUCCESS; 


On Sun, Apr 8, 2012 at 6:59 PM, < mod...@co... > wrote: 


Hi Vincent, 

I'm following the "Array Interface Example" here, 

http://orclib.sourceforge.net/doc/html/group__g__bind.html 

except the length of my array is not known at compile time. Rather, it is downloaded from the database when the program is run. I've created the following example code to see if I can get this working: 

OCI_Connection *cn; 
OCI_Statement *st; 
char **array_person_name; 
char person_name[101] = ""; 
... 

/* create connection and statement */ 

OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT); 
OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT); 
st = OCI_StatementCreate(cn); 

/* binding */ 

OCI_Prepare(st, "INSERT INTO my_table values( :person_name )"); 
OCI_BindArraySetSize(st, 3); 
OCI_BindArrayOfStrings(st, ":person_name", (char*) array_person_name, 100, 0 ); 

/* filling arrays */ 

strncpy(person_name, "John Smith", strlen("John Smith")+1 ); 
array_person_name = malloc(3 * sizeof(char*)); /* allocate memory for row pointers */ 
for(i=0;i<3;i++) /* try 3 rows */ 
{ 
array_person_name[i] = malloc(101 * sizeof(char)); /* allocate memory for columns */ 
strncpy(array_person_name[i], person_name, 100 ); 
} 

if (!OCI_Execute(st)) { 
exit(41); 
} 

if (!OCI_Commit(cn)) { 
exit(100); 
} 
OCI_Cleanup(); 
return EXIT_SUCCESS; 


Running the code causes the program to exit 41 (the OCI_Execute statement fails). Any idea what I'm doing wrong? In your example you use one contiguous buffer to store the array, whereas in my code above, because the arrays are allocated at run time, the array contents are not stored in one contiguous buffer (is this a problem for your function?). I can't think of any other difference between our approaches. 

If I instead declare the array size at runtime as you do in your example code, everything works fine. 

Note that the table my_table in the database only contains one column, which is named "person_name" and is varchar2(100). 

Thanks in advance for any comments. 






----- Original Message ----- 
From: "vincent rogier" < vin...@ya... > 
To: mod...@co... 
Cc: orc...@li... 


Sent: Thursday, April 5, 2012 9:57:58 AM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

hi, 

yes, what you want to perform is shown on the example " Array interface Example" . That is the best way to do it. 

regards, 

vincent 


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


Thanks Vincent, 

It works perfect now! I learned that I could remove the "into :out_d1, :out_d2, :out_d3, :out_i1" from the OCI_Prepare statement (since those binded variables aren't actually used anywhere). The code now looks like: 

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 

i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

This downloads multiple rows at a time and stores them into C variables. How would one do the reverse? That is, how to INSERT multiple rows into a database table. For example, I have arrays (each array contains "pts" number of elements) for 

C_report_name 
C_edge 
d1 
d2 
d3 
i1 

corresponding to columns in "my_table" named 

report_name 
edge 
dd1 
dd2 
dd3 
ii1 

What would an example code look like? I didn't see an OCI_SetDouble statement exists to use a similar architecture but the other way around. Are bulk operations the only way to do this? -- as shown in the "Array Interface Example" section here: 

http://orclib.sourceforge.net/doc/html/group__g__bind.html 

(e.g. arrays for tab_int and tab_str). Is this the right approach? 

How would it change if I wanted to use a stored procedure (saved in the schema) instead of a SQL command in the OCI_Prepare statement? 

Whatever hints you can provide would be extremely useful. Thanks again 





----- Original Message ----- 
From: "vincent rogier" < vin...@ya... > 
To: mod...@co... 
Cc: orc...@li... 
Sent: Wednesday, April 4, 2012 11:41:54 PM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

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 



-- 
Vincent Rogier 



-- 
Vincent Rogier 

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

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

Hi, the solution is easy !


#define COLSIZE 101

OCI_Connection *cn;
OCI_Statement  *st;
char *array_person_name;
char person_name[COLSIZE] = "";

...

/* create connection and statement */

OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);

/* binding */

OCI_Prepare(st, "INSERT INTO my_table values( :person_name )");
OCI_BindArraySetSize(st, 3);
OCI_BindArrayOfStrings(st, ":person_name", (char*) array_person_name, 100,
0 );

/* filling arrays */

strncpy(person_name, "John Smith", strlen("John Smith")+1 );
array_person_name = malloc(3 * COLSIZE); /* allocate memory for row
pointers */
for(char *p =array_person_name; i=0;i<3;i++) /* try 3 rows */
{
strncpy(p, person_name, 100 );
p+=COLSIZE;
}

if (!OCI_Execute(st)) {
  exit(41);
}

if (!OCI_Commit(cn)) {
  exit(100);
}
OCI_Cleanup();
return EXIT_SUCCESS;

On Sun, Apr 8, 2012 at 6:59 PM, <mod...@co...> wrote:

> Hi Vincent,
>
> I'm following the "Array Interface Example" here,
>
> http://orclib.sourceforge.net/doc/html/group__g__bind.html
>
> except the length of my array is not known at compile time. Rather, it is
> downloaded from the database when the program is run. I've created the
> following example code to see if I can get this working:
>
> OCI_Connection *cn;
> OCI_Statement  *st;
> char **array_person_name;
> char person_name[101] = "";
> ...
>
> /* create connection and statement */
>
> OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
> OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT);
> st = OCI_StatementCreate(cn);
>
> /* binding */
>
> OCI_Prepare(st, "INSERT INTO my_table values( :person_name )");
> OCI_BindArraySetSize(st, 3);
> OCI_BindArrayOfStrings(st, ":person_name", (char*) array_person_name, 100,
> 0 );
>
> /* filling arrays */
>
> strncpy(person_name, "John Smith", strlen("John Smith")+1 );
> array_person_name = malloc(3 * sizeof(char*)); /* allocate memory for row
> pointers */
> for(i=0;i<3;i++) /* try 3 rows */
> {
>  array_person_name[i] = malloc(101 * sizeof(char)); /* allocate memory for
> columns */
>  strncpy(array_person_name[i], person_name, 100 );
> }
>
> if (!OCI_Execute(st)) {
>   exit(41);
> }
>
> if (!OCI_Commit(cn)) {
>   exit(100);
> }
> OCI_Cleanup();
> return EXIT_SUCCESS;
>
>
> Running the code causes the program to exit 41 (the OCI_Execute statement
> fails). Any idea what I'm doing wrong? In your example you use one
> contiguous buffer to store the array, whereas in my code above, because the
> arrays are allocated at run time, the array contents are not stored in one
> contiguous buffer (is this a problem for your function?). I can't think of
> any other difference between our approaches.
>
> If I instead declare the array size at runtime as you do in your example
> code, everything works fine.
>
> Note that the table my_table in the database only contains one column,
> which is named "person_name" and is varchar2(100).
>
> Thanks in advance for any comments.
>
>
>
>
>
> ----- Original Message -----
> From: "vincent rogier" <vin...@ya...>
> To: mod...@co...
> Cc: orc...@li...
> Sent: Thursday, April 5, 2012 9:57:58 AM
> Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk
> operations
>
> hi,
>
> yes, what you want to perform is shown on the example " Array interface
> Example" . That is the best way to do it.
>
> regards,
>
> vincent
>
>
> On Thu, Apr 5, 2012 at 6:18 PM, < mod...@co... > wrote:
>
>
> Thanks Vincent,
>
> It works perfect now! I learned that I could remove the "into :out_d1,
> :out_d2, :out_d3, :out_i1" from the OCI_Prepare statement (since those
> binded variables aren't actually used anywhere). The code now looks like:
>
> OCI_SetFetchSize(st, pts);
> OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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))
> {
> d1[i] = OCI_GetDouble(rs, 1);
> d2[i] = OCI_GetDouble(rs, 2);
> d3[i] = OCI_GetDouble(rs, 3);
>
> i1[i] = OCI_GetInt(rs, 4);
> i++;
> }
>
> This downloads multiple rows at a time and stores them into C variables.
> How would one do the reverse? That is, how to INSERT multiple rows into a
> database table. For example, I have arrays (each array contains "pts"
> number of elements) for
>
> C_report_name
> C_edge
> d1
> d2
> d3
> i1
>
> corresponding to columns in "my_table" named
>
> report_name
> edge
> dd1
> dd2
> dd3
> ii1
>
> What would an example code look like? I didn't see an OCI_SetDouble
> statement exists to use a similar architecture but the other way around.
> Are bulk operations the only way to do this? -- as shown in the "Array
> Interface Example" section here:
>
> http://orclib.sourceforge.net/doc/html/group__g__bind.html
>
> (e.g. arrays for tab_int and tab_str). Is this the right approach?
>
> How would it change if I wanted to use a stored procedure (saved in the
> schema) instead of a SQL command in the OCI_Prepare statement?
>
> Whatever hints you can provide would be extremely useful. Thanks again
>
>
>
>
>
> ----- Original Message -----
> From: "vincent rogier" < vin...@ya... >
> To: mod...@co...
> Cc: orc...@li...
> Sent: Wednesday, April 4, 2012 11:41:54 PM
> Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk
> operations
>
> 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
>
>
>
> --
> Vincent Rogier
>



-- 
Vincent Rogier

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

[<mod...@co...>]

Hi Vincent,

I'm following the "Array Interface Example" here, 

http://orclib.sourceforge.net/doc/html/group__g__bind.html

except the length of my array is not known at compile time. Rather, it is downloaded from the database when the program is run. I've created the following example code to see if I can get this working:

OCI_Connection *cn; 
OCI_Statement  *st;
char **array_person_name;
char person_name[101] = "";
...

/* create connection and statement */

OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);

/* binding */

OCI_Prepare(st, "INSERT INTO my_table values( :person_name )");
OCI_BindArraySetSize(st, 3);
OCI_BindArrayOfStrings(st, ":person_name", (char*) array_person_name, 100, 0 );

/* filling arrays */

strncpy(person_name, "John Smith", strlen("John Smith")+1 );
array_person_name = malloc(3 * sizeof(char*)); /* allocate memory for row pointers */
for(i=0;i<3;i++) /* try 3 rows */
{
  array_person_name[i] = malloc(101 * sizeof(char)); /* allocate memory for columns */
  strncpy(array_person_name[i], person_name, 100 );
}

if (!OCI_Execute(st)) {
   exit(41);
}

if (!OCI_Commit(cn)) {
   exit(100);
}
OCI_Cleanup();  
return EXIT_SUCCESS;


Running the code causes the program to exit 41 (the OCI_Execute statement fails). Any idea what I'm doing wrong? In your example you use one contiguous buffer to store the array, whereas in my code above, because the arrays are allocated at run time, the array contents are not stored in one contiguous buffer (is this a problem for your function?). I can't think of any other difference between our approaches. 

If I instead declare the array size at runtime as you do in your example code, everything works fine.

Note that the table my_table in the database only contains one column, which is named "person_name" and is varchar2(100). 

Thanks in advance for any comments.





----- Original Message ----- 
From: "vincent rogier" <vin...@ya...> 
To: mod...@co... 
Cc: orc...@li... 
Sent: Thursday, April 5, 2012 9:57:58 AM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

hi, 

yes, what you want to perform is shown on the example " Array interface Example" . That is the best way to do it. 

regards, 

vincent 


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


Thanks Vincent, 

It works perfect now! I learned that I could remove the "into :out_d1, :out_d2, :out_d3, :out_i1" from the OCI_Prepare statement (since those binded variables aren't actually used anywhere). The code now looks like: 

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 

i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

This downloads multiple rows at a time and stores them into C variables. How would one do the reverse? That is, how to INSERT multiple rows into a database table. For example, I have arrays (each array contains "pts" number of elements) for 

C_report_name 
C_edge 
d1 
d2 
d3 
i1 

corresponding to columns in "my_table" named 

report_name 
edge 
dd1 
dd2 
dd3 
ii1 

What would an example code look like? I didn't see an OCI_SetDouble statement exists to use a similar architecture but the other way around. Are bulk operations the only way to do this? -- as shown in the "Array Interface Example" section here: 

http://orclib.sourceforge.net/doc/html/group__g__bind.html 

(e.g. arrays for tab_int and tab_str). Is this the right approach? 

How would it change if I wanted to use a stored procedure (saved in the schema) instead of a SQL command in the OCI_Prepare statement? 

Whatever hints you can provide would be extremely useful. Thanks again 





----- Original Message ----- 
From: "vincent rogier" < vin...@ya... > 
To: mod...@co... 
Cc: orc...@li... 
Sent: Wednesday, April 4, 2012 11:41:54 PM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

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 



-- 
Vincent Rogier 

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

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

hi,

all string and characters columns are mapped to C null terminated strings.
That's why your variable 'a' must be 2 bytes long.

On Fri, Apr 6, 2012 at 5:36 PM, <mod...@co...> wrote:

> Hi Vincent,
>
> I think my code below is correct but for reasons not shown, there were
> some memory problems. I've got it working now, but in the process I made an
> interesting observation that I wanted to share with you. I've got both
> methods A and B below working.
>
> Method A (sql statement)
>
> OCI_SetFetchSize(st, pts);
> OCI_Prepare(st, "select dd1, ii1 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)) {
> d1[i] = OCI_GetDouble(rs, 1);
> i1[i] = OCI_GetInt(rs, 2);
> i++; }
>
> Method B (stored procedure)
>
> OCI_SetFetchSize(st, pts);
> OCI_Prepare(st, "begin get_data( :in_report_name, 'a', :out_resultset );
> end;");
> OCI_BindString(st, ":in_report_name", report_name, 60);
> OCI_BindStatement(st, ":out_resultset", st2);
> OCI_Execute(st);
> rs = OCI_GetResultset(st2);
> while (OCI_FetchNext(rs)) {
> d1[i] = OCI_GetDouble(rs, 1);
> i1[i] = OCI_GetInt(rs, 2);
> i++; }
>
> One of the ways I got my code working, by trial and error, was to use 'a'
> inside the OCI_Prepare statement. Originally, I attempted to bind an edge
> parameter ":in_edge" using (Method A):
>
> char edge[1] = "";
> ...
> strncpy(edge, "a", 1);
> OCI_Prepare(st, "select dd1, ii1 from my_table where report_name =
> :in_report_name and edge = :in_edge");
> OCI_BindString(st, ":in_edge", edge, 1);
>
> or (Method B):
>
> char edge[1] = "";
> ...
> strncpy(edge, "a", 1);
> OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset
> ); end;");
> OCI_BindString(st, ":in_edge", edge, 1);
>
> However, this did not work, even though I could use a debugger and verify
> edge="a" at the OCI_Prepare statement. Interestingly, if I declared the
> edge variable as having a length of 2, the code worked. That is, declaring
> edge as:
>
> char edge[2] = "";
>
> made the code work. Note the database table's column for "edge" is
> declared as char (1 byte). I also tried varchar2 (1 byte) with the same
> result.
>
> Anyway I thought that was interesting, but maybe you already knew. Looking
> at this link:
>
>
> http://orclib.sourceforge.net/doc/html/group__g__bind.html#ga49988b9ca8f00b55cc3e62ca2385778b
>
> in the "OCI_BindString" section, it suggests this string has a "zero null
> terminal character" that this second byte may be needed for. If true,
> perhaps this means when I declare the string length in C, I should add one
> additional byte (e.g. character) for OCILIB to use for this zero null
> terminal character.
>
> Thanks again for all of your wonderful support. OCILIB has really been a
> joy to use!
>
>
>
> ----- Original Message -----
> From: mod...@co...
> To: "vincent rogier" <vin...@ya...>
> Cc: orc...@li...
> Sent: Thursday, April 5, 2012 2:52:37 PM
> Subject: Re: [Orclib-users] [Bulk] newbie question on implementing bulk
> operations
>
> Hi Vincent, I tried to follow this link:
>
> http://sourceforge.net/projects/orclib/forums/forum/470801/topic/3406215
>
> to answer my question below. I *think* the required code (see previous
> question below) should look like:
>
>
> OCI_Connection *cn;
> OCI_Statement *st;
> OCI_Statement *st2;
> OCI_Resultset *rs;
>
> ...
>
> if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
> return EXIT_FAILURE;
>
> ...
>
> cn = OCI_ConnectionCreate("<db>", "myschema", "myuser",
> OCI_SESSION_DEFAULT);
> st = OCI_StatementCreate(cn);
> st2 = OCI_StatementCreate(cn);
>
> OCI_SetFetchSize(st, pts);
> OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset
> ); end;");
> OCI_BindString(st, ":in_report_name", report_name, 60);
> OCI_BindString(st, ":in_edge", edge, 1);
> OCI_BindStatement(st, ":out_resultset", st2);
> OCI_Execute(st);
>
> rs = OCI_GetResultset(st2);
>
> while (OCI_FetchNext(rs))
> {
> d1[i] = OCI_GetDouble(rs, 1);
> d2[i] = OCI_GetDouble(rs, 2);
> d3[i] = OCI_GetDouble(rs, 3);
> i1[i] = OCI_GetInt(rs, 4);
> i++;
> }
>
> However, I run into some funny issues when I place it inside my main
> program to run it. Here's the structure of OCILIB calls in my program (I
> execute 3 processes below):
>
>
> OCI_Connection *cn;
> OCI_Statement *st;
> OCI_Statement *st2;
> OCI_Resultset *rs;
>
> ...
>
> if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
> return EXIT_FAILURE;
>
> cn = OCI_ConnectionCreate("<db>", "myschema", "myuser",
> OCI_SESSION_DEFAULT);
> st = OCI_StatementCreate(cn);
> st2 = OCI_StatementCreate(cn);
>
> OCI_Prepare(st, ...);
> OCI_Bindxxxx(st, ...);
> OCI_Execute(st, ...);
>
> ...
>
> OCI_Prepare(st, ...); /* this is a different prepare stmt than used above
> */
> OCI_Bindxxxx(st, ...);
> OCI_Execute(st, ...);
>
> ...
>
> OCI_SetFetchSize(st, pts);
> OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset
> ); end;");
> OCI_BindString(st, ":in_report_name", report_name, 60);
> OCI_BindString(st, ":in_edge", edge, 1);
> OCI_BindStatement(st, ":out_resultset", st2);
> OCI_Execute(st);
>
> rs = OCI_GetResultset(st2);
>
> while (OCI_FetchNext(rs))
> {
> d1[i] = OCI_GetDouble(rs, 1);
> d2[i] = OCI_GetDouble(rs, 2);
> d3[i] = OCI_GetDouble(rs, 3);
> i1[i] = OCI_GetInt(rs, 4);
> i++;
> }
>
> printf("\nd1[0] = %g ", d1[0]);
>
> ...
>
> OCI_Cleanup();
> return EXIT_SUCCESS;
>
>
> OCI_Execute executes three times here.
>
> If I run the program, the first OCI_Execute process runs fine, but the
> SECOND OCI_Execute process does not return the correct values from the
> database, and the THIRD OCI_Execute process appears not to run (the printf
> statement doesn't print anything).
>
> However, if I comment out the line of code for "OCI_Statement *st2;", and
> also comment out the lines of code from "OCI_SetFetchSize(st, pts);" to
> "printf(...);" and then run the program, the first two OCI_Execute
> processes run fine.
>
> At this point, if I simply remove the comment from the line "OCI_Statement
> *st2;" (keeping the THIRD OCI_Execute related lines of code commented out),
> and run the program, the FIRST OCI_Execute process runs fine but the SECOND
> OCI_Execute process does not return the correct values from the database.
> So, simply having that "OCI_Statement *st2;" line of code there seems to
> cause programs, even without any code related to the THIRD OCI_Execute
> process.
>
> Am I arranging the calls correctly here? Do you see anything that looks
> wrong? Thanks for any comments.
>
>
> ----- Original Message -----
> From: mod...@co...
> To: "vincent rogier" <vin...@ya...>
> Cc: orc...@li...
> Sent: Thursday, April 5, 2012 10:53:16 AM
> Subject: Re: [Orclib-users] [Bulk] newbie question on implementing bulk
> operations
>
> Hi Vincent, if I want to re-write this successful code
>
>
> OCI_SetFetchSize(st, pts);
> OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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))
> {
> d1[i] = OCI_GetDouble(rs, 1);
> d2[i] = OCI_GetDouble(rs, 2);
> d3[i] = OCI_GetDouble(rs, 3);
> i1[i] = OCI_GetInt(rs, 4);
> i++;
> }
>
>
> to call a stored procedure in the OCI_Prepare statement, how would I do
> it? I've tried the following without success:
>
>
> OCI_SetFetchSize(st, pts);
> OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset
> ); end;");
> OCI_BindString(st, ":in_report_name", report_name, 60);
> OCI_BindString(st, ":in_edge", edge, 1);
> OCI_BindRef(st, ":out_resultset", rs); /* I also tried OCI_BindObject,
> OCI_BindStatement, etc. */
> OCI_Execute(st);
>
> rs = OCI_GetResultset(st);
>
> while (OCI_FetchNext(rs))
> {
> d1[i] = OCI_GetDouble(rs, 1);
> d2[i] = OCI_GetDouble(rs, 2);
> d3[i] = OCI_GetDouble(rs, 3);
> i1[i] = OCI_GetInt(rs, 4);
> i++;
> }
>
> Thanks for any comments!
>
>
> ----- Original Message -----
> From: "vincent rogier" <vin...@ya...>
> To: mod...@co...
> Cc: orc...@li...
> Sent: Wednesday, April 4, 2012 11:41:54 PM
> Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk
> operations
>
> 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
>
>
> ------------------------------------------------------------------------------
> 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
>
>
> ------------------------------------------------------------------------------
> 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

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

[<mod...@co...>]

Hi Vincent, 

I think my code below is correct but for reasons not shown, there were some memory problems. I've got it working now, but in the process I made an interesting observation that I wanted to share with you. I've got both methods A and B below working. 

Method A (sql statement) 

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "select dd1, ii1 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)) { 
d1[i] = OCI_GetDouble(rs, 1); 
i1[i] = OCI_GetInt(rs, 2); 
i++; } 

Method B (stored procedure) 

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "begin get_data( :in_report_name, 'a', :out_resultset ); end;"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_BindStatement(st, ":out_resultset", st2); 
OCI_Execute(st); 
rs = OCI_GetResultset(st2); 
while (OCI_FetchNext(rs)) { 
d1[i] = OCI_GetDouble(rs, 1); 
i1[i] = OCI_GetInt(rs, 2); 
i++; } 

One of the ways I got my code working, by trial and error, was to use 'a' inside the OCI_Prepare statement. Originally, I attempted to bind an edge parameter ":in_edge" using (Method A): 

char edge[1] = ""; 
... 
strncpy(edge, "a", 1); 
OCI_Prepare(st, "select dd1, ii1 from my_table where report_name = :in_report_name and edge = :in_edge"); 
OCI_BindString(st, ":in_edge", edge, 1); 

or (Method B): 

char edge[1] = ""; 
... 
strncpy(edge, "a", 1); 
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;"); 
OCI_BindString(st, ":in_edge", edge, 1); 

However, this did not work, even though I could use a debugger and verify edge="a" at the OCI_Prepare statement. Interestingly, if I declared the edge variable as having a length of 2, the code worked. That is, declaring edge as: 

char edge[2] = ""; 

made the code work. Note the database table's column for "edge" is declared as char (1 byte). I also tried varchar2 (1 byte) with the same result. 

Anyway I thought that was interesting, but maybe you already knew. Looking at this link: 

http://orclib.sourceforge.net/doc/html/group__g__bind.html#ga49988b9ca8f00b55cc3e62ca2385778b 

in the "OCI_BindString" section, it suggests this string has a "zero null terminal character" that this second byte may be needed for. If true, perhaps this means when I declare the string length in C, I should add one additional byte (e.g. character) for OCILIB to use for this zero null terminal character. 

Thanks again for all of your wonderful support. OCILIB has really been a joy to use! 



----- Original Message ----- 
From: mod...@co... 
To: "vincent rogier" <vin...@ya...> 
Cc: orc...@li... 
Sent: Thursday, April 5, 2012 2:52:37 PM 
Subject: Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations 

Hi Vincent, I tried to follow this link: 

http://sourceforge.net/projects/orclib/forums/forum/470801/topic/3406215 

to answer my question below. I *think* the required code (see previous question below) should look like: 


OCI_Connection *cn; 
OCI_Statement *st; 
OCI_Statement *st2; 
OCI_Resultset *rs; 

... 

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) 
return EXIT_FAILURE; 

... 

cn = OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT); 
st = OCI_StatementCreate(cn); 
st2 = OCI_StatementCreate(cn); 

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_BindString(st, ":in_edge", edge, 1); 
OCI_BindStatement(st, ":out_resultset", st2); 
OCI_Execute(st); 

rs = OCI_GetResultset(st2); 

while (OCI_FetchNext(rs)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 
i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

However, I run into some funny issues when I place it inside my main program to run it. Here's the structure of OCILIB calls in my program (I execute 3 processes below): 


OCI_Connection *cn; 
OCI_Statement *st; 
OCI_Statement *st2; 
OCI_Resultset *rs; 

... 

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) 
return EXIT_FAILURE; 

cn = OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT); 
st = OCI_StatementCreate(cn); 
st2 = OCI_StatementCreate(cn); 

OCI_Prepare(st, ...); 
OCI_Bindxxxx(st, ...); 
OCI_Execute(st, ...); 

... 

OCI_Prepare(st, ...); /* this is a different prepare stmt than used above */ 
OCI_Bindxxxx(st, ...); 
OCI_Execute(st, ...); 

... 

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_BindString(st, ":in_edge", edge, 1); 
OCI_BindStatement(st, ":out_resultset", st2); 
OCI_Execute(st); 

rs = OCI_GetResultset(st2); 

while (OCI_FetchNext(rs)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 
i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

printf("\nd1[0] = %g ", d1[0]); 

... 

OCI_Cleanup(); 
return EXIT_SUCCESS; 


OCI_Execute executes three times here. 

If I run the program, the first OCI_Execute process runs fine, but the SECOND OCI_Execute process does not return the correct values from the database, and the THIRD OCI_Execute process appears not to run (the printf statement doesn't print anything). 

However, if I comment out the line of code for "OCI_Statement *st2;", and also comment out the lines of code from "OCI_SetFetchSize(st, pts);" to "printf(...);" and then run the program, the first two OCI_Execute processes run fine. 

At this point, if I simply remove the comment from the line "OCI_Statement *st2;" (keeping the THIRD OCI_Execute related lines of code commented out), and run the program, the FIRST OCI_Execute process runs fine but the SECOND OCI_Execute process does not return the correct values from the database. So, simply having that "OCI_Statement *st2;" line of code there seems to cause programs, even without any code related to the THIRD OCI_Execute process. 

Am I arranging the calls correctly here? Do you see anything that looks wrong? Thanks for any comments. 


----- Original Message ----- 
From: mod...@co... 
To: "vincent rogier" <vin...@ya...> 
Cc: orc...@li... 
Sent: Thursday, April 5, 2012 10:53:16 AM 
Subject: Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations 

Hi Vincent, if I want to re-write this successful code 


OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 
i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 


to call a stored procedure in the OCI_Prepare statement, how would I do it? I've tried the following without success: 


OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_BindString(st, ":in_edge", edge, 1); 
OCI_BindRef(st, ":out_resultset", rs); /* I also tried OCI_BindObject, OCI_BindStatement, etc. */ 
OCI_Execute(st); 

rs = OCI_GetResultset(st); 

while (OCI_FetchNext(rs)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 
i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

Thanks for any comments! 


----- Original Message ----- 
From: "vincent rogier" <vin...@ya...> 
To: mod...@co... 
Cc: orc...@li... 
Sent: Wednesday, April 4, 2012 11:41:54 PM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

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 

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

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

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

[<mod...@co...>]

Hi Vincent, I tried to follow this link:

http://sourceforge.net/projects/orclib/forums/forum/470801/topic/3406215

to answer my question below. I *think* the required code (see previous question below) should look like:


OCI_Connection *cn; 
OCI_Statement  *st;
OCI_Statement  *st2;
OCI_Resultset  *rs;

...

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
    return EXIT_FAILURE;

...

cn = OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn); 
st2 = OCI_StatementCreate(cn);

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_BindString(st, ":in_edge", edge, 1); 
OCI_BindStatement(st, ":out_resultset", st2);  
OCI_Execute(st); 

rs = OCI_GetResultset(st2); 

while (OCI_FetchNext(rs)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 
i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

However, I run into some funny issues when I place it inside my main program to run it. Here's the structure of OCILIB calls in my program (I execute 3 processes below):


OCI_Connection *cn; 
OCI_Statement  *st;
OCI_Statement  *st2;
OCI_Resultset  *rs;

...

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
    return EXIT_FAILURE;

cn = OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn); 
st2 = OCI_StatementCreate(cn);

OCI_Prepare(st, ...);
OCI_Bindxxxx(st, ...);
OCI_Execute(st, ...);

...

OCI_Prepare(st, ...); /* this is a different prepare stmt than used above */
OCI_Bindxxxx(st, ...);
OCI_Execute(st, ...);

...

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_BindString(st, ":in_edge", edge, 1); 
OCI_BindStatement(st, ":out_resultset", st2);  
OCI_Execute(st); 

rs = OCI_GetResultset(st2); 

while (OCI_FetchNext(rs)) 
{ 
    d1[i] = OCI_GetDouble(rs, 1); 
    d2[i] = OCI_GetDouble(rs, 2); 
    d3[i] = OCI_GetDouble(rs, 3); 
    i1[i] = OCI_GetInt(rs, 4); 
    i++; 
} 

printf("\nd1[0] = %g ", d1[0]);

...

OCI_Cleanup();
return EXIT_SUCCESS;


OCI_Execute executes three times here. 

If I run the program, the first OCI_Execute process runs fine, but the SECOND OCI_Execute process does not return the correct values from the database, and the THIRD OCI_Execute process appears not to run (the printf statement doesn't print anything). 

However, if I comment out the line of code for "OCI_Statement  *st2;", and also comment out the lines of code from "OCI_SetFetchSize(st, pts);" to "printf(...);" and then run the program, the first two OCI_Execute processes run fine. 

At this point, if I simply remove the comment from the line "OCI_Statement *st2;" (keeping the THIRD OCI_Execute related lines of code commented out), and run the program, the FIRST OCI_Execute process runs fine but the SECOND OCI_Execute process does not return the correct values from the database. So, simply having that "OCI_Statement *st2;" line of code there seems to cause programs, even without any code related to the THIRD OCI_Execute process. 

Am I arranging the calls correctly here? Do you see anything that looks wrong? Thanks for any comments.


----- Original Message ----- 
From: mod...@co... 
To: "vincent rogier" <vin...@ya...> 
Cc: orc...@li... 
Sent: Thursday, April 5, 2012 10:53:16 AM 
Subject: Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations 

Hi Vincent, if I want to re-write this successful code 


OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 
i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 


to call a stored procedure in the OCI_Prepare statement, how would I do it? I've tried the following without success: 


OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_BindString(st, ":in_edge", edge, 1); 
OCI_BindRef(st, ":out_resultset", rs); /* I also tried OCI_BindObject, OCI_BindStatement, etc. */ 
OCI_Execute(st); 

rs = OCI_GetResultset(st); 

while (OCI_FetchNext(rs)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 
i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

Thanks for any comments! 


----- Original Message ----- 
From: "vincent rogier" <vin...@ya...> 
To: mod...@co... 
Cc: orc...@li... 
Sent: Wednesday, April 4, 2012 11:41:54 PM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

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 

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

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

[<mod...@co...>]

Hi Vincent, if I want to re-write this successful code 


OCI_SetFetchSize(st, pts);
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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))
{
d1[i] = OCI_GetDouble(rs, 1);
d2[i] = OCI_GetDouble(rs, 2);
d3[i] = OCI_GetDouble(rs, 3);
i1[i] = OCI_GetInt(rs, 4);
i++;
} 
 

to call a stored procedure in the OCI_Prepare statement, how would I do it? I've tried the following without success: 


OCI_SetFetchSize(st, pts);
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;");
OCI_BindString(st, ":in_report_name", report_name, 60);
OCI_BindString(st, ":in_edge", edge, 1);
OCI_BindRef(st, ":out_resultset", rs); /* I also tried OCI_BindObject, OCI_BindStatement, etc. */
OCI_Execute(st);

rs = OCI_GetResultset(st);

while (OCI_FetchNext(rs))
{
d1[i] = OCI_GetDouble(rs, 1);
d2[i] = OCI_GetDouble(rs, 2);
d3[i] = OCI_GetDouble(rs, 3);
i1[i] = OCI_GetInt(rs, 4);
i++;
} 

Thanks for any comments!


----- Original Message ----- 
From: "vincent rogier" <vin...@ya...> 
To: mod...@co... 
Cc: orc...@li... 
Sent: Wednesday, April 4, 2012 11:41:54 PM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

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 

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

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

hi,

yes, what you want to perform is shown on the example "*Array interface
Example"*. That is the best way to do it.

regards,

vincent

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

> Thanks Vincent,
>
> It works perfect now! I learned that I could remove the "into :out_d1,
> :out_d2, :out_d3, :out_i1" from the OCI_Prepare statement (since those
> binded variables aren't actually used anywhere). The code now looks like:
>
> OCI_SetFetchSize(st, pts);
> OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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))
> {
> d1[i] = OCI_GetDouble(rs, 1);
> d2[i] = OCI_GetDouble(rs, 2);
> d3[i] = OCI_GetDouble(rs, 3);
> i1[i] = OCI_GetInt(rs, 4);
> i++;
> }
>
> This downloads multiple rows at a time and stores them into C variables.
> How would one do the reverse? That is, how to INSERT multiple rows into a
> database table. For example, I have arrays (each array contains "pts"
> number of elements) for
>
> C_report_name
> C_edge
> d1
> d2
> d3
> i1
>
> corresponding to columns in "my_table" named
>
> report_name
> edge
> dd1
> dd2
> dd3
> ii1
>
> What would an example code look like? I didn't see an OCI_SetDouble
> statement exists to use a similar architecture but the other way around.
> Are bulk operations the only way to do this? -- as shown in the "Array
> Interface Example" section here:
>
> http://orclib.sourceforge.net/doc/html/group__g__bind.html
>
> (e.g. arrays for tab_int and tab_str). Is this the right approach?
>
> How would it change if I wanted to use a stored procedure (saved in the
> schema) instead of a SQL command in the OCI_Prepare statement?
>
> Whatever hints you can provide would be extremely useful. Thanks again
>
>
>
> ----- Original Message -----
> From: "vincent rogier" <vin...@ya...>
> To: mod...@co...
> Cc: orc...@li...
> Sent: Wednesday, April 4, 2012 11:41:54 PM
> Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk
> operations
>
> 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
>



-- 
Vincent Rogier

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

[<mod...@co...>]

Thanks Vincent, 

It works perfect now! I learned that I could remove the "into :out_d1, :out_d2, :out_d3, :out_i1" from the OCI_Prepare statement (since those binded variables aren't actually used anywhere). The code now looks like:

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 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)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2);
d3[i] = OCI_GetDouble(rs, 3);
i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

This downloads multiple rows at a time and stores them into C variables. How would one do the reverse? That is, how to INSERT multiple rows into a database table. For example, I have arrays (each array contains "pts" number of elements) for 

C_report_name
C_edge
d1
d2
d3
i1  

corresponding to columns in "my_table" named

report_name
edge
dd1
dd2
dd3
ii1

What would an example code look like? I didn't see an OCI_SetDouble statement exists to use a similar architecture but the other way around. Are bulk operations the only way to do this? -- as shown in the "Array Interface Example" section here:

http://orclib.sourceforge.net/doc/html/group__g__bind.html

(e.g. arrays for tab_int and tab_str). Is this the right approach?

How would it change if I wanted to use a stored procedure (saved in the schema) instead of a SQL command in the OCI_Prepare statement? 

Whatever hints you can provide would be extremely useful. Thanks again



----- Original Message ----- 
From: "vincent rogier" <vin...@ya...> 
To: mod...@co... 
Cc: orc...@li... 
Sent: Wednesday, April 4, 2012 11:41:54 PM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

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 

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

[Orclib-users] newbie question on implementing bulk operations

[<mod...@co...>]

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

Re: [Orclib-users] how to setup OCILIB to connect to database on local network?

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

You can pass for 'db' param of OCI_ConnectionCreate() :

- a tnsname alias (and then the full connection string from tnsnames.ora
will be used)
- an easy connection string like '//server:port/service'
- an easy connection string like 'server:port/service'


On Wed, Mar 14, 2012 at 6:27 PM, <mod...@co...> wrote:

> My application (Linux) server and database (Linux) server physically sit
> next to each other and are connected with a cross-connect cable. They are
> configured on the same local network.
>
> To speed requests between these servers when using JDBC, I have set up
> JDBC using the Connection URL:
>
> "jdbc:oracle:thin:@<hostname>:<port>:<sid>"
>
> where <hostname> is set to 127.0.0.1  (e.g. local network)
>
> Is there a way I can setup OCILIB to route requests through a local
> network similarly? Can I do this using OCI_ConnectionCreate, for example?
> Where to place the 127.0.0.1 designation?
>
>
>
> ------------------------------------------------------------------------------
> Virtualization & Cloud Management Using Capacity Planning
> Cloud computing makes use of virtualization - but cloud computing
> also focuses on allowing computing to be delivered as a service.
> http://www.accelacomm.com/jaw/sfnl/114/51521223/
> _______________________________________________
> Orclib-users mailing list
> Orc...@li...
> https://lists.sourceforge.net/lists/listinfo/orclib-users
>



-- 
Vincent Rogier

[Orclib-users] how to setup OCILIB to connect to database on local network?

[<mod...@co...>]

My application (Linux) server and database (Linux) server physically sit next to each other and are connected with a cross-connect cable. They are configured on the same local network. 

To speed requests between these servers when using JDBC, I have set up JDBC using the Connection URL:

"jdbc:oracle:thin:@<hostname>:<port>:<sid>"

where <hostname> is set to 127.0.0.1  (e.g. local network)

Is there a way I can setup OCILIB to route requests through a local network similarly? Can I do this using OCI_ConnectionCreate, for example? Where to place the 127.0.0.1 designation?

Re: [Orclib-users] newbie question on installing OCILIB on Linux (CentOS)

[<mod...@co...>]

Thank you Vincent! 

Regarding Question 4, the documentation states: "To compile native 64 bits versions of OCILIB, you need pass your compiler specifics flags to the *configure* script." 

That is, it is talking about the "configure script". 

When I manually compile, I do use GCC. I'm not exactly sure what goes on when I type configure. Is it using GCC? If so, I assume that I should add the "-m64" to the configure call as follows: 

# ./configure --with-oracle-headers-path /path/to/OCI/public/headers --with-oracle-lib-path /path/to/directory/containing/the/file/libclntsh.so -m64 

Is that what you intended in your answer? Or, did you mean I should use the -m64 when I compile my application c program, such as:

gcc -DOCI_IMPORT_LINKAGE -DOCI_CHARSET_ANSI -L$ORACLE_HOME/lib -lclntsh -L/usr/local/lib -locilib conn.c -o conn -m64

Or, perhaps, I should include the "-m64" for both cases above? Thanks for helping me clear this up.

-GKK 

----- Original Message ----- 
From: "vincent rogier" <vin...@ya...> 
To: mod...@co... 
Cc: orc...@li... 
Sent: Tuesday, March 13, 2012 12:14:37 AM 
Subject: Re: [Orclib-users] newbie question on installing OCILIB on Linux (CentOS) 

Hi, 

QUESTION 1 : Yes 
QUESTION 2 : the only disavantage might (i'm not sure) is about the charset handling. the instant client might not handle all possible charsets. 
QUESTION 3 : correct 
QUESTION 4 : if you use gcc, you might add "-m64" to the CFLAGS variable if you using 64bits Oracle shared lib 

Regards, 

Vincent 


On Tue, Mar 13, 2012 at 3:05 AM, < mod...@co... > wrote: 


Hi, I'm new to OCILIB but would like to use the latest version to connect my ANSI C programs (using GCC compiler) on a (Linux) CentOS 6.2 server to talk to an Oracle 11.2 database on a CentOS 5.6 server. 

I have some newbie questions... 

The documentation states in the Requirements section: "An Oracle client (regular or Instant) (note that the client is not required for compilation)". 

QUESTION 1: Does this mean an Oracle client (regular or instant) is ALWAYS required, even though it may not be needed for compilation? That is, an Oracle client is ALWAYS needed for the OCI libraries at the very least, right? 

QUESTION 2: Concerning OCILIB, is/are there any advantage(s) or disadvantage(s) in selecting the Regular versus Instant client? Since they both contain the OCI libraries, either should work equally well. I see in the documentation there are differences in the GNU install options (e.g. --with-oracle-headers-path and --with-oracle-lib-path are meant to be used with Instant client only, etc.), but other than that, are there any considerations I should be aware of? (My only use for the Oracle client is for OCILIB.) 

QUESTION 3: I couldn't find any examples for configure options. If I use the "Oracle Instant Client", am I understanding the installation documentation correctly by installing as follows? 

# cd /path/to/OCILIB/install/directory 
# ./configure --with-oracle-headers-path /path/to/OCI/public/headers --with-oracle-lib-path /path/to/directory/containing/the/file/libclntsh.so 
# ./make 
# su root 
# ./make install 

I need "ANSI" charset and "linkage" options, which should occur by default. 

QUESTION 4: Can someone help me understand what this means: "To compile native 64 bits versions of OCILIB, you need pass your compiler specifics flags to the configure script." 

My CentOS 6.2 (linux) server is 64 bits. Does that mean I want to compile 64 bit versions of OCILIB? (is that common practice?) If so, I didn't see any compiler specific flags documented for the configure script -- is this outside of OCILIB documentation (where can I find them, or can someone let me know what to use)? 

Thanks in advance for any help. GKK 

------------------------------------------------------------------------------ 
Keep Your Developer Skills Current with LearnDevNow! 
The most comprehensive online learning library for Microsoft developers 
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, 
Metro Style Apps, more. Free future releases when you subscribe now! 
http://p.sf.net/sfu/learndevnow-d2d 
_______________________________________________ 
Orclib-users mailing list 
Orc...@li... 
https://lists.sourceforge.net/lists/listinfo/orclib-users 



-- 
Vincent Rogier 

Re: [Orclib-users] newbie question on installing OCILIB on Linux (CentOS)

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

Hi,

QUESTION 1 : Yes
QUESTION 2 : the only disavantage might (i'm not sure) is about the charset
handling. the instant client might not handle all possible charsets.
QUESTION 3 : correct
QUESTION 4 : if you use gcc, you might add "-m64" to the CFLAGS variable if
you using  64bits Oracle shared lib

Regards,

Vincent

On Tue, Mar 13, 2012 at 3:05 AM, <mod...@co...> wrote:

> Hi, I'm new to OCILIB but would like to use the latest version to connect
> my ANSI C programs (using GCC compiler) on a (Linux) CentOS 6.2 server to
> talk to an Oracle 11.2 database on a CentOS 5.6 server.
>
> I have some newbie questions...
>
> The documentation states in the Requirements section: "An Oracle client
> (regular or Instant) (note that the client is not required for
> compilation)".
>
> QUESTION 1: Does this mean an Oracle client (regular or instant) is ALWAYS
> required, even though it may not be needed for compilation? That is, an
> Oracle client is ALWAYS needed for the OCI libraries at the very least,
> right?
>
> QUESTION 2: Concerning OCILIB, is/are there any advantage(s) or
> disadvantage(s) in selecting the Regular versus Instant client? Since they
> both contain the OCI libraries, either should work equally well. I see in
> the documentation there are differences in the GNU install options (e.g.
> --with-oracle-headers-path and --with-oracle-lib-path are meant to be used
> with Instant client only, etc.), but other than that, are there any
> considerations I should be aware of? (My only use for the Oracle client is
> for OCILIB.)
>
> QUESTION 3: I couldn't find any examples for configure options. If I use
> the "Oracle Instant Client", am I understanding the installation
> documentation correctly by installing as follows?
>
> # cd /path/to/OCILIB/install/directory
> # ./configure --with-oracle-headers-path /path/to/OCI/public/headers
> --with-oracle-lib-path /path/to/directory/containing/the/file/libclntsh.so
> # ./make
> # su root
> # ./make install
>
> I need "ANSI" charset and "linkage" options, which should occur by default.
>
> QUESTION 4: Can someone help me understand what this means: "To compile
> native 64 bits versions of OCILIB, you need pass your compiler specifics
> flags to the configure script."
>
> My CentOS 6.2 (linux) server is 64 bits. Does that mean I want to compile
> 64 bit versions of OCILIB? (is that common practice?) If so, I didn't see
> any compiler specific flags documented for the configure script -- is this
> outside of OCILIB documentation (where can I find them, or can someone let
> me know what to use)?
>
> Thanks in advance for any help. GKK
>
>
> ------------------------------------------------------------------------------
> Keep Your Developer Skills Current with LearnDevNow!
> The most comprehensive online learning library for Microsoft developers
> is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
> Metro Style Apps, more. Free future releases when you subscribe now!
> http://p.sf.net/sfu/learndevnow-d2d
> _______________________________________________
> Orclib-users mailing list
> Orc...@li...
> https://lists.sourceforge.net/lists/listinfo/orclib-users
>



-- 
Vincent Rogier

[Orclib-users] newbie question on installing OCILIB on Linux (CentOS)

[<mod...@co...>]

Hi, I'm new to OCILIB but would like to use the latest version to connect my ANSI C programs (using GCC compiler) on a (Linux) CentOS 6.2 server to talk to an Oracle 11.2 database on a CentOS 5.6 server.

I have some newbie questions... 

The documentation states in the Requirements section: "An Oracle client (regular or Instant) (note that the client is not required for compilation)".

QUESTION 1: Does this mean an Oracle client (regular or instant) is ALWAYS required, even though it may not be needed for compilation? That is, an Oracle client is ALWAYS needed for the OCI libraries at the very least, right?

QUESTION 2: Concerning OCILIB, is/are there any advantage(s) or disadvantage(s) in selecting the Regular versus Instant client? Since they both contain the OCI libraries, either should work equally well. I see in the documentation there are differences in the GNU install options (e.g. --with-oracle-headers-path and --with-oracle-lib-path are meant to be used with Instant client only, etc.), but other than that, are there any considerations I should be aware of? (My only use for the Oracle client is for OCILIB.)

QUESTION 3: I couldn't find any examples for configure options. If I use the "Oracle Instant Client", am I understanding the installation documentation correctly by installing as follows?

# cd /path/to/OCILIB/install/directory
# ./configure --with-oracle-headers-path /path/to/OCI/public/headers --with-oracle-lib-path /path/to/directory/containing/the/file/libclntsh.so
# ./make
# su root
# ./make install

I need "ANSI" charset and "linkage" options, which should occur by default.

QUESTION 4: Can someone help me understand what this means: "To compile native 64 bits versions of OCILIB, you need pass your compiler specifics flags to the configure script." 

My CentOS 6.2 (linux) server is 64 bits. Does that mean I want to compile 64 bit versions of OCILIB? (is that common practice?) If so, I didn't see any compiler specific flags documented for the configure script -- is this outside of OCILIB documentation (where can I find them, or can someone let me know what to use)?

Thanks in advance for any help. GKK

[Orclib-users] Calling a Oracle procedure with output variables

[Allan D. R. de O. <all...@gm...>]

Hello,

I've posted this question on stackoverflow:

http://stackoverflow.com/questions/7652818/calling-a-oracle-procedure-with-output-variables-using-ocilib

I would appreciate any help.

Best Regards,

Allan Douglas R. de Oliveira