What are Nested Tables? How will u delete 5 rows from Nested Tables

What are Nested Tables? How will u delete 5 rows from Nested
Tables

Question Posted / guest

3 Answers
10068 Views
IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What are Nested Tables? How will u delete 5 rows from Nested Tables..

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'
);

Is This Answer Correct ?

16 Yes

0 No

What are Nested Tables? How will u delete 5 rows from Nested Tables..

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

Is This Answer Correct ?

3 Yes

0 No

What are Nested Tables? How will u delete 5 rows from Nested Tables..

Answer / rahul k.

Excellent Keshav...Very nice...

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

Thnanks

Is This Answer Correct ?

1 Yes

1 No

Post New Answer