Problem writing to VARRAY columns (Oracle8 OCI)

From: Stephanos Bacon <msbacon_at_rcn.com>
Date: Sun, 12 Mar 2000 09:02:06 -0500
Message-ID: <8ag82v$d07$1_at_bob.news.rcn.net>



>
>
> I am trying to solve the following problem using OCI:
>
> We have a table with one or more VARRAY columns,
> e.g.
>
> create type IDS_VA as VARRAY(1000000000) of varchar2(500)
> -- Sequence Array
>
> create type SHORTS_VA as VARRAY(1000000000) of number(3)
> -- Short Array
>
> create table test

> ID number NOT NULL,
> shorts_va_col SHORTS_VA,
> ids_va_col IDS_VA )
>
> I want to write a row (either insert or update) of data to this tab;e.
>
> I currently do something like the following. Suppose I have 2
> elements to put in shorts_va_col and 3 elements to put in
> ids_va_col. I construct a SQL statement like the following:
>
> insert into test (ID, shorts_va_col, ids_va_col) values (:1,
> shorts_va_col(:2, :3), ids_va_col(:4, :5, :6))
>
> I then use OCIBindByPos to bind the data and execute the
> statement. This works most of the time. However, if there are
> too many data elements for one or both of the arrays it
> doesn't work. Oracle reports an error that there are too many
> arguments in the SQL statements.
>
> So what I want to do is this:
>
> Construct a Sql statment like:
>
> insert into test (ID, shorts_va_col, ids_va_col) values (:1, :2, :3)
>
> Now I want to create an appropriate object (I'm guessing an
> OCIArray*), put my data elements into it, and then use
> OCIBindByPos and OCIBindObject to bind this object to the the
> variable (either :2 or :3).
>
> So my questions are:
>
> What kind of object do I want to create?
> How do I allocate it?
> How do I put the data in?
> How do I bind it?
> How do a free it?
>
> I have tried a number of things (e.g. using OCIObjectNew to
> create the object, using OCICollAppend() to put the data
> elements into it), but I haven't been able to get any of
> these to work proprerly. Executing the sql statement usually
> results in an error, no matter what I try.
>

Thanks in advance for any help,
-steph Received on Sun Mar 12 2000 - 15:02:06 CET

Original text of this message