473,698 Members | 2,439 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

nabh4u
62 New Member
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 5226
amitpatel66
2,367 Recognized Expert Top Contributor
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 New Member
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 Recognized Expert Expert
why not simply call the 2nd procedure within procedure1 and pass the values .
Mar 13 '08 #4
amitpatel66
2,367 Recognized Expert Top Contributor
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 New Member
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 New Member
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 Recognized Expert Top Contributor
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 New Member
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 New Member
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

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

Similar topics

3
6109
by: DarthMacgyver | last post by:
Hello, I recently wrote a survey application. Each question is very similar. The first questions gives me a problem when there are multiple people taking the survey (The Database connection Timed out) I am using the Data Access Application Blocks as ASP.NET (using VB.NET) and SQL 2000. In there first question there can be up to 27 answers. So I figured instead of making 27 different trips to the database I woulc just concatenate my...
9
1485
by: Mike R | last post by:
Hi, I cant figure out how to do this.... for example: Select name from mytab order by col1 could return Mike
4
2744
by: Bradley Burton | last post by:
I'm using Allen Brown's code for audit logging (http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud table doesn't populate with the tracking info at all. I think it might be a problem with the table set-up. I just can't find the problem. These are the fields in my table: Table1 ID (primary key) AutoNumber
1
1206
by: Irfan | last post by:
hi, I tried to google 'sql to insert multiple row MSaccess' but coudnt get a proper link. This is roughly what i want to do. .. I have Material, Load & filename as Columns in a table.All i want to do is copy all the rows having filename as 'x' in the same table.
3
2051
by: Joe Fawcett | last post by:
Dear All We have an application where users login, their name/password details are stored in a table, and now we want to add a third party bulletin board that lives in its own virtual directory. Both applications are running on the same IIS. How do we authenticate users so that they don't have to sign in again without passing their name and password in the querystring or request header? The login details are the same for both...
7
4342
by: Lorenzino | last post by:
Hi, I have a problem with bindings in a formview. I have a formview; in the insert template i've created a wizard control and inside it i have an HTML table with some textboxes bound to the sqldatasource of the formview. If i put this textboxes outside the table everything works well, but as soon as i put them inside the table (in order to organize the layout in the right way) they doesn't work. They works only as eval() and not bind()...
2
1896
by: underground | last post by:
I need a little help figuring this one out. I have a script that should post mutiple binary files into a single row but instead of copying the indiviuals files it rewrites the first file to all the other columns in the row... Could someone help me with this . Below is the complete script...and form.. <?php if(isset($_POST) && $_FILES && $_FILES && $_FILES && $_FILES && $_FILES && $_FILES && $_FILES > 0) { $fileName = $_FILES; $tmpName =...
10
1953
by: underground | last post by:
I need a little help figuring this one out. I have a script that I've modified to post mutiple binary files into a single row but instead of copying the indiviuals files it rewrites the first file to all the other columns in the row... Could someone help me with this . Below is the complete script...and form.. <?php if(isset($_POST) && $_FILES && $_FILES && $_FILES && $_FILES && $_FILES && $_FILES && $_FILES > 0) { $fileName = $_FILES;...
6
2166
by: dewraj | last post by:
Hi I am using windows applicaiton(.net) as front end, and I want to get mutiple tables in Dataset by executing a single stored procedure i.e. SP would return mutiple tables (record sets) with the user specified name. can anybody guide me no this? thanks.
0
8683
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9031
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8904
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8876
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7741
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4372
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4624
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3052
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2341
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.