Re: selecting from oracle nested table

On Nov 2, 5:38 am, "Tim B" <nos..._at_someisp.ca> 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. Received on Fri Nov 02 2007 - 02:21:56 CDT