Home » SQL & PL/SQL » SQL & PL/SQL » Referencing Collection elements (11g)
Referencing Collection elements [message #656697] Fri, 14 October 2016 14:44 Go to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
I found this script online to learn about BULK_COLLECT using a one-dimensional array.
Since the column is referenced correctly in the DBMS statement per the SQL statement, I am not sure why the error is occurring. (?)

When attempting to compile this script, this line errors out with a PLS-00302 error:
" 'TABLE_NAME' must be declared."

Line in error is the 2nd DBMS statement in the FOR-LOOP area:
dbms_output.put_line('indx, '||L_DBA_TABLES.TABLE_NAME);

Per the script:
"DBA_TABLES_CUR" contains the column "TABLE_NAME" per the SQL statement:
"SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES;"

"DBA_TABLES_ATT" contains the contents of "DBA_TABLES_CUR" % rowtype
"L_DBA_TABLES" contains "DBA_TABLES_AAT";


CREATE OR REPLACE PROCEDURE ITATP.PROCESS_ALL_ROWS (LIMIT_IN IN PLS_INTEGER DEFAULT 10)
-- to run it:   EXEC  PROCESS_ALL_ROWS
IS
    CURSOR DBA_TABLES_CUR 
    IS 
        SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES;
        
    TYPE DBA_TABLES_AAT IS TABLE OF DBA_TABLES_CUR%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    L_DBA_TABLES   DBA_TABLES_AAT;
BEGIN   
    OPEN DBA_TABLES_CUR;
    LOOP
        FETCH DBA_TABLES_CUR BULK COLLECT INTO L_DBA_TABLES LIMIT LIMIT_IN;

        FOR INDX IN 1 .. L_DBA_TABLES.COUNT 
        LOOP
           dbms_output.put_line
                    ('indx, '
                     ||L_DBA_TABLES.count
                     ||'   DBA_Tables.count is : '
                     ||INDX 
                     ||'---'||L_DBA_TABLES.count
                     );
           dbms_output.put_line('indx, '||L_DBA_TABLES.TABLE_NAME);
        END LOOP;

       EXIT WHEN L_DBA_TABLES.COUNT < LIMIT_IN;
   END LOOP;

   CLOSE DBA_TABLES_CUR;
END;    --process_all_rows
Re: Referencing Collection elements [message #656698 is a reply to message #656697] Fri, 14 October 2016 15:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
change:

dbms_output.put_line('indx, '||L_DBA_TABLES.TABLE_NAME);

to:

dbms_output.put_line('indx, '||L_DBA_TABLES(indx).TABLE_NAME);
Re: Referencing Collection elements [message #656810 is a reply to message #656698] Wed, 19 October 2016 14:25 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
Wonderful Barbara thank you! Smile
Re: Referencing Collection elements [message #656822 is a reply to message #656810] Thu, 20 October 2016 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Er, didn't you correctly write this in your previous topic? Smile
http://www.orafaq.com/forum/mv/msg/202103/656351/#msg_656351

Quote:
DBMS_OUTPUT.PUT_LINE('Col-Counter: '||I_||' Column-Name: ~' ||DESC_TAB_(I_).COL_NAME||' ~ ');
/forum/fa/5767/0/

Re: Referencing Collection elements [message #656896 is a reply to message #656822] Fri, 21 October 2016 09:56 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
Yes, but had forgotten about it....sorry.
Re: Referencing Collection elements [message #656898 is a reply to message #656896] Fri, 21 October 2016 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As a French poet, Nicolas Boileau-Despréaux, said:

Quote:
Hasten slowly, and without losing heart,
Put your work twenty times upon the anvil,
Polish it and polish it up again
Smile

Original version:
Hâtez-vous lentement, et, sans perdre courage,
Vingt fois sur le métier remettez votre ouvrage :
Polissez-le sans cesse et le repolissez ;
Ajoutez quelquefois, et souvent effacez.


Re: Referencing Collection elements [message #656995 is a reply to message #656898] Tue, 25 October 2016 06:45 Go to previous message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
Good point Michel, thanks! Smile
Previous Topic: worklist sql
Next Topic: transaction id sql
Goto Forum:
  


Current Time: Thu Mar 28 16:24:19 CDT 2024