Skip to Main Content
  • Questions
  • Pass string values to stored procedure

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: March 27, 2020 - 12:54 pm UTC

Answered by: Chris Saxon - Last updated: March 30, 2020 - 12:05 am UTC

Category: Database Development - Version: 18.6

Viewed 100+ times

You Asked

Hello, Ask Tom Team.

I have several DELETE statements and I want them all within a stored procedure to simplify execution. All these statements would use different string values in the where condition.

DELETE
FROM user1.table4 t4
WHERE t4.id IN (SELECT t3.id
FROM user1.table3 t3
WHERE t3.id IN (SELECT t2.id
FROM user1.table2 t2,
user1.table1 t1
WHERE t1.id = t2.id
AND t1.sender_doc IN ('1234567','8901234','5678912')));

DELETE
FROM user1.table5 t5
WHERE t5.id IN (SELECT t3.id
FROM user1.table3 t3
WHERE t3.id IN (SELECT t2.id
FROM user1.table2 t2,
user1.table1 t1
WHERE t1.id = t2.id
AND t1.sender_doc IN ('1234567','8901234','5678912')));

Requirement:
Create a stored procedure where you can pass one or n string parameters. E.g.


EXEC user.SP1('1234567','8901234','5678912','nn');

Thanks in advanced.

and we said...

You can't pass an IN list as a string!

One way around this to pass the values as an array:

create table t as 
  select level c1 from dual
  connect by level <= 100;

create or replace type n_arr 
  is table of number;
/

create or replace procedure p ( vals n_arr ) as
begin
  delete t
  where  c1 in (
    select column_value from table ( vals )
  );
end p;
/

select count(*) from t;

COUNT(*)   
        100 

declare
  vals n_arr;
begin
  vals := n_arr ( 1, 3, 9, 27, 81 );
  p ( vals );
end;
/

select count(*) from t;

COUNT(*)   
         95 

and you rated our response

  (3 ratings)

Reviews

Follow Up

March 27, 2020 - 6:04 pm UTC

Reviewer: A reader

Thanks for the quick answer.

1. What do these values mean in the procedure? 1, 3, 9, 27, 81

2. Sorry, bur I did not understand this part
declare
vals n_arr;
begin
vals := n_arr ( 1, 3, 9, 27, 81 );
p ( vals );
end;
/

3. If I want to delete strings '12345' and '6789', how would I proceed?

Thanks in advanced.
Connor McDonald

Followup  

March 30, 2020 - 12:05 am UTC

See paul comments below

a little more detail?

March 27, 2020 - 7:32 pm UTC

Reviewer: paul from quarantine, canada

The sample is showing you a simple way of doing this, just they used an array of numbers, not Strings

vals n_arr - declares that they will but a bunch of numbers into a Custom Type (n_arr) previously defined as a table of Numbers.

You would need to create a similar Table of type varchar2()

create or replace type n_arr
is table of varchar2(100);

They then create a Procedure using this Custom Type as a parameter.
create or replace procedure p ( vals n_arr ) as
begin
  delete t
  where  id in (
    select column_value from table ( vals )
  );
end p;


in that procedure they can then use the IN clause against the custom type.




Connor McDonald

Followup  

March 30, 2020 - 12:05 am UTC

thanks for helping out

Follow Up

March 30, 2020 - 12:44 am UTC

Reviewer: Geraldo

Thanks for the help. It was great!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.