ora-01722 invalid number while updating record

Question

I have a table where i have to update multiple records on one button click. I am trying to update multiple record using below simple query.

UPDATE tablename SET column1=1 WHERE     
idcolumn IN ('1','2','3')

where datatype of idcolumn is Number. If i run this query manually its working perfectly. But if i pass these ('1','2','3') parameteres through procedure then it is showing me below error i.e. (ora-01722 invalid number).

I tried to_number() function but still it is showing me above error.

Proc:

CREATE OR REPLACE PROCEDURE procname(idpara  VARCHAR2,
                                 RCT_OUT OUT SYS_REFCURSOR) IS
BEGIN
  UPDATE tablename SET column1 = 1 WHERE idcolumn IN (idpara);
  COMMIT;
  OPEN RCT_OUT FOR
    SELECT 'RECORD UPDATED SUCCESSFULLY' RESULT FROM DUAL;
END;

Show us your procedure code. It looks like you are passing the whole string. — Kaushik Nayak, Oct 05 '18 at 05:57
This sounds weird but almost certainly the problem is related to your data. You need to post a reproducible test case with some sample data which demonstrates the error. — APC, Oct 05 '18 at 05:58
To me, it sounds like a *varying elements in IN list* problem (as you mentioned a "procedure" and "passing parameters"). Though, to be sure, you should post a test case (as you've already been told). — Littlefoot, Oct 05 '18 at 06:02
`idpara` is a **string** contianing a non-numeric value `1,2,3`. This has come up so many times. Please read [this thread](https://stackoverflow.com/q/17900024/146325) and also [the thread it duplicates](https://stackoverflow.com/q/242771/146325) for a full range of options. — APC, Oct 05 '18 at 07:03

Peter · Answer 1 · 2018-10-05T06:33:58.360

The procedure does not understand IN (idpara) with idpara being '1','2','3' as IN ('1','2','3') but as IN (q'!'1','2','3'!'). In other words, it is not searching for '1' and '2' and '3' but for '1,2,3'. But while '1' can be converted to a number '1,2,3' can not.

Here is a test case for you to show you:

select * from dual;
-- X
-- notice I have 'X' in the in list below

set serveroutput on 
declare
idpara varchar2(400) := q'!'X','2','3'!';
v_out varchar2(400);
begin 
select count(*) into v_out from dual where dummy in (idpara);
dbms_output.put_line(v_out);
end;
/
-- 0

declare
idpara varchar2(400) := q'!'X','2','3'!';
v_out varchar2(400);
sql_stmt VARCHAR2(1000) := NULL;
begin 
sql_stmt :='select count(*) from dual where dummy in ('||idpara||')';
execute immediate sql_stmt into v_out;
dbms_output.put_line(v_out);
end;
/
-- 1

One solution inside of procname would be to build a pl/sql object of numbers and use that in the update. There is a lot of info out there on how to do it. E.g. here Convert comma separated string to array in PL/SQL And here is info on how to use the object in the IN-clause Array in IN() clause oracle PLSQL

ora-01722 invalid number while updating record

1 Answers1