Home » SQL & PL/SQL » SQL & PL/SQL » drop/create table in procedure (Oracle 10g)
icon5.gif  drop/create table in procedure [message #381831] Tue, 20 January 2009 01:09 Go to next message
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 #381840 is a reply to message #381831] Tue, 20 January 2009 01:32 Go to previous messageGo to next message
Littlefoot
Messages: 20890
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, EXECUTE IMMEDIATE is the only way to drop a table (or truncate it or execute any other DDL) from a PL/SQL.
Obviously, you can't delete a record if it is a master for another record(s), so you'll need to disable these constraints.

As you need only 4 "permanent" tables, how about dropping the whole schema and IMPORTING it back? This might be OK if these tables are not involved with other users (constraints? privileges?).
Re: drop/create table in procedure [message #381850 is a reply to message #381831] Tue, 20 January 2009 02:08 Go to previous messageGo to next message
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.

icon7.gif  Re: drop/create table in procedure [message #381867 is a reply to message #381831] Tue, 20 January 2009 02:52 Go to previous message
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.
Previous Topic: uninitialized composite
Next Topic: how to write this query
Goto Forum:
  


Current Time: Sat Dec 03 13:40:18 CST 2016

Total time taken to generate the page: 0.05833 seconds