Home » SQL & PL/SQL » SQL & PL/SQL » help needed in procedure
help needed in procedure [message #219663] Thu, 15 February 2007 06:30 Go to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi all,

i have created a procedure to delete a table dynamically. if a table is deleted then the child table rows of the corresponding table will also be deleted.

code works fine and the child tables rows are getting deleted if the refrence key remains same for all the table.
my dounts are

>>>whether the code logic is correct or am i making it more complex.
>>>The problem is if the refrence key is different
then i cannot refrer them

tht is in the example below if the table is emps then its child emps_personal is getting deleted.
but if table name i give us depts then it is not able to reffer the name in emps_personal because the keys are different

can some one help me whether my logic is correct and how can i solve the issue.

thanks in advance Razz

here is the sample table and my procedure

CREATE TABLE depts
(
dept_no NUMBER NOT NULL,
d_name VARCHAR2(20),
CONSTRAINT PK_emps PRIMARY KEY (dept_no)
);
-----------
create table emps
(
emp_no number primary key,
ename varchar2(30),
dept_no number,
foreign key(dept_no) references depts
);
--------------------
create table emps_personal(emp_no number primary key references emps(emp_no),
sex varchar2(6),
status varchar2(10),
);
-----------------------
procedure

/*procedure to delete the table and its child based on the value passed*/

----parameters owner_name,table_name,column_name,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 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 LOOP
FOR cur3rec IN cur2(own_name,cur1rec.table_name,cur1rec.constraint_name) LOOP
dbms_output.put_line(cur1rec.table_name);
-- Dynamic cursor to get the parent column name and its corresponding value
DBMS_SQL.PARSE(cur4, 'select initcap('||cur3rec.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_output.put_line(lv_v_colname1);
END loop;----cursor cur4(dynamic) ends here
END LOOP;--cursor cur3rec ends here
FOR cur2rec IN cur2(cur1rec.owner,cur1rec.table_name,cur1rec.constraint_name) LOOP
dbms_output.put_line(cur2rec.column_name);
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_colname, 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 cur3(dynamic) ends here
--deletes the child table
lv_v_child_delete:= 'delete from '||UPPER(cur1rec.owner)||'.'||UPPER(cur1rec.table_name)||' where '
||UPPER(cur2rec.column_name)||' = '||''''||UPPER(lv_v_colname)||'''';
dbms_output.put_line(lv_v_child_delete);
EXECUTE IMMEDIATE lv_v_child_delete;
dbms_output.put_line(lv_v_child_delete);
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;
/
---------- procedure del_table ends-----------------------------------



[Updated on: Thu, 15 February 2007 06:32]

Report message to a moderator

Re: help needed in procedure [message #219688 is a reply to message #219663] Thu, 15 February 2007 09:43 Go to previous messageGo to next message
oskarsdba
Messages: 7
Registered: January 2007
Location: Latvia
Junior Member
Maybe you need just foreign Keys with cascade delete...
Re: help needed in procedure [message #219689 is a reply to message #219688] Thu, 15 February 2007 09:53 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

thanks for the time

but i cannot alter the key constraints of the tables created. the production tables are not to be altered.. so any other options.
Re: help needed in procedure [message #219765 is a reply to message #219663] Thu, 15 February 2007 20:30 Go to previous messageGo to next message
randerson@viops.com
Messages: 8
Registered: February 2007
Junior Member
My suggestion would be to write a before delete trigger on the parent table and delete all the records in the child table(s) where the child table fk = parent table pk. I wouldn't go to the trouble of trying to write a generic delete table procedure. Use the triggers that is what they are there for plus if the child delete fails you can log that information. Also if you have a Parent, Child, Grandchild situation and you have before delete triggers on each of the parent tables, Oracle will handle it all for you.

Just my $0.02 worth.

Rick Anderson
Re: help needed in procedure [message #219778 is a reply to message #219765] Thu, 15 February 2007 23:55 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,
thanks for the time and suggestion .
i will try doing it so..
Cool


Shanth

[Updated on: Thu, 15 February 2007 23:55]

Report message to a moderator

Re: help needed in procedure [message #219807 is a reply to message #219778] Fri, 16 February 2007 03:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I thought you said you cannot change the production tables.. How is creating/altering triggers different from altering constraints?
Re: help needed in procedure [message #219827 is a reply to message #219807] Fri, 16 February 2007 04:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well my guess would be you can only alter the constraints with an ALTER TABLE command, wheras triggers get their own CREATE statment, and so are obviously completely different.....
Re: help needed in procedure [message #219831 is a reply to message #219827] Fri, 16 February 2007 04:30 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member


hi frank,

thanks for the reply
yes you are right. its all the same.

i not sure whether this can be done through a procedure. so i thought its also an option to create a trigger or alter the constraints..
but it depends upon the client

so still working with the same procedures.. any suggestions for the procedures will be really help full...

thanks

hi JRowbottom ,
thanks for the time...
right now i dont have the luxury of altering a constraint.. is that possible through a procedure. if so where am i going wrong? any suggestions will be really helpfull..

thanks

Re: help needed in procedure [message #220301 is a reply to message #219831] Tue, 20 February 2007 02:46 Go to previous message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi all,

thanks for the help and suggestion.

finally i could create a procedure to delete the parent rows and corresponding all child rows...

thanks again.......

[Updated on: Tue, 20 February 2007 02:49]

Report message to a moderator

Previous Topic: CLCB to PDF File
Next Topic: plsql records
Goto Forum:
  


Current Time: Wed Dec 07 03:12:32 CST 2016

Total time taken to generate the page: 0.12636 seconds