PL/SQL Array generation

[Pierre Oub]

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

--
Pierre Oub

Remove "123" to answer me

[Thomas Kyte]

A copy of this was sent to "Pierre Oub" <POu...@hotmail.com>
(if that email address didn't require changing)

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@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@8i>
ops$tkyte@8i> exec my_proc
smith
allen
ward
jones

PL/SQL procedure successfully completed.

OR, you could do this:

ops$tkyte@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@8i> create or replace type myTableType as table of myScalarType;
2 /
Type created.

ops$tkyte@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@8i>
ops$tkyte@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 tk...@us.oracle.com
Oracle Service Industries Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation