OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Hi

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Hi [message #8004]

Tue, 22 July 2003 00:55

Need some help
Messages: 1
Registered: July 2003

Junior Member

I am an old user of Oracle (pre Version 7) returning the Oracle workforce again. I have an interview lined up for this week. I'm a little confused about order heirarchies and the concept of nested tables. Could anyone please clarify this to me? Also, how does a binary row index function?

Thank you for your time.

Report message to a moderator

Re: Hi [message #8017 is a reply to message #8004]

Tue, 22 July 2003 07:28

hardik
Messages: 12
Registered: December 2001

Junior Member

here u go
i dont know may be this would help u out
BEst of luck wid ur interview

SQL> Create or Replace Type ObjType as Object(Add1 Varchar2(20),

2 Add2 Varchar2(20),Zip Number(6))

3 /

Type created.

SQL> Create or Replace Type NestType as Table Of ObjType

2 /

Type created.

SQL> Create Table EmpDetails (Empno Number,Ename Varchar2(20),

2 Address NestType) Nested Table Address Store as Emp_Nest;

Table created.

SQL> Insert into EmpDetails Values(1001,'Hariom',

2 NestType(ObjType('Vrundavan Flats','Navarangpura',380009),

3 ObjType('SSIL','Ashram Road',380008))) ;

1 row created.

SQL> Insert into EmpDetails Values(1002,'Kumar',

2 NestType(ObjType('Vrundavan Flats','Navarangpura',380009),

3 ObjType('SSIL','Ashram Road',380008))) ;

1 row created.

SQL> Insert into EmpDetails Values(1003,'Verma',

2 NestType(ObjType('Vrundavan Flats','Navarangpura Near PS',380009),

3 ObjType('SSIL','Ashram Road',380008)))

1 row created.

SQL> select * from empdetails;

EMPNO ENAME

--------- --------------------

ADDRESS(ADD1, ADD2, ZIP)

----------------------------------------------------------------------------------------------------

1001 Hariom

NESTTYPE(OBJTYPE('Vrundavan Flats', 'Navarangpura', 380009), OBJTYPE('SSIL', 'Ashram Road', 380008))

1002 Kumar

NESTTYPE(OBJTYPE('Vrundavan Flats', 'Navarangpura', 380009), OBJTYPE('SSIL', 'Ashram Road', 380008))

1003 Verma

NESTTYPE(OBJTYPE('Vrundavan Flats', 'Navarangpura Near PS', 380009), OBJTYPE('SSIL', 'Ashram Road', 380008))

SQL> Select * from The(Select Address From EmpDetails Where Empno=1001);

ADD1 ADD2 ZIP

-------------------- -------------------- ---------

Vrundavan Flats Navarangpura 380009

SSIL Ashram Road 380008

SQL> Select * from The(Select Address From EmpDetails Where Empno=1003);

ADD1 ADD2 ZIP

-------------------- -------------------- ---------

Vrundavan Flats Navarangpura Near PS 380009

SSIL Ashram Road 380008

SQL> Select * From The(Select Address From EmpDetails Where Empno=1003)

Where Add2='Ashram Road';

ADD1 ADD2 ZIP

-------------------- -------------------- ---------

SSIL Ashram Road 380008

SQL> Select Address From EmpDetails;

ADDRESS(ADD1, ADD2, ZIP)

----------------------------------------------------------------------------------------------------

NESTTYPE(OBJTYPE('Vrundavan Flats', 'Navarangpura', 380009), OBJTYPE('SSIL', 'Ashram Road', 380008))

NESTTYPE(OBJTYPE('Vrundavan Flats', 'Navarangpura', 380009), OBJTYPE('SSIL', 'Ashram Road', 380008))

NESTTYPE(OBJTYPE('Vrundavan Flats', 'Navarangpura Near PS', 380009), OBJTYPE('SSIL', 'Ashram Road', 380008))

SQL> Update The(Select Address From EmpDetails Where Empno=1002) Set

2 Add1='Abhilasha Appt',Add2='Jodhpur',Zip=380006 Where

3 Add1='Vrundavan Flats';

1 row updated.

SQL> Select * From The(Select Address From EmpDetails Where Empno=1002);

ADD1 ADD2 ZIP

-------------------- -------------------- ---------

Abhilasha Appt Jodhpur 380006

SSIL Ashram Road 380008

SQL> Select * From The(Select Address From EmpDetails Where Empno=1003);

ADD1 ADD2 ZIP

-------------------- -------------------- ---------

Vrundavan Flats Navarangpura Near PS 380009

SSIL Ashram Road 380008

SQL> Insert into The(Select Address From EmpDetails Where Empno=1003) Values

2 ('No 15','Navrangpura',370008);

1 row created.

SQL> Select * From The(Select Address From EmpDetails Where Empno=1003);

ADD1 ADD2 ZIP

-------------------- -------------------- ---------

Vrundavan Flats Navarangpura Near PS 380009

SSIL Ashram Road 380008

No 15 Navrangpura 370008

SQL> Insert into The(Select Address From EmpDetails Where Empno=1003)

2 (Add2) Values('Road');

1 row created.

SQL> Select * From The (Select Address From EmpDetails Where Empno=1003);

ADD1 ADD2 ZIP

-------------------- -------------------- ---------

Vrundavan Flats Navarangpura Near PS 380009

SSIL Ashram Road 380008

No 15 Navrangpura 370008

Road

SQL> Delete From The(Select Address From EmpDetails Where Empno=1003)

Where Add1='No 15';

1 row deleted.

SQL> Select * From The(Select Address From EmpDetails Where Empno=1003);

ADD1 ADD2 ZIP

-------------------- -------------------- ---------

Vrundavan Flats Navarangpura Near PS 380009

SSIL Ashram Road 380008

Road

Report message to a moderator

Previous Topic:	view text
Next Topic:	Urgent !!!!!!!!!!!! Please help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 04:42:29 CDT 2024