I have a question ...
If I have a table with the following columns:
COD_ARTICLE || NAME_ARTICLE || COD_PUBLICATION
and if, at a PL/SQL Package, I have the need to bring the results of a query (wich returns 3 or more rows), in a PL/SQL Table, how can I make this ??
The following code, gives me an error, while on compiling:
TYPE table_aux IS TABLE OF tabela%rowtype INDEX BY BINARY_INTEGER;
CURSOR my_cursor IS SELECT COD_ARTICLE || NAME_ARTICLE || COD_PUBLICATION FROM TABELA WHERE COD_PUBLICATION = I_COD_PUBLICATION;
FETCH my_cursor INTO tab_aux;
PLS-00597: expression 'TAB_AUX' in the INTO list is of wrong type
The || in your select means you concatenate all three text fields into a single one, and then you try to say it is a 3 columns row... wonder why it doesn't work ? I think that if you just replace || with , in your select, it should work a little better :
CURSOR my_cursor IS SELECT COD_ARTICLE, NAME_ARTICLE, COD_PUBLICATION FROM TABELA WHERE COD_PUBLICATION = I_COD_PUBLICATION;
FETCH my_cursor BULK COLLECT INTO tab_aux;
This loads all the result of your query into your PL/SQL table without fetching row by row. It's much more efficient, but can use a lot of RAM on the server if there are many rows. To limit the number of rows you fetch at a time (here 100) :
FETCH my_cursor BULK COLLECT INTO tab_aux LIMIT 100;
FOR i IN 1..tab_aux.count LOOP
-- Process tab_aux(i)