Home » SQL & PL/SQL » SQL & PL/SQL » Array of objects..help me plz
Array of objects..help me plz [message #9866] Tue, 09 December 2003 19:22 Go to next message
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 #9869 is a reply to message #9866] Wed, 10 December 2003 08:12 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
declare
  ea  empArray := empArray();
begin
  ea.extend;
  ea(ea.count) := myEmpArray(1, 'John', 'Doe', 100);
  ea.extend;
  ea(ea.count) := myEmpArray(2, 'Jane', 'Smith', 100);
end;
/
Re: Array of objects..help me plz [message #9876 is a reply to message #9869] Wed, 10 December 2003 20:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Array of objects..help me plz [message #9880 is a reply to message #9878] Thu, 11 December 2003 02:20 Go to previous message
resy
Messages: 86
Registered: December 2003
Member
thanx a lot.
Previous Topic: How to get the range of values from the table
Next Topic: What is FILE_ID column in DBA_EXTENTS?
Goto Forum:
  


Current Time: Wed Apr 24 23:58:50 CDT 2024