Can I anchor my data type to system type?

Question

The following code:

create or replace type sqlids_t is table of sys.v_$sql.sql_id%type;

...gives me...

Error(2,31): PLS-00329: schema-level type has illegal reference to SYS.V_$SQL

I get the same error creating a %object or %row type.

Is there any way I can use a sys type in my type definition? I know I can use the underlying VARCHAR2(13), but I'd like to avoid it if possible.

Answer 1

Short answer: No, you can't, and not because it's a system type. You can't anchor a freestanding type to any table's column data type.

%TYPE is a PL/SQL construct. CREATE [OR REPLACE] TYPE is SQL. You can't use %TYPE in SQL.

It somewhat makes sense that you can't. If you use MYTABLE.MYCOLUMN%TYPE in PL/SQL, you have anchored that PL/SQL type to the table, and should the type of MYCOLUMN change PL/SQL can invalidate your code, then recompile it. It's much less clear what Oracle would have to do if your example worked.

Imagine what would happen if you were storing objects of SQLID_T in a table, and the definition of SQL_ID in V_$SQL changed. Would Oracle need to change the definition of the stored objects? What if it couldn't (e.g, SQL_ID was changing from a VARCHAR2 to a NUMBER)? Should that prevent the definition of V_$SQL from changing? Or should the table that used that type become invalid in some way ... you can't SELECT from it anymore?

Oracle tries to prevent this kind of thing from happening (from Oracle's Object-Relational Developer's Guide):

SQL> CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (c varchar(20));
  2  /
Type created.

SQL> CREATE TABLE tb1 (c1 t1);
Table created.

SQL> CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (d number);
  2  /
CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (d number);
*
ERROR at line 1:
ORA-22866: cannot replace a type with table dependents