Re: varray and nested table questions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 30 Nov 1999 09:01:39 -0500
Message-ID: <13m74s0dn1ffqv2mqnnaiuinmr8km999r2@4ax.com>

A copy of this was sent to "Alan Sze" <alan_at_icon.com.hk> (if that email address didn't require changing) On Tue, 30 Nov 1999 18:03:35 +0800, you wrote:

>
>Hope this is not a stupid question.
>I've got
> ORA-06531: Reference to uninitialized collection
>from the executing the following package.
>
>Is that the nested table (or varray) need initialization?? If so, how??
>
>And also, I wanna sort the arrays (array_char / array_num) descending.
>Any good sorting algorithm??
>
>Thanks.
>
>-- Alan
>
>
>
>The package as follows:
>
>
>CREATE OR REPLACE PACKAGE BODY packageA
>AS
>TYPE CharArrayType IS TABLE OF varchar2(10);
>TYPE NumArrayType IS TABLE OF number;
>PROCEDURE procA (
> p_a IN number DEFAULT 0,
> p_b IN varchar2 DEFAULT 'NONE'
> )
>IS
> array_char CharArrayType;
> array_num NumArrayType;
> v_i binary_integer;
> CURSOR cur_a IS
> SELECT ... FROM ... WHERE ...;
>BEGIN
> v_i := 1;
> LOOP
> FETCH cur_a INTO ...;
> EXIT WHEN cur_a%NOTFOUND;
> array_num(v_i) := value_a;
> array_char(v_i) := name_a;
> v_i := v_i + 1;
> END LOOP;
> ....
> ....
>END procA;
>
>END packageA;
>
>
>

It might look like this:

tkyte_at_8i> create or replace package packageA   2 as
  3 procedure procA( p_a in number default 0, p_b in varchar2 default 'NONE' );
  4 end;
  5 /

Package created.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> CREATE OR REPLACE PACKAGE BODY packageA

  2 AS
  3 TYPE CharArrayType IS TABLE OF varchar2(10);   4 TYPE NumArrayType IS TABLE OF number;   5
  6
  7 PROCEDURE procA (

  8       p_a IN number DEFAULT 0,
  9       p_b IN varchar2 DEFAULT 'NONE'

 10 )
 11 IS

 12      array_char    CharArrayType := CharArrayType();
 13      array_num     NumArrayType  := NumArrayType();
 14  
 15      CURSOR cur_a IS
 16          SELECT user_id value_a, username name_a FROM all_users WHERE rownum

< 10;
 17 BEGIN

 18      for x in cur_a loop
 19          array_char.extend;
 20          array_num.extend;
 21          array_char(array_char.count) := x.name_a;
 22          array_num(array_num.count) := x.value_a;
 23      end loop;

 24 END procA;
 25
 26 END packageA;
 27 /

Package body created.

tkyte_at_8i>
tkyte_at_8i> exec packagea.proca

PL/SQL procedure successfully completed.

that is how to implement using the method you chose above. I would go one step further and create the type OUTSIDE of the plsql package so I could use SQL on it. That way I can fetch and popuate a table of some type easily in one query (no loops) and I can use SQL to sort it. for example:

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> REM instead of putting a type in a spec, do this:

tkyte_at_8i> create or replace type myScalarType as object   2 ( char_value varchar2(10),
  3 num_value number
  4 )
  5 /

Type created.

tkyte_at_8i> create or replace type myTableType as table of myScalarType;   2 /

Type created.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> create or replace package body PackageA

  2 as
  3
  4 PROCEDURE procA (

  5       p_a IN number DEFAULT 0,
  6       p_b IN varchar2 DEFAULT 'NONE'

  7 )
  8 IS
  9 l_data myTableType;
 10
 11 BEGIN
 12

 13      select cast( multiset( SELECT username, user_id
 14                               FROM all_users
 15                              WHERE rownum < 10 ) as myTableType )
 16        into l_data
 17        from dual;
 18  
 19      dbms_output.put_line( 'Sorted by USERNAME' );
 20      for x in ( select *
 21                   from THE ( select cast( l_data as mytableType )
 22                                from dual ) a
 23                  order by char_value )
 24      loop
 25          dbms_output.put_line( x.char_value || ' ' || x.num_value );
 26      end loop;
 27  
 28      dbms_output.put_line( chr(9) );
 29      dbms_output.put_line( chr(9) );
 30      dbms_output.put_line( 'Sorted by USERID' );
 31      for x in ( select *
 32                   from THE ( select cast( l_data as mytableType )
 33                                from dual ) a
 34                  order by num_value )
 35      loop
 36          dbms_output.put_line( x.char_value || ' ' || x.num_value );
 37      end loop;

 38
 39 END procA;
 40
 41
 42 end;
 43 /

Package body created.

tkyte_at_8i> exec packagea.proca
Sorted by USERNAME
CTXSYS 21
DBSNMP 18
OEM 35
ORDPLUGINS 24
ORDSYS 23
OUTLN 11
SYS 0
SYSTEM 5
TRACESVR 20

Sorted by USERID
SYS 0
SYSTEM 5
OUTLN 11
DBSNMP 18
TRACESVR 20
CTXSYS 21
ORDSYS 23
ORDPLUGINS 24
OEM 35 PL/SQL procedure successfully completed.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 30 1999 - 08:01:39 CST