Collections problems in Oracle 10g/XE

From: Sten <petrowi_at_hotmail.com>
Date: 21 Jun 2006 15:17:42 -0700
Message-ID: <1150928262.572383.216720@m73g2000cwd.googlegroups.com>

I'm not a DB pro, but the need arised to create a stored procedure for Oracle 10g/xe
After many failed attempts I'm looking for some help.

The scenario is this: I have a dictionary in the database that contains words and synonim sets. A synonim set contains links to a few words and links to other synonim sets. The goal is to find out if two given words are related over 3-4 "jumps" from a synonim set to other synonim sets that it is linked to.

The basic step for the algorithm is to take a list of IDs of synonim sets (SIDs) and produce a list of SIDs that contains the original list plus all the SIDs linked from the original list.

The error I get:
  ORA-06502: PL/SQL: numeric or value error: host bind array too small   ORA-06512: at line 8
at line 8 there is nothing - it's empty in my code.

Here is a part of the code, when I don't invoke it - there is no error message, but there is no attempts to broaden the search to include linked synonim sets:
...
TYPE LinkTable is TABLE OF NUMBER; --WordLexes.F_SID%TYPE;

....
-- this procedure only prints the table to the DBMS console

    PROCEDURE PrintTab(ltab IN OUT NOCOPY LinkTable) IS     BEGIN
      NULL;
    END;

• this is the basic step PROCEDURE ExpandLevel(ltab IN OUT NOCOPY LinkTable) IS tmpt LinkTable; tmpbuf LinkTable ; last Number; BEGIN

            IF (ltab IS NOT NULL AND ltab.COUNT>0) THEN
                dbms_output.put_line('EXPAND LinkTable');
                dbms_output.put('  EXPAND From ');
                PrintTab(ltab);

                last := ltab.LAST;
                FOR li IN ltab.FIRST .. last
                LOOP
                    OPEN WordLinks(ltab(li));
                    FETCH WordLinks BULK COLLECT INTO tmpbuf;
                    CLOSE WordLinks;

                    dbms_output.put('  appending: ');
                    PrintTab(tmpbuf);

                    --IF (tmpt IS NULL) THEN
                    --    tmpt:=tmpbuf;
                    --ELSE
                        --tmpt:= SET(tmpt) MULTISET UNION DISTINCT
SET(tmpbuf);
                    --END IF;
                    -- neither the version commented above, nor the one
below works
                    FOR ti IN tmpbuf.FIRST .. tmpbuf.LAST
                    LOOP
                        ltab.EXTEND;
                        ltab(ltab.LAST) := tmpbuf(ti);
                    END LOOP;

                END LOOP;
                dbms_output.put('  EXPANDED TO ');
            --    ltab := SET(ltab);
                PrintTab(ltab);
                dbms_output.put_line(' ');
            ELSE
                dbms_output.put_line('Can not expand empty sets');
            END IF;

        NULL;

-- The exception handler below doesn't prevent the error from
happening

    EXCEPTION

        WHEN OTHERS THEN
            dbms_output.put_line('err');

    END; Received on Wed Jun 21 2006 - 17:17:42 CDT