Bind array
li...@my-deja.com
I have a table defined as:
create table test_number(col numeric(7, 2));
In OCI (8.1.5.0.0), I bind an array to the insert statement as:
begin
insert into test_number(col)
values(:col);
end;
Here is the data to be bound:
char pBuffer[]="123456789";
char buffer[128];
ub2 dType;
int cSize;
int indp[]={0, 0, 0};
ub2 alenp[]={3,3,3};
ub2 rcodep[]={0,0,0};
The bind is OK:
rc = OCIBindByPos(m_hstmt, (OCIBind **)&m_hbnd, m_herr, 1,
pBuffer, 3, SQLT_CHR, indp, alenp, rcodep, 3, (unsigned int *)&curelep,
OCI_DEFAULT);
But when I execute it:
rc = OCIStmtExecute(m_hsvc, m_hstmt, m_herr, (ub4) 3, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT |
OCI_COMMIT_ON_SUCCESS);
rc return -1 and the error is:
Error - ORA-01485: compile bind length different from execute bind
length
My questions are:
1. What is the external datatype I shoud use to bind a number datatype
in defined in Oracle?
2. When binding array, does Oracle do the datatype conversion? Looks to
me it does not.
3. Do I need to use OCIBindArrayOfStruct() or not?
4. Do I need to set curelep to 3 before I call OCIBindByPos?
If someone can show me a workable solution, I will be very appreciated.
Thanks,
--ye
Sent via Deja.com http://www.deja.com/
Before you buy.
Michael Oswald
8qboli$7a8$1...@nnrp1.deja.com...
> Hi,there.
>
>
> My questions are:
>
> 1. What is the external datatype I shoud use to bind a number datatype
> in defined in Oracle?
>
(col numeric(7, 2)) in this format: float or double
> 2. When binding array, does Oracle do the datatype conversion? Looks to
> me it does not.
>
yes it does, but with string-values it's almost more complicated.
> 3. Do I need to use OCIBindArrayOfStruct() or not?
>
for array-binds (like you want it to do) you need it.
> 4. Do I need to set curelep to 3 before I call OCIBindByPos?
>
You only need curelp if you use a PL/SQL-block like in your example. But for
a simple insert, you can do it with a normal SQL-Statement
> If someone can show me a workable solution, I will be very appreciated.
>
Try the following:
char * sql_stmt = " insert into test_number(col) values(:col)";
// values to insert
double values[] = {123, 234, 345};
char buffer[128];
ub2 dType;
int cSize;
int indp[]={0, 0, 0};
ub2 rcodep[]={0,0,0};
... prepare statement...
rc = OCIBindByPos(m_hstmt, (OCIBind **)&m_hbnd, m_herr, 1,
values, sizeof(double), SQLT_FLT, indp, 0, rcodep, 0, 0,
OCI_DEFAULT);
rc = OCIBindArrayOfStruct(m_hbnd, m_herr, sizeof(double), sizeof(int),
0,sizeof(ub2));
rc = OCIStmtExecute(m_hsvc, m_hstmt, m_herr, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT |
OCI_COMMIT_ON_SUCCESS);
I have not tried it out, cause we use some of our own C++-wrappers, but it
should work this way.
Michael
li...@my-deja.com
Hi, Michael.
Thanks. This is my finding:
To bind an array, we have to use PL/SQL statement instead of SQL
command. Can you verify this?
Oracle clients (sqlplus) can do the datatype conversion and can handle
very large number. I guess OCI should have ways to do it. What now I use
is: I treat all numbers (int, real, double, numeric) as string to
input/output into/from Oracle server. For single value, seems to be OK
but for the array I got ORA-06550 error like:
Error - ORA-06550: line 1, column 43:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored
Another question is that:
Have you tried to use OCINumber to bind or define? what is the dty I
should use (SQLT_VNU seems not work)?
Michael Oswald
Sorry for the late answer, it's a little bit stressy in the moment...
>
> Hi, Michael.
>
> Thanks. This is my finding:
>
> To bind an array, we have to use PL/SQL statement instead of SQL
> command. Can you verify this?
>
We are using OCI for our whole application and I'm using a lot of
array-defines with normal SQL-Statments. I don't use array-binds (we insert
only spare data, but query a lot) but according to the OCI-Documentation it
should work. Another point is, that we use Oracle 8.1.5. and the
PL/SQL-engine seems to be buggy (we had a longer contact with oracle-support
and they could not solve our problem and said we should upgrade), so we use
the normal SQL-engine (if possible).
>
> Oracle clients (sqlplus) can do the datatype conversion and can handle
> very large number. I guess OCI should have ways to do it. What now I use
> is: I treat all numbers (int, real, double, numeric) as string to
> input/output into/from Oracle server. For single value, seems to be OK
> but for the array I got ORA-06550 error like:
>
> Error - ORA-06550: line 1, column 43:
> PLS-00382: expression is of wrong type
> ORA-06550: line 1, column 7:
> PL/SQL: SQL Statement ignored
>
Puh, thats difficult to say. If the conversion for a single value works,
then it should work for arrays too. But lets try...
char pBuffer[]="123456789";
int indp[]={0, 0, 0};
ub2 alenp[]={3,3,3};
ub2 rcodep[]={0,0,0};
rc = OCIBindByPos(m_hstmt, (OCIBind **)&m_hbnd, m_herr, 1,
pBuffer, 3, SQLT_CHR, indp, alenp, rcodep, 3, 0,
OCI_DEFAULT);
rc = OCIBindArrayOfStruct(m_hbnd, m_herr, 3 /* length of the number as
skip-parameter*/, sizeof(int),
sizeof(ub2),sizeof(ub2));
rc = OCIStmtExecute(m_hsvc, m_hstmt, m_herr, (ub4) 3, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT |
OCI_COMMIT_ON_SUCCESS);
>
> Another question is that:
>
> Have you tried to use OCINumber to bind or define? what is the dty I
> should use (SQLT_VNU seems not work)?
>
No. OCINumber is a 21-Byte-Datatype and our application needs about 37 MBs
for startup, so we use normal int, float and double-types. The typecode
should be SQLT_NUM.
Hope this helps
Michael