CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqltypes
sunny_dev
sunny_dev

Reputation: 765

Oracle TYPE declaration throwing error PLS-00201 - identifier must be declared

I created a Object Type successfully using below command:

create or replace 
TYPE  "SharedAccountRecType" AS object(
 account_id NUMBER(11),
 share_by_id NUMBER(11),
 share_to_id NUMBER(11)
);

Then I tried to create another Type as a table of above successfully created Object Type. Below command is throwing error:

create or replace 
TYPE "SharedAccountRecTypeCollection" 
as table of SharedAccountRecType

Error: PLS-00201: identifier 'SHAREDACCOUNTRECTYPE' must be declared.

Both above commands were executed using same Oracle user, one after the other. Please help.

Upvotes: 0

Views: 2183

Answers (2)

Aleksej
Aleksej

Reputation: 22969

If you use double quotes, Oracle will create a type with the exact name you typed, case sensitive. For example:

SQL> create or replace
  2  TYPE  "SharedAccountRecType" AS object(
  3   account_id NUMBER(11),
  4   share_by_id NUMBER(11),
  5   share_to_id NUMBER(11)
  6  );
  7  /

Type created.

SQL> create or replace
  2  TYPE  SharedAccountRecType AS object(
  3   account_id NUMBER(11),
  4   share_by_id NUMBER(11),
  5   share_to_id NUMBER(11)
  6  );
  7  /

Type created.

SQL> select type_name
  2  from user_types
  3  where upper(type_name) = 'SHAREDACCOUNTRECTYPE';

TYPE_NAME
------------------------------
SHAREDACCOUNTRECTYPE
SharedAccountRecType

SQL>

Given this, you may want to remove the double quotes from the creation of SharedAccountRecType:

create or replace  TYPE  SharedAccountRecType AS object ...
create or replace ... as table of SharedAccountRecType

or add them in the creation of the table type, depending whether you want case sensitive names or not

create or replace  TYPE  "SharedAccountRecType" AS object ...
create or replace ... as table of "SharedAccountRecType"

Upvotes: 2

Vladimir
Vladimir

Reputation: 19

It is happened because you use "" in the name of type.

In Oracle names "SharedAccountRecType" and SharedAccountRecType are different.

Don't use name in "", because Oracle threat names in "" as case-sensitive, but name without "" as case-unsensitive uppercase.

Upvotes: 2

Related Questions