Function call as a parameter inside insert values statement

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.

This error means that the parameter value (status) is not one of the cases in the case expression (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 clause 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; 

CASE used as a PL/SQL expression (rather than a function that returns a result, as it is commonly used in SQL) has to do something. If none of your conditions match, it has nothing to do:

DECLARE
  var_test integer := 1;
BEGIN
  CASE var_test 
    WHEN 2 THEN null; 
  END CASE;
END;

Throws the ORA-06592 error. To fix, add an ELSE to catch anything that doesn't satisfy your other WHEN conditions.

DECLARE
  var_test integer := 1;
BEGIN
  CASE var_test 
    WHEN 2 THEN null; 
    ELSE null; 
  END CASE;
END;

Works fine. Note again that this requirement only exists for CASE..END CASE used as a PL/SQL expression. Most of the time we see CASE it's a function and doesn't have to have an ELSE like this (it will return NULL if no WHEN clauses are matched). But the PL/SQL expression is a different animal.

I would also add that you shouldn't be using a user-defined function for this. Simply use the SQL function CASE or even DECODE inside your SQL directly to do the translation.