DELETE By Procedure With String Input – Oracle

I am writing a procedure to delete some records of z_names table by IDs from the input as string (and I need to seprate them by special character (comma ,)).

Sample Input: '1,4'

z_names table:

ID	NAME
1	jim
2	john
3	jack
4	alex

After running this procedure, the record with ID 1 and 4 (jim and alex) must be deleted.

I have lots of errors while was writing this procedure.

create or replace procedure prc_z_names_del (par_string in varchar2)
is
  V_del_query  varchar2(200);
begin
  V_del_query := 'delete from '  || z_names|| 'WHERE ID = ' ||
    select to_number(trim(regexp_substr(par_string, '[^,]+', 1, level)))
    from dual
    connect by level <= regexp_count(par_string, ',') + 1||';';
  EXECUTE IMMEDIATE V_del_query;
end;
/

Answer

You’ don’t need dynamic SQL.

Sample data:

SQL> select * from z_names;

        ID NAME
---------- ----
         1 jim
         2 john
         3 jack
         4 alex

Procedure:

SQL> create or replace procedure prc_z_names_del (par_string in varchar2)
  2  is
  3  begin
  4    delete from z_names
  5    where id in (select to_number(trim(regexp_substr(par_string, '[^,]+', 1, level)))
  6                 from dual
  7                 connect by level <= regexp_count(par_string, ',') + 1
  8                );
  9    dbms_output.put_line('Deleted ' || sql%rowcount ||' row(s)');
 10  end;
 11  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec prc_z_names_del('1, 4');
Deleted 2 row(s)

PL/SQL procedure successfully completed.

SQL> select * from z_names;

        ID NAME
---------- ----
         2 john
         3 jack

SQL>