problem with mutiple row insert in a table using pl/sql

[nabh4u]

hi,

I am trying to insert multiple rows in a table using PL/SQL. I have two procedures and two tables. the first procedure gets the data from the source table, manipulates it and then sends the data to the second procedure which inserts the data into the target table. The way i do it, it inserts only one row into the target table. I am not able to understand what the problem is. It would be great if anyone could help me.

Following is the logic i am using:

First procedure
- select the source table data into a cursor.
- using for loop i pass the values into rowtype variable of target table.
- now i call the second procedure from here passing the values stored in the
rowtype.

Second procedure
- take the values from the first procedure and insert them into the table.

So far, i have 4 rows in the source table, but i am able to insert only 1 row.

I am guessing the problem might be with the calling of the procedure, but m not sure.

When i try to print the values from the first procedure without calling the second procedure, it prints the values for all the 4 rows.

Please help.

thanks,
Nabh4u.

[amitpatel66]

Could you please post the procedure 1 and procedure 2 code here for reference of our experts?

Why dont you perform what ever you want to in a single procedure itself??

[nabh4u]

Thanks Amit for a quick response. I am not using a single procedure to do everything because i will be inserting data into different tables and if i include everything into one procedure it will be a very huge one. The problem i have stated is the initial one.

Following is what i am trying:

First Procedure:

create or replace first_proc(v_out OUT number) as
cursor a is select col_1,col_2 from source table;
--where col_1 is a 4 char field and col_2 is a 50 char field.
--col_1 is used to differentiate between data and i do substring on col_2 to get 
--data for individual fields on the target table.
 
cursor b is select col_1,col_2 from source table 
where col_1='1001' or col_1='1002';
-- I have multiple rows in the source table for col_1 = '1001 and '1002'.
r     a%rowtype
s    b%rowtype
begin
for r in a loop
begin
if(r.col_1='1000') then
-- i do all the mainpulations on the field.
end if;
for s in b loop
begin
if(s.col_1='1001') then
-- place values into rowtype variable using substr with respect to the target table.
end if;
-- now i call the second procedure
second_proc(rowtype variable,return_val);
if(s.col_2='1002') then
-- place values into rowtype variable using substr with respect to the target table.
end if;
-- now i call the second procedure again to insert the second row.
second_proc(rowtype variable,return_val);
end;
end loop;
end;
end loop;
end;
 

second procedure

create or replace second_proc(v_num IN target_table%rowtype, error_val OUT number) as
 
begin
error_val :=0;
insert into target_table
values(v_num.a,v_num.b,,....,); [all the values we get from the first procedure].
commit;
end;

I think the problem is with the logic i am implementing to insert multiple rows. Is there any other way i can get around this problem. Actually, this is not a syntax or some other issue, the problem is that i am not getting all the rows inserted.

I am sorry i could'nt provide the actual code but the one i have provided is almost the same.

any suggestions would help.

[debasisdas]

why not simply call the 2nd procedure within procedure1 and pass the values .

[amitpatel66]

I would suggest you to do all the manipulations first then pass all the 4 records together to second procedure. Use table instead of rowtype variable.

[nabh4u]

why not simply call the 2nd procedure within procedure1 and pass the values .

Thanks for replying. My problem is that i will have many procedures and if i have all of them in the same procedure then it will be a huge one. I have to insert into many tables so i have a separate procedure for each target table.

Thanks,
Nabh4u

"Together we can and we will make a difference"

[nabh4u]

I would suggest you to do all the manipulations first then pass all the 4 records together to second procedure. Use table instead of rowtype variable.

Thanks Amit. Could you give some example logic for that? and also how can i use a table?

Thanks,
Nabh4u

"Together we can and we will make a difference"

[amitpatel66]

Thanks Amit. Could you give some example logic for that? and also how can i use a table?

Thanks,
Nabh4u

"Together we can and we will make a difference"

Something like this:

 
SQL> create or replace package p_type as
  2  TYPE emp_data is TABLE of emp%ROWTYPE;
  3  END p_type;
  4  /
Package created.
 
SQL> ed
Wrote file afiedt.buf
  1  CREATE OR REPLACE PROCEDURE proc2(in_data IN OUT NOCOPY p_type.emp_data) AS
  2  BEGIN
  3  FORALL I IN in_data.FIRST..in_data.LAST
  4  INSERT INTO emp2 VALUES in_data(i);
  5  COMMIT;
  6* END proc2;
SQL> /
 
SQL> CREATE OR REPLACE PROCEDURE proc1 AS
  2  empd p_type.emp_data;
  3  BEGIN
  4  SELECT * BULK COLLECT INTO empd FROM emp;
  5  proc2(empd);
  6  END proc1;
  7  /
 
 

This will give you an idea on how you can implement your requirement. I have not tested this so please check for any syntax errors.

[nabh4u]

Something like this:

 
SQL> create or replace package p_type as
  2  TYPE emp_data is TABLE of emp%ROWTYPE;
  3  END p_type;
  4  /
Package created.
 
SQL> ed
Wrote file afiedt.buf
  1  CREATE OR REPLACE PROCEDURE proc2(in_data IN OUT NOCOPY p_type.emp_data) AS
  2  BEGIN
  3  FORALL I IN in_data.FIRST..in_data.LAST
  4  INSERT INTO emp2 VALUES in_data(i);
  5  COMMIT;
  6* END proc2;
SQL> /
 
SQL> CREATE OR REPLACE PROCEDURE proc1 AS
  2  empd p_type.emp_data;
  3  BEGIN
  4  SELECT * BULK COLLECT INTO empd FROM emp;
  5  proc2(empd);
  6  END proc1;
  7  /
 
 

This will give you an idea on how you can implement your requirement. I have not tested this so please check for any syntax errors.

Thanks Amit. I will look into this and work on.

[vijay]

try 1nce more
the thing is when u call the procedure u might not be calling it for all the rows

hi,

I am trying to insert multiple rows in a table using PL/SQL. I have two procedures and two tables. the first procedure gets the data from the source table, manipulates it and then sends the data to the second procedure which inserts the data into the target table. The way i do it, it inserts only one row into the target table. I am not able to understand what the problem is. It would be great if anyone could help me.

Following is the logic i am using:

First procedure
- select the source table data into a cursor.
- using for loop i pass the values into rowtype variable of target table.
- now i call the second procedure from here passing the values stored in the
rowtype.

Second procedure
- take the values from the first procedure and insert them into the table.

So far, i have 4 rows in the source table, but i am able to insert only 1 row.

I am guessing the problem might be with the calling of the procedure, but m not sure.

When i try to print the values from the first procedure without calling the second procedure, it prints the values for all the 4 rows.

Please help.

thanks,
Nabh4u.