Search code examples
sqloracle11gnestedcreate-table

Getting varchar in colum value


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


Solution

  • 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.