Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: formating the nestedtable select output

Re: formating the nestedtable select output

From: <fitzjarrell_at_cox.net>
Date: Wed, 20 Jun 2007 11:08:02 -0700
Message-ID: <1182362882.443504.162590@q75g2000hsh.googlegroups.com>


On Jun 20, 12:46 pm, Lucas Carvalho Teixeira <lucascarva..._at_gmail.com> wrote:
> CREATE TYPE Course AS OBJECT (
> course_no NUMBER(4),
> title VARCHAR2(35),
> credits NUMBER(1));
>
> CREATE TYPE CourseList AS TABLE OF Course;
>
> CREATE TABLE department (
> name VARCHAR2(20),
> director VARCHAR2(20),
> office VARCHAR2(20),
> courses CourseList)
> NESTED TABLE courses STORE AS courses_tab;
>
> INSERT INTO department
> VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
> CourseList(Course(1000, 'General Psychology', 5),
> Course(2100, 'Experimental Psychology', 4),
> Course(2200, 'Psychological Tests', 3),
> Course(2250, 'Behavior Modification', 4),
> Course(3540, 'Groups and Organizations', 3),
> Course(3552, 'Human Factors in Busines', 4),
> Course(4210, 'Theories of Learning', 4),
> Course(4320, 'Cognitive Processes', 4),
> Course(4410, 'Abnormal Psychology', 4)));
>
> I just want to remove the "CourseList" and "Course" words from my
> select output.
> I can do it on my app but it will require a high computer processing.
> Cuz the select output can return to me more than 1000 rows.
> So I want to know if it is possible to do into the oracle server.
>
> When I do the SELECT * FROM department;
>
> The "courses" fields returns:
> COURSELIST(COURSE(1000, 'General Psychology', 5), COURSE(2100,
> 'Experimental Psychology', 4), COURSE(2200, 'Psychological Tests', 3),
> COURSE(2250, 'Behavior Modification', 4), COURSE(3540, 'Groups and
> Organizations', 3), COURSE(3552, 'Human Factors in Busines', 4),
> COURSE(4210, 'Theories of Learning', 4), COURSE(4320, 'Cognitive
> Processes', 4), COURSE(4410, 'Abnormal Psychology', 4))
>
> But I need to return this:
> ((1000, 'General Psychology', 5), (2100, 'Experimental Psychology',
> 4), (2200, 'Psychological Tests', 3), (2250, 'Behavior Modification',
> 4),(3540, 'Groups and Organizations', 3), (3552, 'Human Factors in
> Busines', 4),(4210, 'Theories of Learning', 4), (4320, 'Cognitive
> Processes', 4),(4410, 'Abnormal Psychology', 4))

Did not the original answer appeal to you? It's found here, if you have forgotten about your original post of this very same question:

http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/b4975d2d5a16f0e0

David Fitzjarrell Received on Wed Jun 20 2007 - 13:08:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US