I have below parameters from reactjs and I'm passing it to C#, then it will pass to Oracle stored procedure for INSERT. My question is on Oracle Stored Procedure, how will I able to insert it to table as multiple row, depends on the value of Array from the column2 with same ID.
{
ID: 2,
DEP_ID: [4,5,6]
}
I want to save it to tbl_testing with below result.
tbl_testing
ID | DEP_ID
2 | 4
2 | 5
2 | 6
One simple way to achieve this is using a Procedure with regular expression functions to split the array into its different parts
Demo
create table dptos ( v1 number, v2 number );
create or replace procedure insert_table ( p1 in varchar2 , p2 in varchar2 )
is
v_counter pls_integer := regexp_count (p2, ',') + 1;
v_record pls_integer;
begin
for var in 1..v_counter
loop
if var = 1
then
v_record := regexp_substr( p2, '[^,]+', 1 , 1 );
insert into dptos ( v1 , v2 ) values ( p1 , v_record );
elsif var > 1 and var <= v_counter
then
v_record := regexp_substr( p2, '[^,]+', 1 , var );
insert into dptos ( v1 , v2 ) values ( p1 , v_record );
end if;
end loop;
commit;
end;
/
SQL> exec insert_table ( 2 , '4,5,6' ) ;
PL/SQL procedure successfully completed.
SQL> select * from dptos ;
V1 V2
---------- ----------
2 4
2 5
2 6
SQL>
Of course, it works for the purpose of your example as long as you can call the procedure in that way using the values of the array.
