Home » SQL & PL/SQL » SQL & PL/SQL » Varrays
icon1.gif  Varrays [message #186685] Wed, 09 August 2006 01:33 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi,

How to insert another phone number to the already existing employee in varray concept?

CREATE OR REPLACE TYPE PHONES_TY IS VARRAY(3) OF NUMBER
/
Type created.

CREATE TABLE EMPLOYEES
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
PHONES PHONES_TY
);

Table Created.

INSERT INTO EMPLOYEES VALUES
(
1,'RAJ',PHONES_TY(9989491111,9989492222)
);

1 Row created.

INSERT INTO EMPLOYEES VALUES
(
2,'PAL',PHONES_TY(9989494444,9989495555,9989496666)
);

1 Row Created.

COMMIT;

I am in need of inserting 3rd phone number ( 9989493333 ) to
the employee no: 1.

How can i do that?

Regards
Yashora

Re: Varrays [message #186692 is a reply to message #186685] Wed, 09 August 2006 02:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm not sure, but I'll have a go. Try variations of this:
UPDATE employees
SET phones = PHONES_TY(phones(1), phones(2), '9989493333')
WHERE empno = 1


Ross Leishman
Re: Varrays [message #186723 is a reply to message #186685] Wed, 09 August 2006 04:19 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

try this

UPDATE Empolyees SET PHOINES = (PHONES_TY(9989491111,9989492222, 9989493333 ))
    WHERE EMPNO= 1

Quote:


Oops i didnt see rleishman answer...


Naveen

[Updated on: Wed, 09 August 2006 04:21]

Report message to a moderator

Re: Varrays [message #186791 is a reply to message #186685] Wed, 09 August 2006 10:12 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
SQL> select * from emps;

     EMPNO ENAME      PHONES
---------- ---------- --------------------------------------------------
         1 RAJ        PHONES_TY(9989491111, 9989492222)
         2 PAL        PHONES_TY(9989494444, 9989495555, 9989496666)

SQL> UPDATE emps e1
  2  SET    e1.phones =
  3        (CAST(MULTISET(
  4             SELECT * FROM (SELECT ptab.*
  5                            FROM   emps e2, TABLE(e2.phones) ptab
  6                            WHERE  e2.empno = 1
  7                            UNION
  8                            SELECT 9989493333 FROM dual))
  9               AS phones_ty))
 10  WHERE e1.empno = 1;

1 row updated.

SQL> select * from emps;

     EMPNO ENAME      PHONES
---------- ---------- --------------------------------------------------
         1 RAJ        PHONES_TY(9989491111, 9989492222, 9989493333)
         2 PAL        PHONES_TY(9989494444, 9989495555, 9989496666)
Previous Topic: Ora10g MVs & QR
Next Topic: Data manipulation is not possible on views
Goto Forum:
  


Current Time: Sat Dec 10 20:55:43 CST 2016

Total time taken to generate the page: 0.08284 seconds