selecting from oracle nested table

The task I have is to output some messages from a procedure in a package.
The package is run in a shell script from plsql by piping the output from
the proc to a file. There is a need to have 4 different types of messages in
the report with some sorting. I want to output to one file only.

A possible solution is to accumulate the messages in 4 collections - I'm
starting with nested tables of varchar2- and then dbms_output.putline() the
messages at the end of the proc, in correct sorted order. I'm not familiar
with all the nuances of using nested tables and have run into a couple of
snags.

This is what I have so far. The commented-out code in add_message() and
main_line() does not compile, but Toad is not giving me any useful
information as to why. Any suggestions would be appreciated.



CREATE OR REPLACE PACKAGE collections_test
AS
PROCEDURE main_line;
PROCEDURE init_message_tables;
END collections_test;


CREATE OR REPLACE PACKAGE BODY collections_test

IS

TYPE messages_table IS TABLE OF VARCHAR2 (100);
messages1 messages_table;
messages2 messages_table;

PROCEDURE init_message_tables
IS
BEGIN
messages1 := messages_table ();
messages2 := messages_table ();
END init_message_tables;


Procedure add_message (m_table messages_table, message varchar2)
IS
v_count number;
BEGIN
-- m_table.extend;
-- v_count:= m_table.COUNT;
-- m_table(v_count + 1) := message;
null;
END add_message;

PROCEDURE main_line
IS
-- CURSOR messages
-- IS
-- SELECT *
-- FROM TABLE (messages1);

BEGIN
init_message_tables();
add_message(messages1, 'blue');
add_message(messages2, 'green');
END main_line;

END collections_test;

On Nov 2, 5:38 am, "Tim B" wrote:
> The task I have is to output some messages from a procedure in a package.
> The package is run in a shell script from plsql by piping the output from
> the proc to a file. There is a need to have 4 different types of messages in
> the report with some sorting. I want to output to one file only.
>
> A possible solution is to accumulate the messages in 4 collections - I'm
> starting with nested tables of varchar2- and then dbms_output.putline() the
> messages at the end of the proc, in correct sorted order. I'm not familiar
> with all the nuances of using nested tables and have run into a couple of
> snags.
>
> This is what I have so far. The commented-out code in add_message() and
> main_line() does not compile, but Toad is not giving me any useful
> information as to why. Any suggestions would be appreciated.
>
> CREATE OR REPLACE PACKAGE collections_test
> AS
> PROCEDURE main_line;
> PROCEDURE init_message_tables;
> END collections_test;
>
> CREATE OR REPLACE PACKAGE BODY collections_test
>
> IS
>
> TYPE messages_table IS TABLE OF VARCHAR2 (100);
> messages1 messages_table;
> messages2 messages_table;
>
> PROCEDURE init_message_tables
> IS
> BEGIN
> messages1 := messages_table ();
> messages2 := messages_table ();
> END init_message_tables;
>
> Procedure add_message (m_table messages_table, message varchar2)
> IS
> v_count number;
> BEGIN
> -- m_table.extend;
> -- v_count:= m_table.COUNT;
> -- m_table(v_count + 1) := message;
> null;
> END add_message;
>
> PROCEDURE main_line
> IS
> -- CURSOR messages
> -- IS
> -- SELECT *
> -- FROM TABLE (messages1);
>
> BEGIN
> init_message_tables();
> add_message(messages1, 'blue');
> add_message(messages2, 'green');
> END main_line;
>
> END collections_test;

So don't use TOAD

Presumably the error is something like

PLS-00363: expression 'M_TABLE' cannot be used as an assignment target

because m_table is passed as an IN parameter, when it should be IN OUT
NOCOPY.