Dropping Tables
Below are some examples of how to create tables in Oracle 12. PL/SQL, for the most part PL/SQL follows standard SQL conventions.
Drop a Table
Normal way to drop a table.
|
1 |
DROP TABLE EventRequest; |
Nothing too fancy here, just drop the table.
Check before Dropping
While if you issue the DROP TABLE command and the table doesn’t exist, you will get an error message, however it isn’t too severe and can be ignored. Although in completeness I will show how to suppress or receive error messages when dropping a table.
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN EXECUTE IMMEDIATE 'DROP TABLE EventRequest'; -- Other option is to use PURGE option -- EXECUTE IMMEDIATE 'DROP TABLE EventRequest PURGE'; WHEN OTHERS THEN -- show the error message DBMS_OUTPUT.put_line(SQLERRM); -- suppress the error message -- NULL END; |
In the above example there are a couple of options that you can use. Remove the comment indicator “–” from the beginning of the line, and add the comment indicator, “–” to the other line.
The first one is if you want to immediately PURGE the table, and not send it to the recycle bin, you can use the PURGE option at the end of the DROP TABLE statement.
The next option would be to show or suppress the error messages. You can either send the error message about the table not existing when trying to drop the table to NULL; and therefore never see the error message. Or you may want to see error messages related to trying to drop the table.
I hope you found the above informative, let me know in the comments below.
— michael.data@eipsoftware.com
Leave a Reply