MikeDelaney
asked on
DECLARE and DELETE in Oracle
I have the following SQL in oracle which is failing - can someone please tell what I'm doing wrong?
DECLARE ACCT VARCHAR(20 CHAR) := "00000000000011434562";
BEGIN
DELETE FROM deb.ACCOUNT WHERE CUSTOMERNO = ACCT;
DELETE FROM deb.CUSTOMER WHERE CUSTOMERNO = ACCT;
DELETE FROM deb.OTHERTABLE WHERE CUSTOMERNO = ACCT;
COMMIT;
END;
Note: I am using SQuerreL as my interface. --The following SQL works but I don't want to edit all the strings because in reality there are 20 tables to update.
DELETE FROM deb.ACCOUNT WHERE CUSTOMERNO = "00000000000011434562";
DELETE FROM deb.CUSTOMER WHERE CUSTOMERNO = "00000000000011434562";
DELETE FROM deb.OTHERTABLE WHERE CUSTOMERNO = "00000000000011434562";
COMMIT;
Any help would be greatly appreciated.
Mike
DECLARE ACCT VARCHAR(20 CHAR) := "00000000000011434562";
BEGIN
DELETE FROM deb.ACCOUNT WHERE CUSTOMERNO = ACCT;
DELETE FROM deb.CUSTOMER WHERE CUSTOMERNO = ACCT;
DELETE FROM deb.OTHERTABLE WHERE CUSTOMERNO = ACCT;
COMMIT;
END;
Note: I am using SQuerreL as my interface. --The following SQL works but I don't want to edit all the strings because in reality there are 20 tables to update.
DELETE FROM deb.ACCOUNT WHERE CUSTOMERNO = "00000000000011434562";
DELETE FROM deb.CUSTOMER WHERE CUSTOMERNO = "00000000000011434562";
DELETE FROM deb.OTHERTABLE WHERE CUSTOMERNO = "00000000000011434562";
COMMIT;
Any help would be greatly appreciated.
Mike
put your strings in single quotes, not double
try this :
DECLARE
ACCT VARCHAR2(20 CHAR) := "00000000000011434562";
BEGIN
DELETE FROM deb.ACCOUNT WHERE CUSTOMERNO = ACCT;
DELETE FROM deb.CUSTOMER WHERE CUSTOMERNO = ACCT;
DELETE FROM deb.OTHERTABLE WHERE CUSTOMERNO = ACCT;
COMMIT;
END;
/
also ensure there is no column with name ACCT in your tables otherwise it will not work. to be on the safer side, just have another variable name instead of ACCT.
so try this :
DECLARE
my_ACCT_var VARCHAR2(20 CHAR) := "00000000000011434562";
BEGIN
DELETE FROM deb.ACCOUNT WHERE CUSTOMERNO = my_ACCT_var;
DELETE FROM deb.CUSTOMER WHERE CUSTOMERNO = my_ACCT_var;
DELETE FROM deb.OTHERTABLE WHERE CUSTOMERNO = my_ACCT_var;
COMMIT;
END;
/
DECLARE
ACCT VARCHAR2(20 CHAR) := "00000000000011434562";
BEGIN
DELETE FROM deb.ACCOUNT WHERE CUSTOMERNO = ACCT;
DELETE FROM deb.CUSTOMER WHERE CUSTOMERNO = ACCT;
DELETE FROM deb.OTHERTABLE WHERE CUSTOMERNO = ACCT;
COMMIT;
END;
/
also ensure there is no column with name ACCT in your tables otherwise it will not work. to be on the safer side, just have another variable name instead of ACCT.
so try this :
DECLARE
my_ACCT_var VARCHAR2(20 CHAR) := "00000000000011434562";
BEGIN
DELETE FROM deb.ACCOUNT WHERE CUSTOMERNO = my_ACCT_var;
DELETE FROM deb.CUSTOMER WHERE CUSTOMERNO = my_ACCT_var;
DELETE FROM deb.OTHERTABLE WHERE CUSTOMERNO = my_ACCT_var;
COMMIT;
END;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DECLARE
s_account VARCHAR2(20) := '00000000000011434562';
s_query VARCHAR2(1000);
Try this :
s_account VARCHAR2(20) := '00000000000011434562';
s_query VARCHAR2(1000);
Try this :
BEGIN
FOR rec IN
(
select owner, table_name from all_tab_columns
where owner = 'DEB' and COLUMN_NAME = 'CUSTOMERNO'
)
LOOP
s_query:= 'DELETE FROM '|| rec.owner ||'.' || rec.table_name || ' WHERE CUSTOMERNO = ' || s_account;
execute immediate s_query;
END LOOP;
COMMIT;
END;
/
--choukssa
Sorry I messed up the last post
Try this
Try this
DECLARE
s_account VARCHAR2(20) := '00000000000011434562';
s_query VARCHAR2(1000);
BEGIN
FOR rec IN
(
select owner, table_name from all_tab_columns
where owner = 'DEB' and COLUMN_NAME = 'CUSTOMERNO'
)
LOOP
s_query:= 'DELETE FROM '|| rec.owner ||'.' || rec.table_name || ' WHERE CUSTOMERNO = ' || s_account;
execute immediate s_query;
END LOOP;
COMMIT;
END;
/
--choukssa
Made it generic and reduced potential extra updates.
--choukssa
DECLARE
s_account VARCHAR2(20) := '00000000000011434562';
s_query VARCHAR2(1000);
s_column VARCHAR2(50) := 'CUSTOMERNO';
s_owner VARCHAR2(50) := 'DEB';
BEGIN
FOR rec IN
(
select distinct table_name from all_tab_columns where owner = s_owner and COLUMN_NAME = s_column
)
LOOP
s_query:= 'DELETE FROM '|| s_owner ||'.' || rec.table_name || ' WHERE ' || s_column || ' = ' || s_account;
execute immediate s_query;
END LOOP;
COMMIT;
END;
/
--choukssa
looks ok to me.
But just a note here, if there are two tables ( one parent and one child which has foreign key relationship ), then your delete has to delete from the child first. If you try to delete from the parent when the child records exist, then you will get error. Not sure whether you have ON DELETE CASCADE for all your parent/child tables which can take care of deleting child records when you delete from the parent table.
Thanks
But just a note here, if there are two tables ( one parent and one child which has foreign key relationship ), then your delete has to delete from the child first. If you try to delete from the parent when the child records exist, then you will get error. Not sure whether you have ON DELETE CASCADE for all your parent/child tables which can take care of deleting child records when you delete from the parent table.
Thanks
In that case we can run a loop to disable all referential constraints that have that column before the delete script and enable them afterwards.
--choukssa
--choukssa
>>disable all referential constraints
I would not recommend disabling constraints on a live database unless there is absolutely no other way.
Doing so can allow dirty data to enter the system. This is even more likely if applications have been coded to trap integrity errors and take appropriate action.
I would not recommend disabling constraints on a live database unless there is absolutely no other way.
Doing so can allow dirty data to enter the system. This is even more likely if applications have been coded to trap integrity errors and take appropriate action.
ASKER
Sorry for the delay but this worked out great - Thanks so much.