Function to strip the SELECT OUTPUT

[Lucas Carvalho Teixeira]

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.

[DA Morgan]

Go to Morgan's Library at www.psoug.org
Click on Types
Scroll down to Table Unnesting
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org