| moving data from one table to mutliple tables [message #41029] |
Tue, 19 November 2002 12:52  |
thundercat
Messages: 10
Registered: August 2002
|
Junior Member |
|
|
Hello,
I have a flat table that contains information that will be moved into several tables.
The way I'm doing this is using a stored procedure.
the cursor will access the temp table, and for each record several insert statements will run to populate the required tables. I'm not sure how to catch the exceptions. They way its set up, the cursor will incroment after an exception is raised. I want all insert statements to execute before the cusor is incremented
Here is a quick alogrythm for what I'm doing
Open cursur
fetch data
Insert ******
Insert*******
Insert*******
Exception
loop until cusor%NOTFOUND
any advice is greatly appreciated
Thanks in advance
|
|
|
|
| Re: moving data from one table to mutliple tables [message #41030 is a reply to message #41029] |
Tue, 19 November 2002 12:57  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The most common construct for this type of job is:
for r in (select * from temp) loop
begin
insert ... values (r.c1, r.c2, ...);
insert ...;
insert ...;
exception
when others then
-- handle error - log it, etc.
end;
end loop;
Note the CURSOR FOR loop instead of an explicit cursor (open/fetch/close).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
