I’m trying to insert the data inside a forall loop. For this case, I cannot use a temporary variable and set result of the function beforehand.

The function just maps a number to a string:

create or replace function GetInvoiceStatus(status number)
    return nvarchar2
as
begin
    case status
        when 0 then return 'New';
        when 200 then return 'Sent';
        when 300 then return 'Accepted';
        end case;

    return '';
end; 

when I call this function like:

select GetInvoiceStatus(200) from dual;

I get the appropriate result.

However, when I try to insert the data I get errors.
The forall insert:

forall i in 1.. INVOICE_DATA.COUNT
insert into "InvoiceAudit"
("PropertyName", "OldValue", "NewValue" (
            VALUES ('Status', (GetInvoiceStatus(invoice_data(i).status)),
                    ((GetInvoiceStatus((select "Status" from "Invoice" where "InvoiceId" = invoice_data(i).invoiceId)))));

However, I get the following error:

[2023-06-01 15:02:57] [65000][6592] [2023-06-01 15:02:57] ORA-06592:
CASE not found while executing CASE statement [2023-06-01 15:02:57]
ORA-06512: at "PUBLIC.GETINVOICESTATUS", line 9 [2023-06-01 15:02:57]
ORA-06512: at "PUBLIC.INVOICESSP", line 63 [2023-06-01 15:02:57]
Position: 5

I have double checked, and the results from invoice_data(i).Status and the other select value are both valid parameters (and have their cases covered) and return appropriate string when called outside the stored procedure.

Is the syntax somewhere wrong?
I would like to remain using forall if at all possible because it is much faster than a regular for loop.

>Solution :

This error means that the parameter value (status) is not one of the cases in the case statement (which are 0, 200, 300).

If you executed this code select GetInvoiceStatus(555) as dd from dual you will get the same error.

So, add else statement like this:

create or replace function GetInvoiceStatus(status number)
    return nvarchar2
as
begin
    case status
        when 0 then return 'New';
        when 200 then return 'Sent';
        when 300 then return 'Accepted';
        else return '';
        end case;
end;