Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to accept multiple input parameters in comma separated in stored procedure

Albert ChaoOct 25 2021
create table staging
(key_id number(10),inv_id number(10), i_name varchar2(30), req_id number(10));


insert into staging values(110,1001,'test1',1);
insert into staging values(111,1002,'test2',2);

create table target_tab
(key_id number(10), inv_id number(10), i_name varchar2(30), req_id number(10));



Stored Procedure :

create or replace NONEDITIONABLE procedure sp_main_target(iv_req_id IN VARCHAR)
is
lv_count number(10);
begin
for r in(--should I select request id from staging table)loop
select count(1) into lv_count from staging where req_id = iv_req_id;
if lv_count > 0 then
dbms_output.put_line('Insertion into target table');
MERGE INTO target_tab t
   USING (SELECT key_id, inv_id, i_name, req_id FROM staging
   WHERE req_id = iv_req_id) S
   ON (t.inv_id = S.inv_id)
   WHEN MATCHED THEN UPDATE SET 
   t.key_id = s.key_id,
   t.i_name = s.i_name,
   t.req_id = s.req_id


   WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
     VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
else
dbms_output.put_line('Request id is not available');
end if;
commit;
end sp_main_target;


I want to give multiple input parameters for iv_request_id and then have to process each request-id individually. How to achieve this? How will I implement multiple input parameters in comma separated?

This post has been answered by Solomon Yakobson on Oct 25 2021
Jump to Answer

Comments

Post Details

Added on Oct 25 2021
6 comments
3,567 views