Rows not deleting in PL/SQL 1

[RustyAfro]

This is probably a noobie question with an obvious answer but it is elluding me.

I execute this command in PL/SQL:

DELETE from MyTable
Commit;

And I see it process in .xx seconds.

But when I query the table the rows are still there. I though the "Commit" would force the deletion. What am I missing?

Thanks,

Joel

 

[SantaMufasa]

Joel,

Since the above code is an excerpt from your PL/SQL, can you please post the code between your PL/SQL "BEGIN" statement down to the DELETE/COMMIT code?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

Ah.. <

So I have to wrap the statement in a Begin/End?

BEGIN
DELETE from uwtbl_ab2012plancodes
Commit;
END

Would that be the proper syntax? I get an "end-of-file" error when trying to execute this.

Sorry, I'm new to PL/SQL and to action queries in Oracle...

 

[RustyAfro]

I should mention that the first delete finally seems to have deleted the rows after 10 minutes or so. Wierd...I'm much more of a SQL Server guy so this is a little different for me.

 

[RustyAfro]

If you could, what would be the correct way to delete rows as close to instantly as possible?

 

[SantaMufasa]

First, the correct code for your PL/SQL block, above is (presuming that you are running in SQL*Plus):

BEGIN
DELETE from uwtbl_ab2012plancodes;
Commit;
END;
/

Notice the semi-colons that I added, above, and the "/" at the end to run in the SQL*Plus environment.


By far, the fastest method to empty out a table is (at the SQL*Plus prompt):

truncate table uwtbl_ab2012plancodes;

From within PL/SQL, that same code would be:

BEGIN
    execute immediate 'truncate table uwtbl_ab2012plancodes';
end;
/

With the TRUNCATE command, it does an implicit COMMIT.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

That is perfect, thanks so much :-D

 

[Beilstwh]

please be aware of one thing. If you do a delete you can rollback the delete before you issue the commit. A truncate works almost instantly, but you can't change your mind and anything else you have done (insert, update, delete) to any other table is made permanent.

Bill
Oracle DBA/Developer
New York State, USA