CODEWITHSUNDEEP
sqloracle-database
Rexam
Rexam

Reputation: 913

Why a type cannot be constrained?


I am struggling with the following code:

DECLARE
  TOTACID TAB_OF_ID(50);
  RES NUMBER;
BEGIN
  SELECT DISTINCT ID INTO TOTACID
  FROM TABLE_B;

  FOR indx IN 1 .. TOTACID.COUNT
    LOOP
      RES := F_IMPORT(TOTACID(indx));
      DBMS_OUTPUT.PUT_LINE ('Moved ID ' || RES);
    END LOOP;

END;
/

When I run it, it fails with the error:

Error report - ORA-06550: line 2, column 11: PLS-00566: type name "TAB_OF_ID" cannot be constrained ORA-06550: line 5, column 19: PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER ORA-06550: line 5, column 3: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Where TAB_OF_ID has been declared as follows:

create or replace TYPE TAB_OF_ID AS table of NUMBER(19,2);

I don't understand what is wrong. It seems that I cannot declare TOTACID TAB_OF_ID(50) but in a function I created some days ago I could declare LIS_ID TAB_OF_ID := TAB_OF_ID(50); and it works properly. What is the difference with the script I have here?

Upvotes: 0

Views: 1001

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59543

Obviously TOTACID TAB_OF_ID(50) is different to LIS_ID TAB_OF_ID := TAB_OF_ID(50); The command is like

{variable name} {data type} := {inital value};

where := {inital value} is optional.

TOTACID TAB_OF_ID(50) would mean 

{variable name} {inital value};

which is not valid syntax.

Upvotes: 3

Aleksej
Aleksej

Reputation: 22959

You can not declare the variable by assigning your table a fixed number of records, so you need something like:

DECLARE
  TOTACID TAB_OF_ID;
  RES NUMBER;
BEGIN
  SELECT DISTINCT ID bulk collect INTO TOTACID
  FROM TABLE_B;
  ...
END;
/

Also, notice that you are fetching more than one value, so you need BULK COLLECT INTO.

About

LIS_ID TAB_OF_ID := TAB_OF_ID(50);

here you are not declaring a variable with a given number of records, but you are assigning to a variable the 50th value of the table.

Upvotes: 2

Related Questions