469,343 Members | 5,420 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,343 developers. It's quick & easy.

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

nabh4u
62
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.
Mar 12 '08 #1
13 4925
amitpatel66
2,367 Expert 2GB
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??
Mar 12 '08 #2
nabh4u
62
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:

Expand|Select|Wrap|Line Numbers
  1. create or replace first_proc(v_out OUT number) as
  2. cursor a is select col_1,col_2 from source table;
  3. --where col_1 is a 4 char field and col_2 is a 50 char field.
  4. --col_1 is used to differentiate between data and i do substring on col_2 to get 
  5. --data for individual fields on the target table.
  6.  
  7. cursor b is select col_1,col_2 from source table 
  8. where col_1='1001' or col_1='1002';
  9. -- I have multiple rows in the source table for col_1 = '1001 and '1002'.
  10. r     a%rowtype
  11. s    b%rowtype
  12. begin
  13. for r in a loop
  14. begin
  15. if(r.col_1='1000') then
  16. -- i do all the mainpulations on the field.
  17. end if;
  18. for s in b loop
  19. begin
  20. if(s.col_1='1001') then
  21. -- place values into rowtype variable using substr with respect to the target table.
  22. end if;
  23. -- now i call the second procedure
  24. second_proc(rowtype variable,return_val);
  25. if(s.col_2='1002') then
  26. -- place values into rowtype variable using substr with respect to the target table.
  27. end if;
  28. -- now i call the second procedure again to insert the second row.
  29. second_proc(rowtype variable,return_val);
  30. end;
  31. end loop;
  32. end;
  33. end loop;
  34. end;
  35.  
second procedure
Expand|Select|Wrap|Line Numbers
  1. create or replace second_proc(v_num IN target_table%rowtype, error_val OUT number) as
  2.  
  3. begin
  4. error_val :=0;
  5. insert into target_table
  6. values(v_num.a,v_num.b,,....,); [all the values we get from the first procedure].
  7. commit;
  8. 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.
Mar 12 '08 #3
debasisdas
8,127 Expert 4TB
why not simply call the 2nd procedure within procedure1 and pass the values .
Mar 13 '08 #4
amitpatel66
2,367 Expert 2GB
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.
Mar 13 '08 #5
nabh4u
62
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"
Mar 13 '08 #6
nabh4u
62
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"
Mar 13 '08 #7
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> create or replace package p_type as
  3.   2  TYPE emp_data is TABLE of emp%ROWTYPE;
  4.   3  END p_type;
  5.   4  /
  6. Package created.
  7.  
  8. SQL> ed
  9. Wrote file afiedt.buf
  10.   1  CREATE OR REPLACE PROCEDURE proc2(in_data IN OUT NOCOPY p_type.emp_data) AS
  11.   2  BEGIN
  12.   3  FORALL I IN in_data.FIRST..in_data.LAST
  13.   4  INSERT INTO emp2 VALUES in_data(i);
  14.   5  COMMIT;
  15.   6* END proc2;
  16. SQL> /
  17.  
  18. SQL> CREATE OR REPLACE PROCEDURE proc1 AS
  19.   2  empd p_type.emp_data;
  20.   3  BEGIN
  21.   4  SELECT * BULK COLLECT INTO empd FROM emp;
  22.   5  proc2(empd);
  23.   6  END proc1;
  24.   7  /
  25.  
  26.  
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.
Mar 13 '08 #8
nabh4u
62
Something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> create or replace package p_type as
  3.   2  TYPE emp_data is TABLE of emp%ROWTYPE;
  4.   3  END p_type;
  5.   4  /
  6. Package created.
  7.  
  8. SQL> ed
  9. Wrote file afiedt.buf
  10.   1  CREATE OR REPLACE PROCEDURE proc2(in_data IN OUT NOCOPY p_type.emp_data) AS
  11.   2  BEGIN
  12.   3  FORALL I IN in_data.FIRST..in_data.LAST
  13.   4  INSERT INTO emp2 VALUES in_data(i);
  14.   5  COMMIT;
  15.   6* END proc2;
  16. SQL> /
  17.  
  18. SQL> CREATE OR REPLACE PROCEDURE proc1 AS
  19.   2  empd p_type.emp_data;
  20.   3  BEGIN
  21.   4  SELECT * BULK COLLECT INTO empd FROM emp;
  22.   5  proc2(empd);
  23.   6  END proc1;
  24.   7  /
  25.  
  26.  
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.
Mar 13 '08 #9
vijay
30
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.
Mar 17 '08 #10
QVeen72
1,445 Expert 1GB
Hi,

I'am not sure about this, but,
you have opened cursor for col_1='1001' OR col_1='1002'

And in second If condition you are checking for Col_2
IF(s.col_2='1002') THEN

is the logic appropriate...?

Regards
Veena
Mar 17 '08 #11
nabh4u
62
try 1nce more
the thing is when u call the procedure u might not be calling it for all the rows
Thanks Vijay. The way i have the logic setup i am getting all the rows. I call the procedure as and when i read a row completely.
Mar 18 '08 #12
nabh4u
62
Hi,

I'am not sure about this, but,
you have opened cursor for col_1='1001' OR col_1='1002'

And in second If condition you are checking for Col_2
IF(s.col_2='1002') THEN

is the logic appropriate...?

Regards
Veena
Thanks Veena. The logic is correct according to what i want to achieve.

I finally figured out the problem, It was related to the unique key consrtaint of the primary key. I think it was setup wrong.

Thank you all for the support. I really appreciate your help.

Thanks,
Nabh4u..

"together we can and we did make a difference"
Mar 18 '08 #13
vijay
30
can u give ur mail id '

i wil send some decoumnts resolving ur problem
May 30 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by DarthMacgyver | last post: by
9 posts views Thread by Mike R | last post: by
4 posts views Thread by Bradley Burton | last post: by
1 post views Thread by Irfan | last post: by
3 posts views Thread by Joe Fawcett | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.