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

"Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<e7aWa.20563$It4.14158_at_rwcrnsc51.ops.asp.att.net>...
> The array is a record. So you can have your 4 attributes and more than 1
> record. Think of it as an array of structures.
> Jim
>

I understand what you are saying in regards to an array or records, and thinking of it as a C structure, and I can certainly create an array of VB user defined types, but it is still unknown to me how to indicate to OO4O that my array of records will be used as a single parameter to call a PL/SQL Stored Procedure. In this scenario, (on the PL/SQL side) the single parameter will be defined as a PL/SQL Table of PL/SQL Records.

I've used the OO4O AddTable() method for doing the above, *but* only for PL/SQL tables that have 1 column --this is where I can find no OO4O example code, or any Oracle documentation indicating it is possible (it must be?) to use AddTable(), etc. to pass data to a PL/SQL table that has more than 1 column -in other words a PL/SQL Table of PL/SQL Records. It doesn't matter to me if the OO4O example/documentation is in C++, VB, etc.

> "George Hynes" <gggeorge_at_hotmail.com> wrote in message
> news:2ac16616.0307310413.575c08a5_at_posting.google.com...
> > Hi Jim,
> >
> > "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in
> message news:<2PZVa.22840$Ho3.4056_at_sccrnsc03>...
> > > That's not what I was thinking of, but its a better idea than mine, this
> is
> > > probably what you are looking for:
> > >
> http://download-west.oracle.com/docs/cd/B10501_01/win.920/a95895/o4o00046.htm
> >
> > thanks for the link, and I did check it out, but the
> > documentation/example only shows a one dimensional array. I guess if
> > I were to be able to extend the example code, somehow there would be a
> > way to make the:
> >
> > ...
> > hrDb.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, ORATYPE_VARRAY,
> > "ENAMELIST"
> > ...
> >
> > take on an array of "records" ("multi-dimensional array"), such as
> > I've indicated, and not just a one dimension array.
> >
> > I also searched through (via your link) other Oracle documentation,
> > with similar results.
> >
> > >
> > > Jim
> > >
> > > also do a search on asktom.oracle.com
> > >
> >
> > thanks for this, as well: I checked this out, and found a similar
> > question & answer:
> >
> > QUESTION:
> >
> > I want to know if I can have one of the parameter in
> > stored procedure as an array.
> > Ex : I have a procedure
> > procedure employee_report
> > (emp_no number
> > ,emp_dept varchar2
> > ,emp_salary number
> > ,emp_title varchar2)
> >
> > Instead can I define an object/array emp_property of structure
> > (emp_no number
> > ,emp_dept varchar2
> > ,emp_salary number
> > ,emp_title varchar2)
> > and use it a parameter in stored proc like
> > procedure employee_report(emp_prop emp_property ).
> > If this is possible than can I pass multiple records in this
> > procedure.
> >
> >
> > ANSWER:
> >
> > Sure, you just need to declare new types. You can have types that are
> > tables of
> > a RECORD or types that are tables of SCALARS. I'll show both below.
> > See the
> > plsql guide for how to declare record types -- you can do them based
> > on an
> > existing table structure, a cursor structure or by explicity defining
> > your own
> > record type.
> >
> > Note that if you are clling emp_report from a 3gl such as Pro*C, OCI,
> > VB, etc
> > (eg: anything OTHER then plsql) you will need to use tables of scalars
> > -- NOT a
> > table of records as tables of records cannot be bound to by a 3gl.
> > Also, I
> > suggest only use charArray's if you are going to be calling this from
> > a 3gl --
> > if you made a table of NUMBER or a table of DATE -- we would expect
> > you to send
> > the INTERNAL format of a number (22 byte field in an obscure format)
> > or a date
> > (7 byte, equally obscure format). Best to send strings and let the
> > conversions
> > take place.
> >
> > ----
> > The answer indicates it is possible, by saying "...you will need to
> > use tables of scalars...", but again, I am in the same situation, I
> > have no documentation on how to go about doing this, be it embedded
> > SQL (Pro*C, etc.), VB (OO4O, etc.), Perl (DBI, etc.), etc. Note: I did
> > several searches, based on the above answer, and still do not, as of
> > yet, have a solution.
> >
> > Now it seems I need to be able to figure out how to "use a table of
> > scalars" in the context of OO4O.
> >
> > All the documentation I can find, only indicates & gives examples of
> > passing a single-columned "array" (PL/SQL table) to the PL/SQL Stored
> > Parameter...
> >
> > >
> > > "George Hynes" <gggeorge_at_hotmail.com> wrote in message
> > > news:2ac16616.0307300754.7a582075_at_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 Aug 13 2003 - 08:58:25 CDT