CREATE TYPE CourseList1 AS TABLE OF VARCHAR2(64);
/
CREATE TABLE department1
(
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses1 CourseList1)
NESTED TABLE courses1 STORE AS courses_tab1;
/
BEGIN
INSERT INTO department1
VALUES('English', 'Lynn Saunders', 'Breakstone Hall 205',
CourseList1('Expository Writing',
'Film and Literature',
'Modern Science Fiction',
'Discursive Writing',
'Modern English Grammar',
'Introduction to Shakespeare',
'Modern Drama',
'The Short Story',
'The American Novel'));
END;
When I retrieve data using
select * from department1
My output is:
English Lynn Saunders Breakstone Hall 205 VARCHAR(Expository Writing,Film and Literature,Modern Science Fiction,Discursive Writing,Modern English Grammar,Introduction to Shakespeare,Modern Drama,The Short Story,The American Novel)
I am unaware and confused, why this VARCHAR is showing
You're just seeing the normal way SQL Developer shows a nested table. Although it shows as the nested table column name (COURSELIST1('Expository Writing', ...)
) rather than VARCHAR(...)
, at least in SQL Developer 4.2. If you run the query as a statement, so the the results appear in the Query Result window instead, it shows the schema name too. Different clients may choose to display the nested table in other ways.
You can unnest your nested table:
select d.name, d.director, d.office, t.column_value as course
from department1 d
cross join table(courses1) t;
NAME DIRECTOR OFFICE COURSE
-------------------- -------------------- -------------------- ----------------------------------------------------------------
English Lynn Saunders Breakstone Hall 205 Expository Writing
English Lynn Saunders Breakstone Hall 205 Film and Literature
English Lynn Saunders Breakstone Hall 205 Modern Science Fiction
English Lynn Saunders Breakstone Hall 205 Discursive Writing
English Lynn Saunders Breakstone Hall 205 Modern English Grammar
English Lynn Saunders Breakstone Hall 205 Introduction to Shakespeare
English Lynn Saunders Breakstone Hall 205 Modern Drama
English Lynn Saunders Breakstone Hall 205 The Short Story
English Lynn Saunders Breakstone Hall 205 The American Novel
9 rows selected.
If you want them as a comma-separated string you can use listagg()
to generate that:
select d.name, d.director, d.office,
listagg(t.column_value, ',') within group (order by t.column_value) as courses
from department1 d
cross join table(courses1) t
group by d.name, d.director, d.office;
NAME DIRECTOR OFFICE COURSES
-------------------- -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
English Lynn Saunders Breakstone Hall 205 Discursive Writing,Expository Writing,Film and Literature,Introduction to Shakespeare,Modern Drama,Modern English Grammar,Modern Science Fiction,The American Novel,The Short Story
It depends what you want to do with the nested table data really.