Can an Oracle variable of type RECORD be passed between procedures without the procedures knowing its real type?

Question

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.

Answer 1

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:

CREATE OR REPLACE PROCEDURE TheTest AS

    v_cursor SYS_REFCURSOR;
    v_v1   NUMBER;
    v_v2   NUMBER;
    v_v3   NUMBER;

  PROCEDURE getRS(pr OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN pr FOR SELECT 1,2,3 FROM dual;
  END;

  BEGIN
    getRS(v_cursor);
    FETCH v_cursor INTO v_v1, v_v2, v_v3;
    dbms_output.put_line(v_v1||','||v_v2||','||v_v3);
    CLOSE v_cursor;
  END;

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.

Answer 2

You are trying to deal with two separate layers, PL/SQL and SQL. SQL does not understand PL/SQL records and will not deal with them.

"Ultimately what I'm looking for is some way to store different RECORD types in the same variable" Write wrappers that convert them to/from XML.

No offence intended, but looking at your other posts, you seem to be trying to impose an object oriented paradigm on SQL and PL/SQL, and it isn't fitting too well. If you are not coding for XE, then maybe consider using Java Stored Procedures in the database.

Answer 3

From my point of view application design should start in the most bottom layer, which usually is the database. You seem to try to force the DB do something it is not meant for.

If you really want universal container then you always can serialize anything as for example xml and store it as text (clob) (in this case you can store it as xmltype). However I would suggest to rethink the approach and start using the core capabilities of the DB - store and retrieve rows.