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

sql oracle user-defined-types
AppleGrew picture AppleGrew · Mar 9, 2012 · Viewed 34.7k times · Source

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

APC picture APC · Mar 9, 2012

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?

Related questions

Concatenating collections in PLSQL

I need to collect a lot of ids from a couple of different tables into a variable of some sort to be passed to another function. Which tables to take the ids from is dynamic, depending on the param iVar …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More