What are Nested Tables? How will u delete 5 rows from Nested
Tables
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
Explain the difference between cursor declared in procedures and cursors declared in the package specification?
What is Raise_application_error ?
Table name: T1, it has only one column. col1 ------ c b a b b b b d s a a t s Requirement: I need the following output from the above base table by using SQL query. col1 Cnt ----- ------- a 3 b 5 Others 5 Please help. Thanks Guru v.gurus@in.com
What are all types of user defined functions?
How would you pass hints to the sql processor?
what are the different functions in sorting an array? : Sql dba
What is sql mysql pl sql oracle?
What jobs use sql?
What is trigger in pl sql?
What do we need to check in database testing?
How do you declare a constant?
I have the table like this S.No Name ID 01 Xyz 123 I want the result as 01Xyz123 How to write the query to retrieve the entire row data in a single column?