Answer / keshav

CREATE Or Replace TYPE AddressType AS OBJECT (
street VARCHAR2(15),
city VARCHAR2(15),
state CHAR(2),
zip VARCHAR2(5)
);

CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF AddressType;

CREATE TABLE employee (
id INTEGER PRIMARY KEY,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
addresses nested_table_AddressType
)
NESTED TABLE
addresses
STORE AS
nested_addresses;

INSERT INTO employee VALUES (
1, 'Steve', 'Brown',
nested_table_AddressType(
AddressType('2 Ave', 'City', 'MA', '12345'),
AddressType('4 Ave', 'City', 'CA', '54321')
)
);

DELETE FROM TABLE (
SELECT addresses FROM employee WHERE id = 1
) addr
WHERE
VALUE(addr) = AddressType(
'4 Ave', 'City', 'CA', '54321'
);

Answer / subrat ray

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts.
You can define equivalent SQL types,allowing nested tables to be stored in database tables and manipulated through SQL.
->it does not have fixed upper bound.
->the data storage is out of line.

DECLARE
TYPE COURSELIST IS TABLE OF VARCHAR2(10);
COURSES COURSELIST;
BEGIN
COURSES:=COURSELIST('A','B','C','D','E','F','G','H','I');
DBMS_OUTPUT.PUT_LINE('THE TOTAL NO OF ELEMENTS ARE:'||COURSES.COUNT);
COURSES.DELETE(1,5);
DBMS_OUTPUT.PUT_LINE('THE NO OF ELEMENTS ARE PRESENT IS:'||COURSES.COUNT);
END;
SQL> /
THE TOTAL NO OF ELEMENTS ARE:9
THE NO OF ELEMENTS ARE PRESENT IS:4

Answer / rahul k.

Excellent Keshav...Very nice...

Table within a table called nested table same as nested
loop in any procedural languages...

Thnanks

Is it possible to remove child records without removing master table records...the two having pk,fk relationship?

0 Answers  

---

What is triggering circuit?

0 Answers  

---

If we declare constraints Unique and Not null on a single column then it will act as a Primary key, so what is the use of primary key??

3 Answers  

---

Can a foreign key be null?

0 Answers  

---

Does sap use sql?

0 Answers  

---

What is pivot query?

0 Answers  

---

define sql update statement ? : Sql dba

0 Answers  

---

what is “go” in t-sql? : Transact sql

0 Answers  

---

What is %rowtype in pl sql?

0 Answers  

---

Hi how to import oracle sequence in Informatica? Please write stored procedure code that will import oracle sequence in Informatica SP transformation as per below scenario Oracle table product list Pro_id, pro_name 101, LED Lights. 102, 20watt CFL Lights. 103, 30 watt CFL lights Now a new flat file with new product list needs to be added to oracle table product list with oracle sequence. flat file product Prono,pro_name, 1, 20 watt tube light 2, 30 watt tube light & target should be like 101, LED Lights. 102, 20watt CFL Lights. 103, 30 watt CFL lights. 104, 20 watt tube light 105, 30 watt tube light thks reg suvarna joshi suvarnaatsuvarna@rediffmail.com

0 Answers   TCS,

---

what are the 'mysql' command line options? : Sql dba

0 Answers  

---

What is the purpose of design view?

0 Answers  

---