Array of objects..help me plz [message #9866] |
Tue, 09 December 2003 19:22 |
resy
Messages: 86 Registered: December 2003
|
Member |
|
|
hi,
please check my code.
CREATE OR REPLACE TYPE MYEMPARRAY AS OPBJECT
(
EMPID NUMBER(5),
FIRSTNAME VARCHAR2(10),
LASTNAME VARCHAR2(10),
SALARY NUMBER(6)
);
CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF MYEMPARRAY;
Now anybody can tell me how to enter values to MYEMPARRAY object and how to retrieve values from that?
thanx a lot.
|
|
|
|
Re: Array of objects..help me plz [message #9876 is a reply to message #9869] |
Wed, 10 December 2003 20:47 |
resy
Messages: 86 Registered: December 2003
|
Member |
|
|
thanx for ur reply.
now i've a table called MYEMP.
SQL> desc myemp;
Name Null? Type
------------------------------ -------- --------------
EMPID NOT NULL NUMBER(5)
FIRSTNAME VARCHAR2(10)
LASTNAME VARCHAR2(10)
SALARY NUMBER(6)
DEPT VARCHAR2(5)
I wanna insert the values in the MyempArray into the table MYEMP.
can u help me once again.
thanx.
|
|
|
Re: Array of objects..help me plz [message #9878 is a reply to message #9876] |
Wed, 10 December 2003 21:57 |
|
Barbara Boehmer
Messages: 9090 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
scott@ORA92> CREATE OR REPLACE TYPE MYEMPARRAY AS OBJECT
2 (EMPID NUMBER ( 5),
3 FIRSTNAME VARCHAR2 (10),
4 LASTNAME VARCHAR2 (10),
5 SALARY NUMBER ( 6),
6 DEPT VARCHAR2 ( 5)
7 )
8 /
Type created.
scott@ORA92> CREATE OR REPLACE TYPE EMPARRAY is VARRAY (20) OF MYEMPARRAY
2 /
Type created.
scott@ORA92> CREATE TABLE myemp
2 (EMPID NUMBER ( 5) NOT NULL,
3 FIRSTNAME VARCHAR2 (10),
4 LASTNAME VARCHAR2 (10),
5 SALARY NUMBER ( 6),
6 DEPT VARCHAR2 ( 5))
7 /
Table created.
scott@ORA92> declare
2 ea empArray := empArray();
3 begin
4 ea.extend;
5 ea(ea.count) := myEmpArray(1, 'John', 'Doe', 100, 'DEPT1');
6 ea.extend;
7 ea(ea.count) := myEmpArray(2, 'Jane', 'Smith', 100, 'DEPT2');
8 FOR i IN 1 .. ea.COUNT
9 LOOP
10 INSERT INTO myemp (empid, firstname, lastname, salary, dept)
11 VALUES (ea(i).empid, ea(i).firstname, ea(i).lastname, ea(i).salary, ea(i).dept);
12 END LOOP;
13 end;
14 /
PL/SQL procedure successfully completed.
scott@ORA92> SELECT * FROM myemp
2 /
EMPID FIRSTNAME LASTNAME SALARY DEPT
---------- ---------- ---------- ---------- -----
1 John Doe 100 DEPT1
2 Jane Smith 100 DEPT2
|
|
|
|