Re: [HELP] Use of VARRAY in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 24 Nov 1999 08:32:11 -0500
Message-ID: <42qn3scvrgsem4u7q6pm7ehi6qdtopl2qj_at_4ax.com>

A copy of this was sent to "Guillaume" <gcrest_at_universalflower.com> (if that email address didn't require changing) On Wed, 24 Nov 1999 11:55:56 GMT, you wrote:

> Hi to all,
>
> I'm writing a PL/SQL procedure for the very first time...
> I'm trying to exctract all the values of a table with some simple
>criteria, within a varray or a record in order to insert these values into
>another field (with another primary key obviously).
> I'm having problems when I try to get these values in the select
>command, when I compile I have a type mismatch error...
>
> Here is the latest snippet I've tried:
>
>TYPE REC IS RECORD(
> codeCat NUMBER(3),
> codePart NUMBER(3),
> codeProd NUMBER(3),
> codeTitre NUMBER(3),
> codeCadeau NUMBER(3),
> codeInfo NUMBER(3),
> ordre NUMBER(3)
>);
>TYPE TAB IS VARRAY(200) OF REC;
>tableau TAB;
>
>BEGIN
> select * into tableau from Catalogue_Produit where code_partenaire =
>code_Part and code_Catalogue = code_Cat;
>
>END;
>
> Thank you very much,
>
> Regards,
>
> Guillaume
>
>

You don't mention a version.

In Oracle7.x and up, it could be:

declare

    cursor c1 is select * from .....;
    tableau tab;
begin

    open c1;
    loop

       fetch c1 into tableau(tableau.count+1);
       exit when c1%notfound;

    end loop;
    close c1;
end;

In Oracle8.0 and up, it could resemble:

scott_at_8.0> create or replace type empType as object

  2  (   empno       number,
  3      ename       varchar2(10),
  4      job         varchar2(9),
  5      hiredate    date,
  6      sal         number,
  7      comm        number

  8 );
  9 /

Type created.

scott_at_8.0>
scott_at_8.0> create or replace type empArrayType as table of empType;   2 /

Type created.

scott_at_8.0> declare

  2      x   empArrayType;
  3      i   number;
  4  begin
  5      select CAST( multiset( select empno, ename, job, hiredate, sal, comm
  6                               from emp  ) AS empArrayType )
  7        INTO x
  8        from dual;
  9  
  9      dbms_output.put_line( 'There were ' || x.COUNT || ' rows fetched' );
 10      i := x.FIRST;
 11      while ( i is not null ) loop
 12          dbms_output.put_line( x(i).ename || ' ' || x(i).hiredate );
 13          i := x.NEXT(i);
 14      end loop;
 15  
 15      x.DELETE( x.FIRST );
 16      dbms_output.put_line( 'Now there are ' || x.COUNT || ' rows fetched' );

 17 end;
 18 /
There were 14 rows fetched
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81
MARTIN 28-SEP-81

BLAKE 01-MAY-81
CLARK 09-JUN-81
SCOTT 09-DEC-82

KING 17-NOV-81
TURNER 08-SEP-81
ADAMS 12-JAN-83
JAMES 03-DEC-81
FORD 03-DEC-81
MILLER 23-JAN-82
Now there are 13 rows fetched

PL/SQL procedure successfully completed.

(NOTE: must be a table type -- must be a SQL table type, defined at the sql level, not plsql level).   

-- 
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 Wed Nov 24 1999 - 14:32:11 CET