CODEWITHSUNDEEP
c#reactjsoracle-database
JayDee
JayDee

Reputation: 102

Insert parameters with array in ORACLE using stored procedure

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

Upvotes: 0

Views: 202

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

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.

Upvotes: 1

Related Questions