Home » SQL & PL/SQL » SQL & PL/SQL » delete A QUEUE TABLE HOW TO ?
delete A QUEUE TABLE HOW TO ? [message #294475] Thu, 17 January 2008 13:42 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
BEGIN

FOR rec in (
select 'drop '||object_type||' '||object_name cmd
from user_objects
where object_type in ('PACKAGE','PROCEDURE','SEQUENCE','VIEW')
union all
select 'drop table '||table_name || ' cascade constraints PURGE'
from user_tables
) LOOP
execute immediate rec.cmd;
END LOOP;

END;
/

i USE THIS SCRIPT TO CLEAN ALL TABLES, VIEWS, PACKAGES ETC .i ALSO HAVE A QUEUE TABLE. hOW TO DELETE queue TABLE USING THIS SCRIPT ?
Re: delete A QUEUE TABLE HOW TO ? [message #294478 is a reply to message #294475] Thu, 17 January 2008 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use dbms_aqadm package.

Regards
Michel
Re: delete A QUEUE TABLE HOW TO ? [message #294482 is a reply to message #294478] Thu, 17 January 2008 14:06 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Please tell me more on using that ?
Re: delete A QUEUE TABLE HOW TO ? [message #294486 is a reply to message #294482] Thu, 17 January 2008 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_aqadm.htm#i1011175

Regards
Michel
Re: delete A QUEUE TABLE HOW TO ? [message #294489 is a reply to message #294486] Thu, 17 January 2008 15:02 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
iam doing this to drop queue table

declare
queue_table varchar2(200);
begin
queue_table := 'ND_NOTIF_QUEUE_STORAGE';
BMS_AQADM.DROP_QUEUE_TABLE(queue_table, true, true);
end;

I get this error
ORA-06550: line 5, column 3:
PLS-00201: identifier 'BMS_AQADM.DROP_QUEUE_TABLE' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored


what is wrong ?

Re: delete A QUEUE TABLE HOW TO ? [message #294490 is a reply to message #294475] Thu, 17 January 2008 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
typo

>BMS_AQADM.DROP_QUEUE_TABLE(queue_table, true, true);
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table, true, true);

Re: delete A QUEUE TABLE HOW TO ? [message #294492 is a reply to message #294490] Thu, 17 January 2008 15:17 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
one more favour I want to run the above script only if the queue tablke is present how to do this ?
Re: delete A QUEUE TABLE HOW TO ? [message #294501 is a reply to message #294492] Thu, 17 January 2008 16:41 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
declare
queue_table varchar2(200);
begin
queue_table := 'ND_NOTIF_QUEUE_STORAGE';
BMS_AQADM.DROP_QUEUE_TABLE(queue_table, true, true);
end;

I want to execute this script only if the table ND_NOTIF_QUEUE_STORAGE is present how can I do this ?
Re: delete A QUEUE TABLE HOW TO ? [message #294548 is a reply to message #294501] Thu, 17 January 2008 23:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just execute it, if it is not there then you get an error that you can possibly trap in an exception if you want.

Regards
Michel

[Updated on: Fri, 18 January 2008 08:28]

Report message to a moderator

Re: delete A QUEUE TABLE HOW TO ? [message #294693 is a reply to message #294548] Fri, 18 January 2008 08:26 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Please can you show me the code used to catch exception in scripts.
Re: delete A QUEUE TABLE HOW TO ? [message #294696 is a reply to message #294693] Fri, 18 January 2008 08:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 10 Handling PL/SQL Errors

Regards
Michel
Previous Topic: ORA-01917: user or role '' does not exist
Next Topic: Problem writing a sql query for a select list item in a form
Goto Forum:
  


Current Time: Sat Dec 03 12:17:19 CST 2016

Total time taken to generate the page: 0.10579 seconds