?
Solved

Oracle trigger (insert multiple rows into table)

Posted on 2014-11-13
12
Medium Priority
?
793 Views
Last Modified: 2014-11-15
Greeting,
I have a trigger in Oracle database. It select a single record from one table and insert into another table. Now the select might return more than 1 record. Please suggest.

//Below is the code it select a record from table
BEGIN
          select phone into pp_nextel_phone
          where shop_name=:new.shop;
          EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
          pp_nextel_phone   := null;
          pp_mssg_type      :=null;
          END;

//below is the code I insert into another table
my_nextel_phone:=pp_nextel_phone;
 insert into mssg_log(PHONE,mssg_type,PCODE,PHSDSC,extra_dsc)
          values(my_nextel_phone,my_mssg_type,p_Problem_code,:new.description,:new.long_desc);
0
Comment
Question by:mrong
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 1

Expert Comment

by:ora-600
ID: 40440216
Hi,
you mustn't return multiple results. This will fail.
You should extend your exception handling and handle multiple results there.
Regards
0
 

Author Comment

by:mrong
ID: 40440241
Can you provide sample code? thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40440245
what do you want it to do when the select returns more than one row?
0
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

Author Comment

by:mrong
ID: 40440260
keep inserting into table mssg_log.
0
 
LVL 1

Expert Comment

by:ora-600
ID: 40440293
well, but what do you want to insert if you get 2 or 5 results?
- the first?
- the last?
- the average?
0
 

Author Comment

by:mrong
ID: 40440317
insert all of the matching phone into table. thanks.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40440370
don't break it into multiple steps, instead of selecting into variable and then inserting those variables

just insert from the select directly

 insert into mssg_log(PHONE,mssg_type,PCODE,PHSDSC,extra_dsc)
select phone, my_mssg_type,p_Problem_code,:new.description,:new.long_desc
from your_table
where  shop_name=:new.shop;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40440950
I agree with sdstuber, that usually it is best to use SQL to solve your data problems directly in SQL.  But, if you want to use a PL\SQL cursor for this for some reason, use a cursor loop with an insert in the loop.
0
 

Author Comment

by:mrong
ID: 40441056
sdstuber,
this will work even the select returns multiple records?
Thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40441073
>> this will work even the select returns multiple records?

yes


simple test to illustrate

create table testtable (n number);
insert into testtable select object_id from all_objects;

Open in new window


testtable should have many rows in it after the insert
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40441109
I would recommend changing the insert in that test to this:
insert into testtable select object_id from all_objects
where rownum < 11;

That will prove the point without wasting a lot of time (and disk space).
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40441141
It only took 3 seconds for the table to be created on my test db (61k rows) and it's small only 1mb.

They probably had the test already completed and dropped up before either of these responses came in.

but yes, 10 rows would have been sufficient
I kind of wanted to show it working for a bunch though.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question