Link to home
Create AccountLog in
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

Avatar of flow01
flow01
Flag of Netherlands image

   SET grant_requested_id = decode(grant_requested_id,
     'YES', 1
     'NO', 2,null)    null = grant requested ?


ASKER CERTIFIED SOLUTION
Avatar of MarkusId
MarkusId
Flag of Austria image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer