PLS-00201 identifier "rep_table_T" must be declared

Question

I'm trying to compile this procedure. I whish it gets table row as parameter:

create or replace PROCEDURE MY_HELPER (rep_table_row IN OUT rep_table_T%ROWTYPE)  IS
...
END MY_HELPER ;

The table is defined as

create or replace TYPE         "rep_table_T" AS TABLE OF rep_table_O;

The object is defined as:

create or replace TYPE         "rep_table_O" AS OBJECT (       
    "day" VARCHAR2(250 BYTE),
    "TS" DATE
    );

However I can't compile it because I'm getting the error:

PLS-00201 identifier "rep_table_T" must be declared.

You should get the error `PLS-00201 identifier 'REP_TABLE_O' must be declared`. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=79e4e26a92e425f0f221c583b9dd6ad0) — MT0, Sep 27 '21 at 12:28
Did you read [the reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-75337742-67FD-4EC0-985F-741C93D918DA)? *A **quoted identifier** begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, **then you must use the double quotation marks whenever you refer to that object**.* — astentx, Sep 27 '21 at 12:36
Does this answer your question? [ORA-00904: invalid identifier](https://stackoverflow.com/questions/6027961/ora-00904-invalid-identifier) [Oracle SQL Syntax: Quoted identifier](https://stackoverflow.com/questions/6468337/oracle-sql-syntax-quoted-identifier) — astentx, Sep 27 '21 at 12:38

score 1 · Answer 1 · answered Sep 27 '21 at 12:20

1

Remove the %ROWTYPE. That only applies to actual sql tables, not pl/sql collections. From the documentation, "The %ROWTYPE attribute provides a record type that represents a row in a database table. "

Yeah it is very confusing as a new user that oracle calls actual tables and pl/sql tables the same thing.

answered Sep 27 '21 at 12:20

OldProgrammer

12,050
4
24
45

Is it then possible to pass rowtype of collection as parameter? – hajduk Sep 27 '21 at 14:04
No. Why do you need to even do that? – OldProgrammer Sep 27 '21 at 14:23
Because let say I have `rep_table rep_table_T;` `myIdx BINARY_INTEGER;` I wish to pass to the procedure `rep_table (myIdx )` instead of `rep_table`; – hajduk Sep 28 '21 at 08:06

score 1 · Answer 2 · answered Sep 27 '21 at 12:21

Forget about double quotes while in Oracle. If you use them while creating any objects, you have to use them always.

As of your code: removed double quotes, removed rowtype in procedure declaration.

SQL> CREATE OR REPLACE TYPE rep_table_o AS OBJECT
  2  (
  3     day VARCHAR2 (250 BYTE),
  4     ts DATE
  5  );
  6  /

Type created.

SQL> CREATE OR REPLACE TYPE rep_table_t AS TABLE OF rep_table_o;
  2  /

Type created.

SQL> CREATE OR REPLACE PROCEDURE my_helper (
  2     rep_table_row  IN OUT rep_table_t)
  3  IS
  4  BEGIN
  5     NULL;
  6  END my_helper;
  7  /

Procedure created.

SQL>

score 1 · Answer 3 · answered Sep 27 '21 at 12:25

You have two errors:

Do not use quoted identifiers:
```
CREATE TYPE rep_table_O AS OBJECT (       
  day VARCHAR2(250 BYTE),
  TS  DATE
);

CREATE TYPE rep_table_T AS TABLE OF rep_table_O;
```
(Or, if you really must have lower-case identifiers [why?] then you need to use quoted identifiers, with exactly the same case, everywhere that identifier is used.)

Do not use %ROWTYPE:

CREATE PROCEDURE MY_HELPER (
  rep_table_row IN OUT rep_table_T
)
IS
  ...
END MY_HELPER;

db<>fiddle here

PLS-00201 identifier "rep_table_T" must be declared

3 Answers3