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" <nos... (AT) someisp (DOT) ca> wrote:
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.