Re: OraParameters: how to "AddTable" with more than one column?

From: George Hynes <gggeorge_at_hotmail.com>
Date: 30 Jul 2003 08:54:47 -0700
Message-ID: <2ac16616.0307300754.7a582075@posting.google.com>

Hi Jim,

I'm not sure I understand you -can you give an example w/psuedo code?

Essentially what I am trying to do is to be able to do a single "AddTable" (or some sort of equivalent method w/OO4O) and somehow associate that AddTable (or equivalent) with an array of records. Here is an example: a record could be:

first_name
last_name
age

Let's say I have an array (on the client side (VB, C++, or whatever)) of 5 of the above record, I would then pass (from the client side) an "array or records" (using OO4O), and have the PL/SQL side take this array of records in as a single parameter (my_data), such as defined (PL/SQL side):

TYPE my_record_Type IS RECORD

    first_name VARCHAR2(100),
    last_name VARCHAR(2),
    age NUMBER
);
TYPE my_Table IS TABLE OF

   my_record_Type
   INDEX BY BINARY_INTEGER; PROCEDURE update_a_record (my_data IN my__Table);

---

The problem I am finding, is there is no Oracle documentation (that I can find) that indicates the above is possible, or not possible. It would seem there should be a way to do this.

thanks,
George H.
07/30/2003 11:52am ET

"Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<OFkVa.2535$Ho3.947_at_sccrnsc03>...
> You are correct that is the correct way to do it. You could create an array
> of elements and just give them a sequentially generated name (eg "param1",
> "param2" etc.)
> Jim
>
> "George Hynes" <gggeorge_at_hotmail.com> wrote in message
> news:2ac16616.0307280741.6310bb92_at_posting.google.com...
> > Hello,
> >
> > I am using OO4O w/VB 6.x, and I can't seem to find any documentation
> > on how to pass an "AddTable" with more than 1 column. All the
> > explanations/examples I have found are similar to this:
> >
> > ...
> > OraDatabase.Parameters.Add "ArraySize", 3, ORAPARM_INPUT
> > OraDatabase.Parameters.AddTable "EMPNOS", ORAPARM_INPUT,
> > ORATYPE_NUMBER,
> > 3, 22
> > OraDatabase.Parameters.AddTable "ENAMES", ORAPARM_OUTPUT,
> > ORATYPE_VARCHAR2, 3, 10
> > Set EmpnoArray = OraDatabase.Parameters("EMPNOS")
> > Set EnameArray = OraDatabase.Parameters("ENAMES")
> >
> > 'Initialize the newly created input parameter table EMPNOS
> > EmpnoArray(0) = 7698
> >
> > EmpnoArray(1) = 7782
> > EmpnoArray(2) = 7654
> >
> > 'Execute the PLSQL package
> > OraDatabase.ExecuteSQL ("Begin Employee.GetEmpNamesInArray(:ArraySize,
> > :EMPNOS, :ENAMES); End;")
> > ...
> >
> >
> > It can be noticed the EMPNOS & ENAMES are seprate parameters, and if I
> > needed to pass another "array", I would simply add on another
> > parameter, such as EMPSOCSEC
> >
> > but this gets unwieldy with many "parameter arrays" being passed to
> > the PL/SQL procedure.
> >
> > ----
> > I am currently doing something like this (example):
> >
> > TYPE my_char_data_Table IS TABLE OF
> > VARCHAR2(100)
> > INDEX BY BINARY_INTEGER;
> >
> > TYPE my_numeric_Table IS TABLE OF
> > NUMBER
> > INDEX BY BINARY_INTEGER;
> >
> > PROCEDURE update_a_record
> > (
> > my_firstName IN my_char_data_Table,
> > my_age IN my_numeric_Table
> > );
> >
> > ----
> > What I would like to do is this (example):
> >
> > TYPE my_record_Type IS RECORD
> > firstName VARCHAR2(100),
> > age NUMBER
> > );
> >
> > TYPE my_Table IS TABLE OF
> > my_record_Type
> > INDEX BY BINARY_INTEGER;
> >
> > PROCEDURE update_a_record
> > (
> > my_data IN my__Table
> > );
> >
> > and have VB OO4O load the my_data parameter & then execute the Stored
> > Procedure.
> >
> > I can't seem to figure out how to load the my_data parameter, is this
> > not possible to do w/OO4O...?
> >
> > thanks,
> > George H.
> > 07/28/2003 11:38am ET
Received on Wed Jul 30 2003 - 10:54:47 CDT