Re: PL/SQL Array generation

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 03 Feb 2000 08:38:03 -0500
Message-ID: <bq0j9skjbgpggss9hspjut1req2dgueo3i@4ax.com>

A copy of this was sent to "Pierre Oub" <POub123_at_hotmail.com> (if that email address didn't require changing) On Wed, 2 Feb 2000 17:03:40 -0500, you wrote:

>Hi everybody
>
>I would like to copy "somewhere" an image of record i want to update later.
>I use this only for 3 or 4 records each time (but very often).
>-1- So i would like to create a VARRAY of record to store the selected
>record,
>-2- Do my update.
>-3- Then i need an answer from an external procedure (it takes 5 secondes)
>-4- If it's a bad answer, i would like to copy the update back the records.
>
>I can use a classic rollback, but i don't want to lock the table for such a
>long time (5 secondes).
>
>I try to create my ARRAY, but i have a invalide error.
>
>Can anyone could give me a direct to fix it ?
>
>Thanks
>
>My script
>
>CREATE OR REPLACE
>PROCEDURE my_proc PROCEDURE (modify_by IN VARCHAR2 DEFAULT NULL) IS
>TYPE t_emp IS VARRAY(10) OF my_database.emp%ROWTYPE;
>v_emp t_emp := NULL;
>BEGIN
> SELECT *
> INTO v_emp
> FROM my_database.emp
>WHERE id = '99999';
>END my_proc;
>/
>
>
>When i generate it ia have this error, but i check and i can see my EMP
>table.
>
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>5/3 PLS-00493: invalid reference to a server-side object in a local
>context
>5/3 PL/SQL: SQL Statement ignored

You cannot select ... INTO ... an array (there is in Oracle8i, release 8.1 a BULK collect method in a select but it can select into ARRAYS of SCALARS, not an array of records). To do what you are trying to do you would:

ops$tkyte_at_8i> create or replace procedure my_proc   2 as

  3          type t_emp is varray(10) of emp%rowtype;
  4          v_emp t_emp := t_emp();
  5  begin
  6          for x in ( select * from emp where rownum < 5 )
  7          loop
  8                  v_emp.extend;
  9                  v_emp(v_emp.count) := x;
 10          end loop;

 11
 12
 13

 14          for i in 1 .. v_emp.count loop
 15                  dbms_output.put_line( v_emp(i).ename );
 16          end loop;

 17 end;
 18 /

Procedure created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> exec my_proc
smith
allen
ward
jones

PL/SQL procedure successfully completed.

OR, you could do this:

ops$tkyte_at_8i> create or replace type myScalarType as object   2 (

  3   EMPNO                  NUMBER(4),
  4   ENAME                  VARCHAR2(10),
  5   JOB                    VARCHAR2(9),
  6   MGR                    NUMBER(4),
  7   HIREDATE               DATE,
  8   SAL                    NUMBER(7,2),
  9   COMM                   NUMBER(7,2),
 10   DEPTNO                 NUMBER(2)

 11 )
 12 /
Type created.

ops$tkyte_at_8i> create or replace type myTableType as table of myScalarType;   2 /
Type created.

ops$tkyte_at_8i> create or replace procedure my_proc   2 is
  3 v_emp myTableType;
  4 begin
  5

  6          select cast( multiset( select * from emp where rownum < 5 )
  7                                   AS myTableType )
  8            into v_emp
  9            from dual;

 10
 11

 12          for i in 1 .. v_emp.count loop
 13                  dbms_output.put_line( v_emp(i).ename );
 14          end loop;

 15
 16 end;
 17 /

Procedure created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> exec my_proc
smith
allen
ward
jones

PL/SQL procedure successfully completed.

You cannot use the EMP%ROWTYPE in the SQL Type definition -- you must be explicit. You must do this in SQL -- not PLSQL (the create types) so the SQL engine can see them.

--
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 Thu Feb 03 2000 - 07:38:03 CST