selecting from oracle nested table

From: Tim B <nospam_at_someisp.ca>
Date: Fri, 02 Nov 2007 05:38:00 GMT
Message-ID: <YIyWi.168650$th2.154322@pd7urf3no>

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

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; Received on Fri Nov 02 2007 - 00:38:00 CDT