SQL Snippets ™: Tutorials for Oracle Developers

create table t ( val varchar2(5) );

insert into t values ( 'A' );
insert into t values ( 'B' );
insert into t values ( 'C' );
insert into t values ( 'D' );
insert into t values ( 'E' );
insert into t values ( 'F' );
insert into t values ( 'G' );

commit;

create package p as

  type aa_type is table of varchar2(5) index by binary_integer ; -- Associative Array

  type nt_type is table of varchar2(5) ;                         -- Nested Table

  type va_type is varray(10) of varchar2(5) ;                    -- Varray

  procedure print( p1 in varchar2   ) ;
  procedure print( p1 in aa_type ) ;
  procedure print( p1 in nt_type ) ;
  procedure print( p1 in va_type ) ;

  function nvl3( p1 in pls_integer ) return varchar2  ;
  function tf( p1 in boolean ) return varchar2        ;


end;
/
show errors

create package body p as

  procedure print( p1 varchar2 ) is begin dbms_output.put_line(p1); end;

  procedure print( p1 in aa_type ) is
    i binary_integer ;
  begin
    if p1 is null then dbms_output.put_line( 'NULL' ); return; end if;
    if p1.count = 0 then dbms_output.put_line( 'NOT NULL and empty' ); end if;
    i := p1.first;
    while i is not null
    loop
      dbms_output.put_line( '(' || i || ') ' || p1(i) );
      i := p1.next(i) ;
    end loop;
    print( '.first = ' || nvl3(p1.first) );
    print( '.last  = ' || nvl3(p1.last)  );
    print( '.count = ' || nvl3(p1.count) );
    print( '.limit = ' || nvl3(p1.limit) );
  end;

  procedure print( p1 in nt_type ) is
    i binary_integer ;
  begin
    if p1 is null then dbms_output.put_line( 'NULL' ); return; end if;
    if p1.count = 0 then dbms_output.put_line( 'NOT NULL and empty' ); end if;
    i := p1.first;
    while i is not null
    loop
      dbms_output.put_line( '(' || i || ') ' || p1(i) );
      i := p1.next(i) ;
    end loop;
    print( '.first = ' || nvl3(p1.first) );
    print( '.last  = ' || nvl3(p1.last)  );
    print( '.count = ' || nvl3(p1.count) );
    print( '.limit = ' || nvl3(p1.limit) );
  end;

  procedure print( p1 in va_type ) is 
  begin
    if p1 is null then dbms_output.put_line( 'NULL' ); return; end if;
    if p1.count = 0 then dbms_output.put_line( 'NOT NULL and empty' ); end if;
    for i in nvl(p1.first,0) .. nvl(p1.last,-1) loop
      dbms_output.put_line( '(' || i || ') ' || p1(i) );
    end loop;
    print( '.first = ' || nvl3(p1.first) );
    print( '.last  = ' || nvl3(p1.last)  );
    print( '.count = ' || nvl3(p1.count) );
    print( '.limit = ' || nvl3(p1.limit) );
  end;


  function nvl3( p1 in pls_integer ) return varchar2 is
  begin return( nvl( to_char(p1), 'NULL' ) ); end;

  function tf( p1 in boolean ) return varchar2 is
  begin if p1 then return('TRUE'); else return('FALSE'); end if; end;

end;
/
show errors