Oracle: PLS-00382: expression is of wrong type

April 4, 2024

When running the below procedure:

PL/SQL:

declare 
    errstr varchar2(4000) := '';
    errors  NUMBER;
    i  NUMBER;
    er NUMBER;
    BK1 acct_common.TYPE_TABNUMBER;
    BK2 acct_common.TYPE_TABDATE;
    BK3 acct_common.TYPE_TABNUMBER;
    BK4 acct_common.TYPE_TABVARCHAR;
    BK5 acct_common.TYPE_TABVARCHAR;
    BK6 acct_common.TYPE_TABNUMBER;
    BK7 acct_common.TYPE_TABVARCHAR;
    BK8 acct_common.TYPE_TABVARCHAR;
    BK9 acct_common.TYPE_TABNUMBER;
begin
    BK1 := acct_common.TYPE_TABNUMBER(1391461367,1391461368,1391461369);
    BK2 := acct_common.TYPE_TABDATE(to_date('09/30/2023'),to_date('09/30/2023'),to_date('09/30/2023'));
    BK3 := acct_common.TYPE_TABNUMBER(11710317,11710317,11710317);
    BK4 := acct_common.TYPE_TABVARCHAR(null,null,null);
    BK5 := acct_common.TYPE_TABVARCHAR('Consolidation','Consolidation','Consolidation');
    BK6 := acct_common.TYPE_TABNUMBER(665,665,665);
    BK7 := acct_common.TYPE_TABNUMBER(665,665,665);
    BK8 := acct_common.TYPE_TABNUMBER(665,665,665);
    BK9 := acct_common.TYPE_TABNUMBER(400000164779,400000164779,400000164779);
    forall i in 1 .. 3 INSERT INTO table (col1,col2,col3,col4,col5,col6,col7,col8,col9) VALUES (BK1(i),BK2(i),BK3(i),BK4(i),BK5(i),BK6(i),BK7(i),BK8(i),BK9(i));    
end;

TYPE_TABNUMBER, TYPE_TABDATE, TYPE_TABVARCHAR are defined as:

create or replace TYPE "TYPE_TABNUMBER" as table of number;
create or replace TYPE "TYPE_TABDATE" as table of date;
create or replace TYPE "TYPE_TABVARCHAR" as table of varchar2(4000 char)

Error:

Error report -
ORA-06550: line 22, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 22, column 5:
PL/SQL: Statement ignored
ORA-06550: line 23, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 23, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

What am doing wrong here? Appreciate your help.
Thank you.

>Solution :

You have defined:

 BK7 acct_common.TYPE_TABVARCHAR;
 BK8 acct_common.TYPE_TABVARCHAR;

But are assigning numerics:

    BK7 := acct_common.TYPE_TABNUMBER(665,665,665);
    BK8 := acct_common.TYPE_TABNUMBER(665,665,665);

Usually Oracle will automatically cast from number to varchar2, but with overloads and type constructors it’s more stringent and isn’t casting for you. Fix the type or wrap the numbers in single quotes to make them strings.

By the way, notice that your error stack pinpointed lines 22 and 23. If you count starting at DECLARE, you find lines 22 and 23 are these two assignments. This might be helpful in the future to isolate a problem like this.