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
"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 Thu Jul 31 2003 - 09:57:14 CDT
