How to Manually Cleanup Oracle Advanced Queuing Tables

When the queue table is locked and not able to proceed, then the simplest way to rectify is to drop the queue table forcefully and create again using below sql.

BEGIN

 DBMS_AQADM.DROP_QUEUE_TABLE (

   queue_table         => 'queue_table',

   force               => TRUE,   auto_commit         => TRUE

 );

End;

/

Typical scenarios when a queue table/queue cannot be dropped

i) Cannot create or drop queue table DBMS_AQADM.CREATE_QUEUE_TABLE results in an

ORA-24001 cannot create QUEUE_TABLE, string already exists

Cause: The queue table already exists in the queueing system.

Action: Drop the table first using the DROP_QUEUE_TABLE() command or specify another table.

while DBMS_AQADM.DROP_QUEUE_TABLE results in an

ORA-24002 QUEUE_TABLE string does not exist

Cause: QUEUE_TABLE does not exist.

Action: Query on the user view USER_QUEUE_TABLES to find out existing queue tables.

ii) Cannot create or drop queue DBMS_AQADM.CREATE_QUEUE results in an

ORA-24006 cannot create QUEUE, string already exists

Cause: The queue requested to be created already exists.

Action: Specify another queue name. Query USER_QUEUES for all the

existing queues in the users's schema.

while executing DBMS_AQADM.DROP_QUEUE results in an

ORA-24010 QUEUE string does not exist

Cause: The specified queue does not exist.

Action: Specify a valid queue. Query USER_QUEUES for all the valid queues.

Procedure for uploading file to blob field in oracle database

Sample Table
CREATE TABLE SV_EMP_PHOTO
(
ID NUMBER(3) NOT NULL,
PHOTO_NAME VARCHAR2(40),
PHOTO_RAW BLOB,
EMP_NAME VARCHAR2(80)
)
Create a directory where the photos will be stored.
Create directory SV_PHOTO_DIR as 'E:\photo'
Procedure to read file and save to database
CREATE OR REPLACE PROCEDURE sv_load_image
(
p_id NUMBER ,
p_emp_name IN VARCHAR2 ,
p_photo_name IN VARCHAR2
) IS
l_source BFILE;
l_dest BLOB;
l_length BINARY_INTEGER;
BEGIN
l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);
INSERT INTO sv_emp_photo(ID, photo_name, emp_name, photo_raw)
VALUES(p_id,p_photo_name,p_emp_name,EMPTY_BLOB ())
RETURNING photo_raw INTO l_dest;
-- lock record
SELECT photo_raw INTO l_dest FROM sv_emp_photo
WHERE ID = p_id AND photo_name = p_photo_name FOR
UPDATE;
-- open the file
DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
-- get length
l_length := DBMS_LOB.getlength (l_source);
-- read the file and store in the destination
DBMS_LOB.loadfromfile (l_dest, l_source, l_length);
-- update the blob field with destination
UPDATE sv_emp_photo SET photo_raw = l_dest WHERE ID = p_id
AND photo_name = p_photo_name;
-- close file
DBMS_LOB.fileclose (l_source);
END;
GRANT ALL ON DIRECTORY SV_PHOTO_DIR TO PUBLIC