Using Oracle 10g PL/SQL. I'm trying to find a way to pass a custom defined RECORD type around without the procedures having to know its real type. Sounds like a job for SYS.ANYDATA but it doesn't look like Oracle supports wrapping RECORD types. For example:
DECLARE
TYPE t_rec IS RECORD (id number);
v_rec t_rec;
v_ad SYS.ANYDATA;
BEGIN
v_rec.id := 1;
v_ad := SYS.ANYDATA.CONVERTOBJECT(v_rec);
END;
Fails with error:
v_ad := SYS.ANYDATA.CONVERTOBJECT(v_rec);
*
ERROR at line 7:
ORA-06550: line 7, column 11:
PLS-00306: wrong number or types of arguments in call to 'CONVERTOBJECT'
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
Clearly convertobject is not expecting a RECORD but I don't see any other candidates on http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/t_anydat.htm either.
Ultimately what I'm looking for is some way to store different RECORD types in the same variable. I'd also like to avoid Oracle object types (top-level types defined outside of packages) as they have their own issues.
Thanks.
Best Answer
This section of the PL/SQL User's Guide and Reference would indicate that you cannot do what you want to do here directly - PL/SQL wants to try to convert parameters at runtime. You might want to see if you can make a REFCURSOR work. Simple example:
You can then pass around the cursor variable reference to your heart's content.
HTH.
EDIT:
Another option, depending on how many record types you need to deal with, is to overload the same handler procedure(s) with the various record types.