Oracle PL/SQL 6504 with BULK COLLECT

Question

I have this simple query:

SELECT MEASURE_ID, MEASURE_VALUE FROM MY_TABLE;

At the moment returning just a couple of records (in the future there will plenty of them):

8   265.7
7   559.6

A DESC on such table provides:

Name         Null     Type         
------------ -------- ------------ 
MEASURE_ID   NOT NULL NUMBER       
MEASURE_VALUE         NUMBER(10,1)

Then I defined the proper PL/SQL types:

CREATE OR REPLACE TYPE HASHMAP_NUM_TYPE_OBJ AS OBJECT (
    THE_ID                 NUMBER,
    THE_VALUE              NUMBER(10,1) 
);
CREATE OR REPLACE TYPE HASHMAP_NUM_TYPE IS TABLE OF HASHMAP_NUM_TYPE_OBJ;

And tried to fetch the records using a BULK COLLECT:

stats_by_measure HASHMAP_NUM_TYPE;
...
OPEN cursor_1 FOR
    SELECT MEASURE_ID, MEASURE_VALUE
    FROM MY_TABLE;
...
FETCH cursor_1 BULK COLLECT INTO stats_by_measure;
...
CLOSE cursor_1;

But I have the Oracle -6504 error. What am I doing wrong?

Remark: If I fetch the same cursor row by row, using a codeblock like this:

foo                    NUMBER;
faa                    NUMBER(10,1);
my_obj                 HASHMAP_NUM_TYPE_OBJ;
...
LOOP
  FETCH cursor_1 INTO foo, faa;
  my_obj := HASHMAP_NUM_TYPE_OBJ(foo,faa);
  EXIT WHEN cursor_1%NOTFOUND;
END LOOP;

everything works fine!

Why are you fetching the rows into an array in the first place? What will you do with them? I'm also wondering why you're using a ref cursor rather than an explicit (i.e. "normal") cursor or even an implicit cursor? — Boneist, Feb 16 '18 at 10:07
You're right, of course it's not necessary. It's just an architecture pattern we apply (in this case without a reason) in order to be able to switch from "pure PL/SQL paradigm" to the "Hibernate fetch cursor" paradigm (if requested). — serkelion, Feb 19 '18 at 09:21

score 3 · Answer 1 · answered Feb 16 '18 at 10:32

3

modify your cursor query like below so that it will have the same type

OPEN cursor_1 FOR
    SELECT HASHMAP_NUM_TYPE_OBJ(MEASURE_ID, MEASURE_VALUE)
      FROM MY_TABLE;

answered Feb 16 '18 at 10:32

eifla001

1,137
8
8

Thanks a lot. now I feel so stupid... I did this plenty of times in the past, but I found the error a little misleading and I spent hours investigating in the wrong direction. Thanks! – serkelion Feb 19 '18 at 09:19

Дмитрий · Answer 2 · 2018-02-16T11:17:29.963

I've solve your question

declare
type REC_TYPE is record (
            THE_ID  number,
            THE_VALUE   number
            );
type TB_TYPE is table of REC_TYPE index by binary_integer;
TBL TB_TYPE;

cursor CURSOR_1 is 
    select  a1.MEASURE_ID A$1, a1.MEASURE_VALUE A$2
     from MY_TABLE a1;
type REF_CUR_ is ref cursor return CURSOR_1%rowtype;
CURSOR_2    REF_CUR_;
begin
    open CURSOR_2 for 
         select  a1.MEASURE_ID A$1, a1.MEASURE_VALUE A$2
         from MY_TABLE a1;
    fetch CURSOR_2 bulk collect into TBL ;
    close CURSOR_2;
    return;
end;

It's works.

I have found another way without ref cursor there (look for FETCH Statement with BULK COLLECT Clause)

score 1 · Answer 3 · answered Feb 16 '18 at 10:51

You can only BULK COLLECT objects into a table of objects. In your case:

SQL> CREATE OR REPLACE TYPE hashmap_num_type_obj AS OBJECT (
  2      the_id                 NUMBER,
  3      the_value              NUMBER(10,1)
  4  );
  5  /

Type created

SQL> CREATE OR REPLACE TYPE hashmap_num_type IS TABLE OF hashmap_num_type_obj;
  2  /

Type created

SQL> DECLARE
  2     l_tab hashmap_num_type;
  3  BEGIN
  4     SELECT hashmap_num_type_obj(measure_id, measure_value)
  5       BULK COLLECT INTO l_tab
  6       FROM my_table;
  7  END;
  8  /

PL/SQL procedure successfully completed

BriteSponge · Answer 4 · 2018-02-16T10:53:52.950

You should retrieve the rows into a type based on a record type rather than an object type. The following works;

DECLARE
   TYPE hashmap_num_type_rt IS RECORD
       (THE_ID                 NUMBER,
        THE_VALUE              NUMBER(10,1) 
       );   
   TYPE hashmap_num_type_t IS TABLE OF hashmap_num_type_rt;

   stats_by_measure hashmap_num_type_t;

BEGIN
  SELECT measure_id, measure_value
  BULK COLLECT INTO stats_by_measure
  FROM my_table;

  FOR i IN 1..stats_by_measure.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('RECORD '||TO_CHAR(i)||' : ID - '||stats_by_measure(i).the_id||' MeasureVal: '||TO_CHAR(stats_by_measure(i).the_value));
  END LOOP;
END;

You could also define a cursor and the create a table type based on the type of the cursor (which is of course is still a row type rather than an object type).

If you want to use a cursor as the row type then try the following;

DECLARE
   CURSOR c_measures IS
   SELECT measure_id, measure_value
     FROM my_table;

   TYPE hashmap_num_type_t IS TABLE OF c_measures%ROWTYPE;

   stats_by_measure hashmap_num_type_t;

BEGIN
  OPEN c_measures;
  FETCH c_measures
  BULK COLLECT INTO stats_by_measure;
  CLOSE c_measures;

  FOR i IN 1..stats_by_measure.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('RECORD '||TO_CHAR(i)||' : ID - '||stats_by_measure(i).measure_id||' MeasureVal: '||TO_CHAR(stats_by_measure(i).measure_value));
  END LOOP;
END;

Oracle PL/SQL 6504 with BULK COLLECT

4 Answers4