drop/create table in procedure [message #381831] |
Tue, 20 January 2009 01:09  |
becenek
Messages: 2 Registered: January 2009 Location: Ankara , Turkey
|
Junior Member |
|
|
Hi,
i have four main tables and about 50 others on my database. There is a large number of relations between these four and others.
i' ve tried to truncate them but it failed. then i' ve learned that i could not truncate tables when they have relations with other tables and it doesn't matter if they are child or parent. i 've considered 2 options. i could delete all the data which takes quite a long time, or i could drop and recreate tables and relations.
I'm trying to do the second and already have the create scripts for each my necessary object including foreign keys and even comments. i' ve tried to create a procedure to execute when i need to empty them again.
is there any other way then EXECUTE IMMEDIATE to execute drop/ create commands in a procedure? because single quotes are causing so much trouble.
or... any ideas about the problem. any other way to empty tables without deleting/disabling foreign keys?
thanks.
|
|
|
|
Re: drop/create table in procedure [message #381850 is a reply to message #381831] |
Tue, 20 January 2009 02:08   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can just disable the constraints, truncate the tables and then re-enable the constraints:
You can disable all the FK constraints in a Schema very easily:
FOR rec in (SELECT table_name,constraint_name
FROM user_constraints
WHERE constraint_type = 'R') LOOP
execute immediate 'ALTER TABLE '||rec.table_name||' MODIFY CONSTRAINT '||constraint_name||' DISABLE';
END LOOP;
All you'd need to do would be to modify this code to truncate the tables after all the constraints were disabled, and then re-enable the constraints.
|
|
|
Re: drop/create table in procedure [message #381867 is a reply to message #381831] |
Tue, 20 January 2009 02:52  |
becenek
Messages: 2 Registered: January 2009 Location: Ankara , Turkey
|
Junior Member |
|
|
Littlefoot, JRowbottom thanks for the reply.
i think importing and exporting schema would be a fine way but i'm not sure about the speed. i mean, some of my tables contain data i don't want to loose and if i export the schema with the data... well as i say. i'm not sure about the speed.
i think i'll use the second idea. using user_constraints table to disable foraign keys should do the trick.
Thanks for help.
|
|
|