Referencing Collection elements [message #656697] |
Fri, 14 October 2016 14:44 |
|
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 #656898 is a reply to message #656896] |
Fri, 21 October 2016 11:15 |
|
Michel Cadot
Messages: 68665 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
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.
|
|
|
|