Forum Stats

  • 3,768,281 Users
  • 2,252,770 Discussions
  • 7,874,513 Comments

Discussions

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

Albert Chao
Albert Chao Member Posts: 129 Green Ribbon
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?

Tagged:

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond
    Accepted Answer

    You can use VARRAY:

    create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
    is
    lv_count number(10);
    begin
    for v_i in 1..iv_req_id.count loop
    select count(1) into lv_count from staging where req_id = iv_req_id(v_i);
    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(v_i)) 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;
    end loop;
    commit;
    end sp_main_target;
    /
    
    Procedure created.
    
    SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
    Insertion into target table
    Insertion into target table
    Request id is not available
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL> select * from target_tab;
    
    
        KEY_ID     INV_ID I_NAME                             REQ_ID
    ---------- ---------- ------------------------------ ----------
           110       1001 test1                                   1
           111       1002 test2                                   2
    
    
    SQL>
    

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond
    Accepted Answer

    Front end is very generic statement? Is it Java, .Net, perl, python....? Anyway, you can use OracleArray in JDBC.

    SY.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond
    Accepted Answer

    You can use VARRAY:

    create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
    is
    lv_count number(10);
    begin
    for v_i in 1..iv_req_id.count loop
    select count(1) into lv_count from staging where req_id = iv_req_id(v_i);
    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(v_i)) 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;
    end loop;
    commit;
    end sp_main_target;
    /
    
    Procedure created.
    
    SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
    Insertion into target table
    Insertion into target table
    Request id is not available
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL> select * from target_tab;
    
    
        KEY_ID     INV_ID I_NAME                             REQ_ID
    ---------- ---------- ------------------------------ ----------
           110       1001 test1                                   1
           111       1002 test2                                   2
    
    
    SQL>
    

    SY.

  • Albert Chao
    Albert Chao Member Posts: 129 Green Ribbon

    @Solomon Yakobson Can we do this using for loop cursor?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond

    We could but why call SQL from PL/SQL if all can be done in PL/SQL?

    create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
    is
    lv_count number(10);
    begin
    for v_rec in (select * from table(iv_req_id)) loop
    select count(1) into lv_count from staging where req_id = v_rec.column_value;
    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 = v_rec.column_value) 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;
    end loop;
    commit;
    end sp_main_target;
    /
    
    Procedure created.
    
    SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
    Insertion into target table
    Insertion into target table
    Request id is not available
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from target_tab;
    
        KEY_ID     INV_ID I_NAME                             REQ_ID
    ---------- ---------- ------------------------------ ----------
           110       1001 test1                                   1
           111       1002 test2                                   2
    
    SQL>
    
    

    SY.

  • Albert Chao
    Albert Chao Member Posts: 129 Green Ribbon

    @Solomon Yakobson Actually this stored procedure I am going to invoke from the front end so will this work? Front end wherein I will provide the request-id and process will happen. Or do we have to use connect by caluse for comma-separated inputs?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond
    Accepted Answer

    Front end is very generic statement? Is it Java, .Net, perl, python....? Anyway, you can use OracleArray in JDBC.

    SY.

  • Albert Chao
    Albert Chao Member Posts: 129 Green Ribbon