Re: Function to strip the SELECT OUTPUT

Lucas Carvalho Teixeira wrote:
> 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.

Go to Morgan's Library at www.psoug.org
Click on Types
Scroll down to Table Unnesting

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org