Home » SQL & PL/SQL » SQL & PL/SQL » Delete all records from all tables
Delete all records from all tables [message #225478] Tue, 20 March 2007 01:33 Go to next message
karthi
Messages: 42
Registered: December 2000
Location: B'lore
Member

Hi all,

I'm writing a procedure to delete all records from all tables, first it will delete any child records exists, thn it will delete the master table records...i've pasted the code, but how to make it into loop or its there any other way to re-write the code.

DECLARE
A VARCHAR2(100);
B VARCHAR2(100);
T VARCHAR2(100);
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM PURCHASE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
A := SUBSTR(SQLERRM, INSTR(SQLERRM, '(', 1)+1, INSTR(SQLERRM, ')', 1)-INSTR(SQLERRM, '(', 1)-1);
B := SUBSTR(A, INSTR(A, '.')+1, LENGTH(A)-INSTR(A, '.'));
DBMS_OUTPUT.PUT_LINE(A||', '||B);
SELECT TABLE_NAME INTO T FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = ''||B||'';
DBMS_OUTPUT.PUT_LINE(T);
EXECUTE IMMEDIATE 'DELETE FROM '||T;
END;

thanks
-Karthik
Re: Delete all records from all tables [message #225482 is a reply to message #225478] Tue, 20 March 2007 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why not "ON DELETE CASCADE" on your foreign keys?

Regards
Michel
Re: Delete all records from all tables [message #225485 is a reply to message #225482] Tue, 20 March 2007 01:46 Go to previous messageGo to next message
karthi
Messages: 42
Registered: December 2000
Location: B'lore
Member

Due to some reasons, we didnt use ON DELETE CASCADE method.
Re: Delete all records from all tables [message #225489 is a reply to message #225482] Tue, 20 March 2007 01:51 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

i too came up with a situation to create a genric procedure for deleting.. because i could not alter the production tables..

so i created this procedure
hope this helps and this is what you are looking for..

the parameters are
owner name,
table name, 
column name,
column value


CREATE OR REPLACE PROCEDURE del_table(own_name IN varchar2,tbl_name IN varchar2,par IN varchar2,val IN
varchar2)
IS
BEGIN
DECLARE
lv_v_error_message VARCHAR2(255);
lv_n_error_code NUMBER;
lv_v_colname VARCHAR2(10);
lv_v_colname1 varchar2(10);
lv_v_child_delete VARCHAR2(254);
lv_v_parent_delete VARCHAR2(254);
e_no_data_exception EXCEPTION;
cur3  INTEGER;
cur4  INTEGER;
v_rows  INTEGER;
/* cursor to get the child of the given table*/
CURSOR cur1 (own_name varchar2,tbl_name varchar2)IS
	 SELECT b.owner,b.table_name,b.constraint_name
	 FROM all_constraints a, all_constraints b
	 WHERE  b.r_constraint_name=a.constraint_name
	 AND b.constraint_type='R'
	 AND a.owner=UPPER(own_name)
	 AND a.table_name =UPPER(tbl_name);
/*cursor to get the column name refrenced */
CURSOR cur2(own_nam varchar2,tab_name varchar2,con_name varchar2) IS
	SELECT constraint_name,column_name from all_cons_columns
	WHERE owner=UPPER(own_nam)
	AND table_name=UPPER(tab_name)
	AND constraint_name=upper(con_name);
BEGIN
cur3 := DBMS_SQL.OPEN_CURSOR;
cur4 := DBMS_SQL.OPEN_CURSOR;
	   FOR cur1rec IN cur1(own_name,tbl_name)  LOOP
		 	FOR cur2rec IN cur2(own_name,cur1rec.table_name,cur1rec.constraint_name) LOOP
 	  	     -- Dynamic cursor to get the parent column name and its corresponding value
			    DBMS_SQL.PARSE(cur4, 'select initcap('||cur2rec.column_name||') from  '||UPPER(tbl_name)||
				' where '||par||' = '||''''||val||'''', DBMS_SQL.V7);
	   			DBMS_SQL.DEFINE_COLUMN_CHAR(cur4,1,lv_v_colname1, 20);
		 		v_rows := DBMS_SQL.EXECUTE(cur4);
			 	LOOP
			      IF DBMS_SQL.FETCH_ROWS(cur4) = 0 THEN
			       EXIT;
			      END IF;
				  DBMS_SQL.COLUMN_VALUE_CHAR(cur4,1,lv_v_colname1);
			      DBMS_SQL.PARSE(cur3, 'select initcap('||cur2rec.column_name||') from  '||UPPER(cur1rec.table_name)||
				' where '||cur2rec.column_name||' = '||''''||lv_v_colname1||'''', DBMS_SQL.V7);
	   			DBMS_SQL.DEFINE_COLUMN_CHAR(cur3,1,lv_v_colname1, 20);
		 		v_rows := DBMS_SQL.EXECUTE(cur3);
			 	LOOP
			      if DBMS_SQL.FETCH_ROWS(cur3) = 0 THEN
			       EXIT;
			      END if;
				  DBMS_SQL.COLUMN_VALUE_CHAR(cur3,1,lv_v_colname);
				  END LOOP;--------------------------------------------------------cursor cur4(dynamic) ends here
			  	 --deletes the child table
				 del_table(own_name,cur1rec.table_name,cur2rec.column_name,lv_v_colname);
					  lv_v_child_delete:= 'delete from '||UPPER(cur1rec.owner)||'.'||UPPER(cur1rec.table_name)||' where '
		     ||UPPER(cur2rec.column_name)||' = '||''''||UPPER(lv_v_colname)||'''';
		    EXECUTE IMMEDIATE lv_v_child_delete;--------------------------------------------------------------cursor cur3rec ends here
				 END LOOP; 
   END LOOP;------------------------------------------------------------------------cursor cur2rec ends here
   END LOOP;---------------------------------------------------------------------------- cursor cur1rec ends here
	 lv_v_parent_delete:='delete from '||own_name||'.'||tbl_name||' where '||par||' = '||''''||val||'''';--deletes the parent table
 EXECUTE IMMEDIATE	lv_v_parent_delete;
 END;
END del_table;
/


rgds,
shanth

[Updated on: Tue, 20 March 2007 02:09]

Report message to a moderator

Re: Delete all records from all tables [message #225494 is a reply to message #225489] Tue, 20 March 2007 02:12 Go to previous messageGo to next message
karthi
Messages: 42
Registered: December 2000
Location: B'lore
Member

Hi,

What parameter to be passed to PAR & VAL ?
Every time i can pass the table name.

-Karthik
Re: Delete all records from all tables [message #225497 is a reply to message #225494] Tue, 20 March 2007 02:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read the code.
If you don't understand it, don't use it! (That goes for all code you find on the internet)
Re: Delete all records from all tables [message #225498 is a reply to message #225494] Tue, 20 March 2007 02:17 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi

the parameters are
owner name,
table name,
column name,
column value

>>karthi@:Every time i can pass the table name.

i did not get what do u want by the above. are u asking that whether you have to pass or????

regards,
shanth
Re: Delete all records from all tables [message #225499 is a reply to message #225498] Tue, 20 March 2007 02:20 Go to previous messageGo to next message
karthi
Messages: 42
Registered: December 2000
Location: B'lore
Member

Yes, Every i cant pass the owner name, tablename, column, etc
Within single procedure, it has to delete all the records from all the tables.

Re: Delete all records from all tables [message #225502 is a reply to message #225499] Tue, 20 March 2007 02:39 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

this procedure deletes all the childs of the given parent table

that is

here is the example

SQL> select * from emps;

    EMP_NO ENAME                             DEPT_NO D_NAME
---------- ------------------------------ ---------- --------------------
    SALARY      BONUS        HRA DATE_OF_J
---------- ---------- ---------- ---------
         1 shanth                                  1
     10000       4000        600 05-JUN-06

         2 kumara                                  2
     11000       4100        610 06-JUN-06


SQL> select * from depts;

   DEPT_NO D_NAME
---------- --------------------
         1 production
         2 finance
         3 hr
         4 delivery

SQL> select * from emps_qual;

    EMP_NO SKILLSET
---------- ----------
         1 mca
         2 mca

SQL> exec del_table('apps','depts','dept_no',1);

PL/SQL procedure successfully completed.





SQL> select * from depts;

   DEPT_NO D_NAME
---------- --------------------
         2 finance
         3 hr
         4 delivery

SQL> select * from emps;

    EMP_NO ENAME                             DEPT_NO D_NAME
---------- ------------------------------ ---------- --------------------
    SALARY      BONUS        HRA DATE_OF_J
---------- ---------- ---------- ---------
         2 kumara                                  2
     11000       4100        610 06-JUN-06


SQL> select * from emps_qual;

    EMP_NO SKILLSET
---------- ----------
         2 mca


it is the way this works and you have to pass only the parent table and it deletes all the child table and the given parent table.

regards,
shanth.
Re: Delete all records from all tables [message #225507 is a reply to message #225502] Tue, 20 March 2007 03:24 Go to previous messageGo to next message
karthi
Messages: 42
Registered: December 2000
Location: B'lore
Member

I got it what you are telling?

But what i wanted is...
1. I dont want to pass any parameters.
2. If i execute a procedure, then automatically it has to delete all the records from all the table.
3. For eg. if it takes "PURCHASE" table, then it has to find automatically the child tables and to delete the child records and parent records, so this loop has to continue till it deletes all the records from all the tables.
I hope you understood.

-Karthik
Re: Delete all records from all tables [message #225518 is a reply to message #225507] Tue, 20 March 2007 03:50 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,
ya i got it now.

rgds,
shanth

[Updated on: Tue, 20 March 2007 04:15]

Report message to a moderator

Re: Delete all records from all tables [message #225643 is a reply to message #225507] Tue, 20 March 2007 12:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
karthi wrote on Tue, 20 March 2007 09:24
But what i wanted is...
1. I dont want to pass any parameters.
2. If i execute a procedure, then automatically it has to delete all the records from all the table.
3. For eg. if it takes "PURCHASE" table, then it has to find automatically the child tables and to delete the child records and parent records, so this loop has to continue till it deletes all the records from all the tables.
I hope you understood.

-Karthik


Who will get paid for this job? You will. So why don't YOU put some effort in it? This site is not some sort of code-generator. shanthkumaar provided you with a nice piece of code, and all you have to do is adjust it to your needs.
Previous Topic: Getting error in procedure
Next Topic: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS
Goto Forum:
  


Current Time: Sat Dec 03 04:12:19 CST 2016

Total time taken to generate the page: 0.26589 seconds