Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 474 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to accept multiple input parameters in comma separated in stored procedure

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?
Best Answers
-
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.
-
Front end is very generic statement? Is it Java, .Net, perl, python....? Anyway, you can use OracleArray in JDBC.
SY.
Answers
-
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 Can we do this using for loop cursor?
-
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.
-
@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?
-
Front end is very generic statement? Is it Java, .Net, perl, python....? Anyway, you can use OracleArray in JDBC.
SY.
-
@Solomon Yakobson Thanks. Got it