If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Originally posted by tamilselvan The syntax is wrong.
It should be
EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'DEF$_AQCALL');

This is exactly the same syntax as Raff is using. The only difference is that you are using named parameter notation while he is using positional notation. But appart from this "cosmetic" difference both shold behave exactly the same.

Raff, are you sure DEF$_AQCALL is indeed a queue table? Does it show up when you isue the following query:

SELECT * FROM DBA_QUEUE_TABLES WHERE OWNER = 'TEST';

You've said the query on DBA_OBJECTS returns an object with object name 'DEF$_AQCALL' under schema TEST. But of what type is this object? I suspect you have a queue (not queue table) defined under schema TEST, but the queue table for this queue is actualy in some other schema. So what you actually want to drop from schema TEST is QUEUE, not QUEUE TABLE. For this you should use DBMS_AQADM.DROP_QUEUE procedure.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

Hi .. again
As i have mentioned earlier DBA_OBJECTS table returns me a record >> containing the followinf information

OWNER : TEST
OBJECT_NAME : DEF$_AQCALL
OBJECT TYPE = TABLE.

i dont find any information about QUEUE in it . But when i issue
DROP TABLE DEF$_AQCALL
return me back with an eeor and to use
DBMS_AQADM.DROP_QUEUE_TABLE procedure to drop the table .. when i us this same procdure .. it returns me back .. that TEST.DEF$_AQCALL does not exist.

I m really stuck in it .. if i m trying to create a queue table in the test schema .. so that i can delete it afterwards .. return me back with the message that
object by the name DEF$_AQCALL already exist in the test scehema. isnt it confusing .. help me out ..

Beside i laso want to let you know that the queue table DEF$_AQCALL basiscally exists on SYSTEM schema. But some how due to wrong import of the databse, i m getting it in this TEST schema. Have to remove it.

Beside i laso want to let you know that the queue table DEF$_AQCALL basiscally exists on SYSTEM schema. But some how due to wrong import of the databse, i m getting it in this TEST schema. Have to remove it.

Thanks

RAFF

Don't u see from the results that its in SYSTEM schema and not in TEST schema....

Amar"There is a difference between knowing the path and walking the path."

Hi again!
Well ... i know its really getting irritated.
As i told u . that THE QUEUE table DEF$_AQCALL is in SYSTEM schema. If i remove it from there throgh the procedure. I get the +ve result.
But still when i drop the test user

DROP USER TEST cascade;
i get back the following error

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

And if i query the USER_OBJECTS in the test schema.
i get an object of type TABLE of the same name DEF$_AQCALL.