Function to strip the SELECT OUTPUT

From: Lucas Carvalho Teixeira <lucascarvalho_at_gmail.com>
Date: Thu, 14 Jun 2007 20:41:36 -0000
Message-ID: <1181853696.530727.156500@q19g2000prn.googlegroups.com>

Hello,
 I have a doubt if it is possible to do.

Lets go. Figure out this simple example.

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

When you execute it:

$ echo -e "SET ECHO OFF HEAD OFF COLSEP','TRIM ON TAB ON LINESIZE 32000 \n SELECT * FROM department;" | sqlplus -S <schema>/ <password>@<ip>

The output:

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

But I don't like the nested table output cuz they return the COURSELIST and COURSE strings.
How could I strip this output?

Can I write a function to delete the COURSELIST and COURSE words and replace the '(' ')' for '{' '}'.

Thank you. Received on Thu Jun 14 2007 - 15:41:36 CDT