Hi All (especially Marco me old chum, Ian here from Cairo days, See you've lost the tash!)
Have a very frustrating problem hope its solvable. Apologies if this is wrong forum.
I have a user defined object that I am converting to an Oracle XmlType inside a c# .net stored procedure , this Xml I am then passing back to a pl-sql stored procedure. I would then like to recreate the object based on the incoming xml.
(All of this because you can't pass user defined types to and from external procedures grrrrrr)
Now all works fine if I use a simple object, however I cannot get it to work for a table object type.
Error being :
PLS-00306: wrong number or types of arguments in call to 'TOOBJECT'
Here is some pseudo code to explain my problem.
CREATE OR REPLACE TYPE "USER_RECORD" AS OBJECT
(
"USER_NAME" VARCHAR2(120) ,
"AGE" NUMBER
) FINAL INSTANTIABLE
/
CREATE OR REPLACE TYPE "TBL_USERS" IS TABLE OF USER_RECORD;
/
declare
myuserrecord USER_RECORD;
myusers TBL_USERS;
xmlusers xmltype;
newusers TBL_USERS;
begin
-- create the users table
myusers := tbl_users();
-- extend table ready for 1 row
myusers.extend(1);
-- setup the user record.
myuserrecord := USER_RECORD('IAN',39);
-- add record to table
myusers(1) := myuserrecord ;
-- convert the object to an xmltype.
select sys_xmlgen ( CAST( myusers AS tbl_users)) into xmlusers from dual;
-- view contents of xml
dbms_output.put_line(xmlusers.getStringVal() );
newusers := tbl_users();
-- convert the xml back to an object
xmlusers.toObject(newusers ); <<<<<<<<<<FAIL
end;
if you comment out "xmlusers.toObject(newusers ); " all works well i.e the xml content is
<?xml version="1.0"?>
<ROW>
<USER_RECORD>
<USER_NAME>IAN</USER_NAME>
<AGE>39</AGE>
</USER_RECORD>
</ROW>
so question is -- can you convert back from above xml into the table of users???
Thanks
Ian