Can an Oracle RECORD variable pass between procedures without procedures, knowing its real type?

Question

Using Oracle 10g PL / SQL. I am trying to find a way to pass a user defined record type (RECORD), without the procedures knowing its real type. Sounds like work for SYS.ANYDATA, but it doesn't seem like Oracle supports record type wrapping. 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;

Error 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

Obviously, convertobject does not expect RECORD, but I do not see other candidates for http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/t_anydat.htm .

, , - . Oracle ( , ), .

.

Answer 1

PL/SQL , , - PL/SQL . , , REFCURSOR. :

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;

.

.

EDIT:

, , , , .

Answer 2

, PL/SQL SQL. SQL PL/SQL .

" , , - " , / XML.

, , , , , - SQL PL/SQL, . XE, , , Java .

Answer 3

From my point of view, the design of the application should start at the very bottom layer, which is usually a database. It seems you are trying to get the DB to do what it is not intended for.

If you really need a universal container, you can always serialize anything, for example, xml and store it as text (clob) (in this case, you can save it as xmltype). However, I would suggest reconsidering the approach and start using the main features of the database storage and get the rows.