CODEWITHSUNDEEP
oracle-databaseoracle11g
Beast
Beast

Reputation: 629

Error creating the user defined data type

I have recently installed Oracle Application Express 11g.

I am trying to create the below user defined data type. 

create or replace type dept_type AS Object
 (
    dept_no DEPT.deptno%type,
    dept_name DEPT.DNAME%type,
    LOC DEPT.LOC%type
  )

In the definition above, DEPT is the table already exists in the current schema. When I am executing the above SQL command I am getting the below exception on the console.

Error at line 4: PLS-00201: identifier 'DEPT.DEPTNO' must be declared

2. (
3. dept_no DEPT.deptno%type,
4. dept_name DEPT.DNAME%type,
5. LOC DEPT.LOC%type
6. )

I don't understand why the above exception is coming? Is it something wrong with my definition?

Upvotes: 0

Views: 135

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

An object type is an SQL object. You can't use <object>%type in SQL, you can only use this type of construct in PL/SQL code.

For instance this will fail:

CREATE TABLE test_table (x dual.dummy%type);

You need to specify the exact datatype, as if you were creating a table.

Upvotes: 4

Related Questions