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. 

CodePudding user response：

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.

CodePudding user response：

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>

CodePudding user response：

You have two errors:

1. 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.)

2. Do not use %ROWTYPE:

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

db<>fiddle here