Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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
share|improve this answer
    
Because Oracle does not know what to do, they require us to create types that have a less clear intent. –  Noctis Skytower 13 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.