How to use dbms_output to display an array of values assigned to a variable?

Question

I have something like this, but got an error says ORA-06533: Subscript beyond count. I want to see all the values from the "select distinct" statement in the output tab. Anyone can help? thanks!

DECLARE
   TYPE v_chks_array IS VARRAY (10) OF VARCHAR2 (50);

   arrSRCs   v_chks_array;
BEGIN
   arrSRCs := v_chks_array ();
   arrSRCs.EXTEND (10);

   SELECT /*+parallel (a,4)*/
         DISTINCT a.src_table
     BULK COLLECT INTO arrSRCs
     FROM hcr_dm.hcr_dm_fact a;

   DBMS_OUTPUT.put_line (arrSRCs (10));
END;

Have you verified that there are no more than 10 rows in HCR_DM.HCR_DM_FACT? — Bob Jarvis - Слава Україні, Oct 29 '14 at 18:37
the table has millions of rows. the "select" result can have 0 to 10 records, and there's a potential that there could be more. — gcbm1984, Oct 29 '14 at 20:51

score 1 · Accepted Answer · answered Oct 30 '14 at 01:57

1

Collections are not needed here:

begin
    for results in
    (
        select /*+ parallel (a,4) */ distinct a.src_table
        from hcr_dm.hcr_dm_fact a;
    ) loop
        dbms_output.put_line(results.src_table);
    end loop;
end;
/

answered Oct 30 '14 at 01:57

Jon Heller

34,999
6
74
132

score 0 · Answer 2 · answered Oct 29 '14 at 21:37

Instead of VARRAY, you may try a TABLE type collection. You'll have more flexibility with number of records to hold while doing BULK COLLECT.

Create or replace type v_chks_array IS TABLE OF VARCHAR2 (500);

More information can be found at http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm

How to use dbms_output to display an array of values assigned to a variable?

2 Answers2