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

From: George Hynes <gggeorge_at_hotmail.com>
Date: 31 Jul 2003 05:13:37 -0700
Message-ID: <2ac16616.0307310413.575c08a5@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 Thu Jul 31 2003 - 07:13:37 CDT