CODEWITHSUNDEEP
oracle-databaseplsql
Kapil Sharma
Kapil Sharma

Reputation: 75

DROP Oracle queue

I am trying to drop the queue using following command: 

 EXECUTE DBMS_AQADM.DROP_QUEUE (queue_name => 'ORDVAL_QUEUE');

It is working fine but i want to put a check if queue does not exist and i execute above command thn it should not throw an error or exception. I m using this but din't got my target :

 BEGIN
    EXECUTE IMMEDIATE 'DBMS_AQADM.DROP_QUEUE (queue_name => "ORDVAL_QUEUE")';
  EXCEPTION
 WHEN OTHERS THEN
  null;
  END;

It executes fine but does not drop queue.

Upvotes: 0

Views: 3643

Answers (1)

Jon Heller
Jon Heller

Reputation: 36912

I haven't used queues before, but this should help:

  1. Add a BEGIN and END to the execute immediate, to use dynamic PL/SQL instead of dynamic SQL.
  2. Use two single quotes instead of a double quote.
  3. Catch specific exceptions when possible, avoid OTHERS. This was catching and ignoring unexpected exceptions.
  4. Not shown below, but perhaps it would be better to check for the existence of the queue in ALL_QUEUES instead?
    DECLARE
        QUEUE_DOES_NOT_EXIST EXCEPTION;
        PRAGMA EXCEPTION_INIT(QUEUE_DOES_NOT_EXIST, -24010);
    BEGIN
        EXECUTE IMMEDIATE
            'BEGIN DBMS_AQADM.DROP_QUEUE (queue_name => ''ORDVAL_QUEUE''); END;';
        EXCEPTION WHEN queue_does_not_exist then
            dbms_output.put_line('Test to see if exception was thrown');
    END;
    /

Upvotes: 1

Related Questions