473,748 Members | 9,913 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 5227
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
6112
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
1488
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
2747
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
1208
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
2053
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
4351
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
1898
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
1958
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
2168
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
8987
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
9534
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9366
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
9316
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
8239
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...
1
6793
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6073
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3303
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
3
2211
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.