Solved

DECLARE and DELETE in Oracle

Posted on 2011-03-03
10
461 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:MikeDelaney
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 35029663
put your strings in single quotes,  not double
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35032812
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;
/
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 35032825
missed to change quotes, 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;
/
0
 
LVL 2

Expert Comment

by:choukssa
ID: 35041376
DECLARE

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;
/

Open in new window

--choukssa
0
 
LVL 2

Expert Comment

by:choukssa
ID: 35041382
Sorry I messed up the last post


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;
/

Open in new window

--choukssa
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:choukssa
ID: 35041392
Made it generic and reduced potential extra updates.
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;
/

Open in new window


--choukssa
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35054049
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
0
 
LVL 2

Expert Comment

by:choukssa
ID: 35056472
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35056483
>>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.
0
 

Author Closing Comment

by:MikeDelaney
ID: 35335511
Sorry for the delay but this worked out great - Thanks so much.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help with Oracle syntax 4 56
JDeveloper 12c for 32 bit 4 71
Oracle SQL Select within a Where Clause 9 58
Oracle -- identify blocking session 24 43
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now