jalal rasooly
jalal rasooly

Reputation: 705

how to daclare queue data-structure in oracle?

is There Any Way to Declare a Queue Data Structure Which be Accessible Globally in Oracle or in a Specific Schema?
With Globally i Mean be Accessible in All Procedures of Database or Specific Schema enter image description here


edited :
i find out i can make FIFO QUEUE by package. so i wrote a code but i get error. any one know why i get this error?
package spec:

CREATE OR REPLACE PACKAGE HR.TEST_PACKAGE IS
  PROCEDURE ADDQ (TABLE_NAME VARCHAR2);
  FUNCTION DELQ RETURN VARCHAR2;
END TEST_PACKAGE;
/

package body:

CREATE OR REPLACE PACKAGE BODY HR.TEST_PACKAGE IS
  REAR NUMBER := 0;
  FRONT NUMBER := 0;
  TYPE QUEUE_TYPE IS VARRAY(10) OF VARCHAR2(15);
  QUEUE_ARRAY QUEUE_TYPE;
PROCEDURE ADDQ (TABLE_NAME VARCHAR2) IS
BEGIN
  REAR := (REAR+1) MOD 10;
  IF (REAR = 0) THEN
    REAR := 10;
  END IF;
  QUEUE_ARRAY(REAR) := TABLE_NAME;
END ADDQ;
FUNCTION DELQ RETURN VARCHAR2 IS
BEGIN
  IF (FRONT = REAR) THEN
    RETURN 'EMPTY';
  ELSE
    FRONT := (FRONT + 1) MOD 10;
    IF (FRONT = 0) THEN
      front := 10;
    END IF;
    RETURN QUEUE_ARRAY(FRONT);
  END IF;
END DELQ;
END TEST_PACKAGE;
/

when i run this example code:

create or replace procedure hr.test is
begin
  hr.test_package.addq('a');
  hr.test_package.addq('b');
  dbms_output.put_line(hr.test_package.delq);
end test;

i get this error:

ORA-06531: Reference to uninitialized collection
06531. 00000 -  "Reference to uninitialized collection"
*Cause:    An element or member function of a nested table or varray
           was referenced (where an initialized collection is needed)
           without the collection having been initialized.
*Action:   Initialize the collection with an appropriate constructor
           or whole-object assignment.

and this error occur when wants to run this part of a code:

QUEUE_ARRAY(REAR) := TABLE_NAME;  

Upvotes: 2

Views: 360

Answers (1)

jalal rasooly
jalal rasooly

Reputation: 705

Problem Solved. following code will declare a FIFO QUEUE in oracle.
the addq procedure will insert in queue and delq function will delete and return a value from queue.
package spec:

CREATE OR REPLACE PACKAGE HR.TEST_PACKAGE IS
  PROCEDURE ADDQ (TABLE_NAME VARCHAR2);
  FUNCTION DELQ RETURN VARCHAR2;
END TEST_PACKAGE;
/

package body:

CREATE OR REPLACE PACKAGE BODY HR.TEST_PACKAGE IS
  REAR NUMBER := 0;
  FRONT NUMBER := 0;
  TYPE QUEUE_TYPE IS VARRAY(10) OF VARCHAR2(15);
  QUEUE_ARRAY QUEUE_TYPE := QUEUE_TYPE() ;
PROCEDURE ADDQ (TABLE_NAME VARCHAR2) IS
BEGIN
  REAR := (REAR+1) MOD 10;
  IF (REAR = 0) THEN
    REAR := 10;
  END IF;
  QUEUE_ARRAY.EXTEND;
  QUEUE_ARRAY(REAR) := TABLE_NAME;
END ADDQ;
FUNCTION DELQ RETURN VARCHAR2 IS
BEGIN
  IF (FRONT = REAR) THEN
    RETURN 'EMPTY';
  ELSE
    FRONT := (FRONT + 1) MOD 10;
    IF (FRONT = 0) THEN
      front := 10;
    END IF;
    RETURN QUEUE_ARRAY(FRONT);
  END IF;
END DELQ;
END TEST_PACKAGE;
/

Upvotes: 1

Related Questions