Avatar of dbatay
dbatay

asked on 

How to resolve ORA-01722: invalid number error when updating a table with the DECODE statement

I have a form in Oracle APEX that is using a Named LOV to allow the user to select 'YES' or "NO' for grant requested. These values are being written back to a table that contains 1 for Yes and 2 for No. My variable to hold the selected LOV value is a VARCHAR.

How can I resolve this error?

Thanks in advance for looking at my problem.
DECLARE
MY_ID         NUMBER;
grant_requested  VARCHAR2(3);
 
BEGIN
if :P370_DSP_BREQUESTED = 'DSPBIF' then  -- Allow setting of grant_requested value only
  FOR i IN 1..HTMLDB_APPLICATION.G_F01.COUNT LOOP
  MY_ID 	  := HTMLDB_APPLICATION.G_F01(i);  
  grant_requested   := HTMLDB_APPLICATION.G_F02(i);  -- grant_requested(YES or NO)
    UPDATE TBL_GRANTS_REQUESTED
    SET grant_requested_id = decode(grant_requested_id,
    1, 'YES', 
    2, 'NO', grant_requested)
    WHERE grant_requested_type_id = MY_ID;
  COMMIT;
  END LOOP;

Open in new window

Oracle DatabaseEnterprise SoftwareWeb Applications

Avatar of undefined
Last Comment
MarkusId

8/22/2022 - Mon