How do I create Oracle Type which refers to table columns for data type?

Question

I am trying to define a type using the following code.

CREATE OR REPLACE TYPE MY_TYPE AS OBJECT (
    app_id        some_table_name.app_id%type
);

If I run this, I get the error.

Error(4,32): PLS-00201: identifier 'some_table_name.app_id' must be declared

What is wrong with this?

Answer 1

What's wrong with it is that %type is PL/SQL syntax. It isn't supported in SQL. Now we use PL/SQL to define Types (especially member functions, constructors, etc) but the Types themselves are SQL objects, and so follow SQL rules. That means we must declare Type attributes with explicit datatypes.

I agree that's a shame, and it would be really neat if we could reference table columns in type declarations like this. Unfortunately Oracle have really slowed down the changes to their TYPE implementation over the last couple of versions, so I think it is unlikely this will change in the near future.

What I would really like to see is Oracle support this syntax:

CREATE OR REPLACE TYPE MY_TYPE AS OBJECT 
      (     one_row        some_table_name.%rowtype ); 

Dynamic objects for interfaces: how cool would that be?