OraParameters: how to "AddTable" with more than one column?
George Hynes
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@no_spam.comcast.net> wrote in message news:<OFkVa.2535$Ho3.947@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" <ggge...@hotmail.com> wrote in message
> news:2ac16616.03072...@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
Jim Kennedy
probably what you are looking for:
http://download-west.oracle.com/docs/cd/B10501_01/win.920/a95895/o4o00046.htm
Jim
also do a search on asktom.oracle.com
"George Hynes" <ggge...@hotmail.com> wrote in message
news:2ac16616.03073...@posting.google.com...
George Hynes
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote in message news:<2PZVa.22840$Ho3.4056@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...
Jim Kennedy
record. Think of it as an array of structures.
Jim
"George Hynes" <ggge...@hotmail.com> wrote in message
news:2ac16616.03073...@posting.google.com...
George Hynes
> 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.