Create object type with rowid attributes

Question

I am trying to create a type with the rowid data type, but I am getting this error because of the type I am trying to use:

SQL> CREATE TYPE join_t IS OBJECT (inn  rowid,    out rowid ); 
/

Warning: Type created with compilation errors.

Even though I can create a table with rowid data type:

SQL> create table test_rowid (inn rowid,out rowid);

Table created.

Is it possible to create this type join_t above, with rowid-type attributes?

Answer 1

No, you can't create an object type with rowid fields. If you looks at the actual error raised, via the user_errors view or with the SQL*Plus command show errors, you will see:

LINE/COL ERROR
-------- ------------------------------------------------------------------------------
1/28     PLS-00530: Illegal type used for object type attribute: 'ROWID'.
1/39     PLS-00530: Illegal type used for object type attribute: 'ROWID'.

The documenation says:

Restrictions on datatype

• You cannot impose the NOT NULL constraint on an attribute.
• You cannot specify attributes of type ROWID, LONG, or LONG RAW.
• You cannot specify a data type of UROWID for an ADT.
  ...

As a workaround, you could potentially use a string type in your object, and convert the values when setting to getting the field values, via the rowidtochar and chartorowid functions:

CREATE TYPE join_t IS OBJECT (inn varchar2(18), out varchar2(18) );
/

Type JOIN_T compiled

SELECT join_t(rowidtochar(rowid), rowidtochar(rowid)) FROM DUAL;

JOIN_T(ROWIDTOCHAR(ROWID),ROWIDTOCHAR(ROWID))(INN, OUT)
-------------------------------------------------------
JOIN_T('AAAAB0AABAAAAOhAAA', 'AAAAB0AABAAAAOhAAA')

Storing rowids in an object doesn't seem particularly useful though, as they can change.