PLS-00355 error whike creating a new table type-CodePudding

I'm getting the PLS-00355 error while trying to create the new type like this:

CREATE OR REPLACE TYPE DAYS_T IS TABLE OF VARCHAR(250) INDEX BY BINARY_INTEGER;

Any clue what is wrong? Many thanks!

CodePudding user response：

This is what you did and how Oracle responded:

SQL> CREATE OR REPLACE TYPE DAYS_T IS TABLE OF VARCHAR(250) INDEX BY BINARY_INTEGER;
  2  /

Warning: Type created with compilation errors.

SQL> show err
Errors for TYPE DAYS_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/16     PLS-00355: use of pl/sql table not allowed in this context

On the other hand:

SQL> CREATE OR REPLACE TYPE DAYS_T IS TABLE OF VARCHAR2(250);
  2  /

Type created.

SQL>

Responding to your comment: if you declared type at PL/SQL level (not SQL), then your code (without create or replace, though) would be OK (line #2 is what you used, literally):

SQL> declare
  2    TYPE DAYS_T IS TABLE OF VARCHAR(250) INDEX BY BINARY_INTEGER;
  3  begin
  4    null;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

You can define a nested table collection type in the SQL scope using:

CREATE OR REPLACE TYPE DAYS_T IS TABLE OF VARCHAR(250);

You can define an associative array collection type in a PL/SQL scope using:

DECLARE
  TYPE DAYS_T IS TABLE OF VARCHAR(250) INDEX BY BINARY_INTEGER;
BEGIN
  NULL;
END;
/

You could also locally define a nested-table collection type in a PL/SQL scope using:

DECLARE
  TYPE DAYS_T IS TABLE OF VARCHAR(250);
BEGIN
  NULL;
END;
/

However, you cannot define an associative array collection type in the SQL scope as it is a PL/SQL only data type.

You then asked in comments:

But how can I add an index?

They both have an index.

For example, after declaring the type in SQL, you can use the nested table collection in PL/SQL like this:

DECLARE
  v_days DAYS_T;
BEGIN
  v_days := DAYS_T();       -- Initialise the collection.
  v_days.EXTEND(3);         -- Extend the collection by 3 elements.
  v_days(1) := 'Monday';    -- Set the first element.
  v_days(3) := 'Wednesday'; -- Set the third element.

  FOR i IN 1 .. v_days.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( i || ' = ' || v_days(i) );
  END LOOP;
END;
/

Which outputs:

1 = Monday
2 = 
3 = Wednesday

db<>fiddle here