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

oracleplsql

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:

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.

Related Topic