CODEWITHSUNDEEP
oracle-databaseplsql
Rotem87
Rotem87

Reputation: 105

uninitialized collection PL/SQL

I have the following types:

TYPE station_record_type IS OBJECT
    (
       city_name NVARCHAR2 (128),
        station_name NVARCHAR2 (128),
    );

    TYPE stations_table_type IS TABLE OF station_record_type;

I have a procdure that received stations_table_type .

 PROCEDURE retrieve_stations (
        o_stations_to_retrieve      OUT stations_table_type )

I tried the following -

o_stations_to_retrieve.EXTEND;

But I received the error Reference to uninitialized collection . I tried to initialize the collection in the call itself -

DECLARE
    o_stations_to_retrieve   stations_table_type := stations_table_type();
BEGIN
    retrieve_stations_flights (
        o_stations_to_retrieve   => o_stations_to_retrieve,
      );

But I keep getting this error. How can I fix it?

Upvotes: 0

Views: 460

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Your procedure has an OUT parameter, so initialising it in the caller has no effect. You need to initialise it inside your procedure:

PROCEDURE retrieve_stations (
        o_stations_to_retrieve      OUT stations_table_type ) is
BEGIN
    o_stations_to_retrieve := stations_table_type();
    o_stations_to_retrieve.EXTEND;
...

You then don't need to initialise it in your anonymous block at all.

SQL Fiddle just showing the procedure compiles and the anonymous block runs without raising an exception.

Alternatively, you could keep the initialisation in the anonymous block, but make it an IN OUT parameter:

PROCEDURE retrieve_stations (
        o_stations_to_retrieve      IN OUT stations_table_type ) is
BEGIN
    o_stations_to_retrieve.EXTEND;
...

SQL Fiddle showing this compiles and runs too.

Upvotes: 2

Related Questions