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

what is definer rights invoke rights?

1 Answers  

---

Can you load data into multiple tables at once? : aql loader

0 Answers  

---

Is primary key clustered or nonclustered?

0 Answers  

---

What is rownum?

0 Answers  

---

What is the maximum size of sqlite database?

0 Answers  

---

how to create a new view in mysql? : Sql dba

0 Answers  

---

List different type of expressions with the example.

0 Answers  

---

I have done oracle 10g. I need a project knowledge. So if u please send a project how it should be done,Or you can send email link. I will be very grateful to u.

1 Answers  

---

What is a database trigger ? Name some usages of database trigger ?

4 Answers  

---

What will be the output of this Query? select to_char(trunc(add_months(sysdate-3),mm),mm/dd/yyyy) from dual

6 Answers   Cap Gemini, TCS,

---

What is meant by Join? What are the different types of Joins available? Explain.

5 Answers   Cap Gemini,

---

What is the difference between delete and truncate statement in sql?

0 Answers  

---