Search code examples
sqloracle-databaseora-01722

oracle pl/sql ora-01722 error

I have a simple oracle statement in my procedure:

update org.security_training_question a 
set a.actv_indr = 'N' where a.qstn_id in (v_qstns_to_delete);

v_qstns_to_delete is a parameter being passed. It is a varchar2 field and a.qstn_id is a numeric field.

When calling the Stored Procedure, for v_qstns_to_delete I am passing the following String: "24, 43, 23, 44, 21".

When I run the statement output the stored procedure thenn it runs fine but when I run it as a stored procedure I get an error on the above line saying Invalid Number.

Any clue?

Solution

  • You can't use a "in" clause with a variable like that. One way around it is

    declare stmt varchar2(4000);
begin
  stmt := 'update org.security_training_question a set a.actv_indr = ''N'' where a.qstn_id in ('||v_qstns_to_delete||')';
  execute immediate stmt;
end;