Enabling Enterprise Provisioning and Security in Oracle Portal

About This Presentation

Title:

Enabling Enterprise Provisioning and Security in Oracle Portal

Description:

Greg Pike. Managing Principal. Piocon Technologies. History: 15 year Oracle solution provider ... Method 2: COLLECT (Greg Pike) ... – PowerPoint PPT presentation

Number of Views:53

Avg rating:3.0/5.0

Slides: 51

Provided by: greg67

Learn more at: http://www.nocoug.org

Category:

more less

Transcript and Presenter's Notes

Title: Enabling Enterprise Provisioning and Security in Oracle Portal

1
Aces in the Hole Learning Advanced SQL
Techniques from the OTN Forum Pros
Greg PikePiocon Technologies
2
Greg PikeManaging PrincipalPiocon Technologies

History 15 year Oracle solution provider
Home ChicagoLand
Focus Business Intelligence, Data Warehousing,
Portal, web applications, devious SQL
Blog www.SingleQuery.com
Client list includes

3
The Oracle Discussion Forums

An interactive community for sharing information,
questions, and comments about Oracle products and
related technologies
Most forums are moderated by product managers,
and all of them are frequently visited by
knowledgeable users from the community
Anyone can read messages but must be a registered
member to post a question or response
Posts to the SQL and PL/SQL Forum are often
answered in minutes

Source http//www.oracle.com/technology/forums/f
aq.html
4
Who Can Learn from the OTN SQL and PL/SQL Forum?

Beginners Got a questionits probably already
been answered 100 times. Please search for a
solution first, but dont be shy to ask
Intermediate Learners The SQL and PL/SQL Forum
has a wealth of knowledge on every imaginable
topic
Experts You may not feel like an expert after a
visit to this Forum
Unique Problem This is your real-time resource
for solutions
Contributor Get involved and share your
expertise

5
Who Contributes? Introducing the Experts and
Recent Forum Post Counts

Warren Tolentino 4,400
Devmiral 2,200
Nicolas Gasparotto 14,900
Marias 1,300
The Flying Spontinalli 700
Rob van Wijk 3,900
Michaels 3,400
John Spencer 3,700

Justin Cave 18,700
Billy Verreynne 5,500
APC 9,500
BluShadow 6,200
William Robertson 4,500
Volder 950
Yingkuan 7000
Kamal Kishore 7,300

and 100s of people worldwide with a passion for
problem solving
6
QIf the advice is free, how good can it be?A
The Program. Enough said.

The Oracle ACE program formally recognizes
advocates of Oracle technology with strong
credentials as evangelists and educators
Oracle ACE recipients are chosen based on their
significant contributions and activity in the
Oracle technical community with candidates
nominated by anyone in the Oracle Technology and
Applications communities.

Source http//www.oracle.com/technology/community
/oracle_ace/index.html
7
The Oracle Aces

Oracle ACEs are known for their strong
credentials as Oracle community enthusiasts and
advocates with candidates nominated by anyone in
the Oracle Technology and Applications
communities.
Technical proficiency
Oracle-related blog
Oracle discussion forum activity
Published white paper(s) and/or article(s)
Presentation experience
Beta program participant
Oracle user group member
Oracle certification

8
Just a Few of the 188 Oracle Aces

Cary Milsap (speaking today)
Peter Koletzke (speaking today)
Dan Norris (Piocon)
Paul Dorsey
Steven Feuerstein
Ken Jacobs
Tom Kyte
Mark Rittman
Laurent Schneider

9
Case Study Examining a Recent Post

The following post is from May 28, 2007 and
elicited 33 replies from long-time Forum
contributors including Oracle Aces
http//forums.oracle.com/forums/thread.jspa?messag
eID1864354
Only a portion of the solutions are presented
here and the supporting commentary is omitted.
Some queries have been altered to fit on the page
but retain their basic logic
Thanks to the following OTN experts who
contributed to this thread (OTN handle shown)

Warren Tolentino
Devang Bhatt (devmiral)
Nicolas Gasparotto
marias

The Flying Spontinalli
Rob van Wijk
michaels

10
The Original Question
I need to get the count on how many customers
answered for each set of questions. For the
above data, this should be the output
Questions IDs Customer Count 1
1 1,2,4 2 3,4
2 2,3 1 2
1 The Question IDs can have as
many as 10 questions. Thanks in advance.

I need your help to generate a report. I'm using
Oracle 9i database. This is our data
Customer_ID Question_ID 10001
1 10002 1
10002 2 10002
4 10003 3 10003
4 10004
2 10004 3 10005
1 10005 2
10005 4 10006
3 10006 4 10007
2

11
Examining a Few of the Posted Solutions

Method 1 Hierarchical Query
Method 2 COLLECT
Method 3 XML
Method 4 MODEL
Method 5 Pipelined Funtions

12
Method 1 Hierarchical Query (Warren)

For querying datasets that contain a parent-child
relationship between rows
Recursively walks through the tree of
relationships from the top-down or bottom-up
Includes tools for determining location in the
hierarchy and the nature of individual nodes

13
Method 1 Hierarchical Query

SELECT ci2.questions,
COUNT(ci2.cnt) Customer Count"
FROM (SELECT ci1.customer_id,
SUBSTR(MAX(SUBSTR(SYS_CONNECT_
BY_PATH
(ci1.question_id,','),2)),1,40
) questions,
ci1.cnt
FROM (

SELECT customer_id,
question_id,
ROW_NUMBER() OVER (PARTITION BY
customer_id ORDER BY
customer_id,
question_id) rn,
COUNT() OVER (PARTITION BY
customer_id,question_id) cnt
FROM customer_inquiry
) ci1 DTSRT
WITH ci1.rn 1 CONNECT BY ci1.rn
PRIOR ci1.rn 1 AND PRIOR
ci1.customer_id ci1.customer_id
GROUP BY ci1.customer_id, ci1.cnt )
ci2 GROUP BY questions
14
Step 1 The pseudo-parent-child relationship

SELECT customer_id,
question_id,
ROW_NUMBER() OVER (PARTITION BY
customer_id ORDER BY
customer_id, question_id) rn,
COUNT() OVER (PARTITION BY
customer_id,question_id) cnt
FROM customer_inquiry

Customer_ID Question_ID 10001 1
10002 1 10002 2
10002 4 10003 3 10003
4 10004 2 10004 3
10005 1 10005 2
10005 4 10006 3 10006
4 10007 2
Customer_ID Question_ID RN CNT 10001
1 1 1 10002 1 1 1
10002 2 2 1 10002 4
3 1 10003 3 1 1
10003 4 2 1 10004 2
1 1 10004 3 2 1
10005 1 1 1 10005 2
2 1 10005 4 3 1
10006 3 1 1 10006 4
2 1 10007 2 1 1
15
Step 2 Employ SYS_CONNECT_BY_PATH

SELECT ci2.questions,
COUNT(ci2.cnt) Customer Count"
FROM (SELECT ci1.customer_id,
SUBSTR(MAX(SUBSTR(SYS_CONNECT_
BY_PATH
(ci1.question_id,','),2)),1,40
) questions,
ci1.cnt
FROM (

SELECT customer_id,
question_id,
ROW_NUMBER() OVER (PARTITION BY
customer_id ORDER BY
customer_id,
question_id) rn,
COUNT() OVER (PARTITION BY
customer_id,question_id) cnt
FROM customer_inquiry
) ci1 START
WITH ci1.rn 1 CONNECT BY ci1.rn
PRIOR ci1.rn 1 AND PRIOR
ci1.customer_id ci1.customer_id
GROUP BY ci1.customer_id, ci1.cnt )
ci2 GROUP BY questions
SYS_CONNECT_BY_PATH returns the path of a column
value from root to node, with column values
separated by char for each row returned by
CONNECT BY condition.
16
Step 2 Employ SYS_CONNECT_BY_PATH

SELECT ci2.questions,
COUNT(ci2.cnt) Customer Count"
FROM (SELECT ci1.customer_id,
SUBSTR(MAX(SUBSTR(SYS_CONNECT_
BY_PATH
(ci1.question_id,','),2)),1,40
) questions,
ci1.cnt
FROM (

Questions IDs Customer Count 1
1 1,2,4 2 3,4
2 2,3 1 2
1
SYS_CONNECT_BY_PATH returns the path of a column
value from root to node, with column values
separated by char for each row returned by
CONNECT BY condition.
17
Method 2 COLLECT (Greg Pike)

COLLECT takes as its argument a column of any
type and creates a nested table of the input type
out of the rows selected
GROUP BY can determine how the rows are COLLECTED
The database creates its own collection object to
hold the data
For the required output, CAST the results into a
more usable form
Get the data out of the collection with a
function

18
Method 2 The Query

SELECT SUBSTR(col,1,10) questions,
COUNT()
FROM (
SELECT customer_id,
tab_to_string(
CAST(
AS t_number_tab
)
) col
FROM answers
GROUP BY customer_id
)
GROUP BY col

COLLECT(question_id)
19
Step 1 COLLECT

SELECT customer_id cust_id,
COLLECT(question_id)
FROM customer_inquiry
GROUP BY customer_id

Customer_ID Question_ID 10001 1
10002 1 10002 2
10002 4 10003 3 10003
4 10004 2 10004 3
10005 1 10005 2
10005 4 10006 3 10006
4 10007 2
CUST_ID COLLECT(QUESTION_ID) -------
--------------------------------- 10001
SYSTP3f5GcsxkSvyjQSG5pDbjhA(1) 10002
SYSTP3f5GcsxkSvyjQSG5pDbjhA(1, 2, 4) 10003
SYSTP3f5GcsxkSvyjQSG5pDbjhA(3, 4) 10004
SYSTP3f5GcsxkSvyjQSG5pDbjhA(2, 3) 10005
SYSTP3f5GcsxkSvyjQSG5pDbjhA(1, 2, 4) 10006
SYSTP3f5GcsxkSvyjQSG5pDbjhA(3, 4) 10007
SYSTP3f5GcsxkSvyjQSG5pDbjhA(2)
COLLECT takes as its argument a column of any
type and creates a nested table of the input type
out of the rows selected.
20
Step 2 CAST(COLLECT)

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF
NUMBER
SELECT customer_id,
CAST(COLLECT(question_id) AS
t_number_tab) col
FROM customer_inquiry
GROUP BY customer_id

Customer_ID Question_ID 10001 1
10002 1 10002 2
10002 4 10003 3 10003
4 10004 2 10004 3
10005 1 10005 2
10005 4 10006 3 10006
4 10007 2
CUST_ID COLLECT(QUESTION_ID) -------
--------------------- 10001 T_NUMBER_TAB(1)
10002 T_NUMBER_TAB(1, 2, 4) 10003
T_NUMBER_TAB(3, 4) 10004 T_NUMBER_TAB(2, 3)
10005 T_NUMBER_TAB(1, 2, 4) 10006
T_NUMBER_TAB(3, 4) 10007 T_NUMBER_TAB(2)
CAST converts one built-in data type or
collection-typed value into another built-in data
type or collection-typed value.
21
Step 3 Simple tab_to_string Function

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF
NUMBER
CREATE OR REPLACE FUNCTION tab_to_string
(
p_number_tab IN
t_number_tab,
p_delimiter IN VARCHAR2
DEFAULT ',
) RETURN VARCHAR2 IS
l_string VARCHAR2(32767)
BEGIN
FOR i IN p_number_tab.FIRST ..
p_number_tab.LAST LOOP
IF i ! p_number_tab.FIRST THEN
l_string l_string p_delimiter
END IF
l_string l_string
to_char(p_number_tab(i))
END LOOP
RETURN l_string
END tab_to_string

22
Step 3 tab_to_string(CAST(COLLECT))

SELECT customer_id,
tab_to_string(CAST(COLLECT(question_id) AS
t_number_tab)) col
FROM customer_inquiry
GROUP BY customer_id

Customer_ID Question_ID 10001 1
10002 1 10002 2
10002 4 10003 3 10003
4 10004 2 10004 3
10005 1 10005 2
10005 4 10006 3 10006
4 10007 2
CUST_ID COLLECT(QUESTION_ID) -------
--------------------- 10001 1 10002 1,2,4
10003 3,4 10004 2,3 10005 1,2,4 10006
3,4 10007 2
The tab_to_string procedure converts a table of
numbers to a comma-separated VARCHAR2.
23
Method 2 Putting it all together

SELECT SUBSTR(col,1,10) questions,
COUNT()
FROM (
SELECT customer_id,
FROM answers
GROUP BY customer_id
)
GROUP BY col

tab_to_string( CAST(
COLLECT(question_id)
AS t_number_tab
) ) col
Questions IDs Customer Count 1
1 1,2,4
2 3,4 2 2,3
1 2 1
24
Method 3 XML (The Flying Spontinalli)

XML functions
Convert traditional table data into XML
Inquire upon XML data and fragments
Operate on XML data and fragments
Convert XML data back to character data type
CURSOR function Converts a sub-query into a REF
CURSOR

25
Method 3 The Query

SELECT REPLACE(REPLACE(REPLACE(
questions,'lt/Qgt'C
HR(10)'ltQgt',','
),'ltQgt',NULL
),'lt/Qgt',NULL
) questions,
COUNT() the_count
FROM (
SELECT DISTINCT customer_id,

EXTRACT (
SYS_XMLGEN(seq)
,'/SEQ/XMLTYPE/ROW/Q'
questions
).getstringval()
FROM ( SELECT customer_id,
XMLSEQUENCE
(
CURSOR
(
SELECT
question_id Q
FROM TEST t2
WHERE customer_id t.customer_id
)
) seq
FROM TEST t ) ) GROUP BY
questions
26
Step 1 CURSOR and XMLSEQUENCE
CUST SEQ ----- ------------------------------ 100
01 XMLSEQUENCETYPE(XMLTYPE( ltROWgt
ltQgt1lt/Qgt lt/ROWgt
)) 10002 XMLSEQUENCETYPE(XMLTYPE( ltROWgt
ltQgt1lt/Qgt lt/ROWgt ),
XMLTYPE( ltROWgt ltQgt2lt/Qgt
lt/ROWgt ), XMLTYPE( ltROWgt
ltQgt4lt/Qgt lt/ROWgt

SELECT customer_id,
XMLSEQUENCE(
CURSOR(
SELECT question_id Q
FROM TEST t2
WHERE customer_id
t.customer_id
)
) seq
FROM TEST t

The CURSOR function converts a sub-query into a
REF CURSOR. In this case, XMLSEQUENCE requires a
REF CURSOR.
The XMLSEQUENCE operator is used to split
multi-value results from XMLTYPE queries into
multiple rows.
27
Step 2 SYS_XMLGen and EXTRACT

SELECT DISTINCT customer_id,
EXTRACT(
SYS_XMLGEN(seq),'/S
EQ/XMLTYPE/ROW/Q'
).getstringval ()
questions
FROM (
SELECT customer_id,
XMLSEQUENCE(
CURSOR(
SELECT
question_id Q
FROM TEST
t2
WHERE
customer_id t.customer_id
)
) seq
FROM TEST t
)

The SYS_XMLGen function takes an expression that
evaluates to a particular row and column of the
database, and returns an instance of type XMLType
containing an XML document
Applying EXTRACT to an XMLType value extracts the
node or a set of nodes from the document
identified by the XPath expression. The method
getStringVal() retrieves the text from the
XMLType instance
28
Step 2 SYS_XMLGen and EXTRACT

SELECT DISTINCT customer_id,
EXTRACT(
SYS_XMLGEN(seq),'/S
EQ/XMLTYPE/ROW/Q'
).getstringval ()
questions

CUST SEQ ----- ------------------------------ 100
02 XMLSEQUENCETYPE(XMLTYPE( ltROWgt
ltQgt1lt/Qgt lt/ROWgt ),
XMLTYPE( ltROWgt ltQgt2lt/Qgt
lt/ROWgt ), XMLTYPE( ltROWgt
ltQgt4lt/Qgt lt/ROWgt
CUST QUESTIONS ----- ------------------------- 10
001 ltQgt1lt/Qgt 10002 ltQgt1lt/QgtltQgt2lt/QgtltQgt4lt/Qgt 10003
ltQgt3lt/QgtltQgt4lt/Qgt 10004 ltQgt2lt/QgtltQgt3lt/Qgt 10005
ltQgt1lt/QgtltQgt2lt/QgtltQgt4lt/Qgt 10006 ltQgt3lt/QgtltQgt4lt/Qgt 10
007 ltQgt2lt/Qgt
The SYS_XMLGen function takes an expression that
evaluates to a particular row and column of the
database, and returns an instance of type XMLType
containing an XML document
Applying EXTRACT to an XMLType value extracts the
node or a set of nodes from the document
identified by the XPath expression. The method
getStringVal() retrieves the text from the
XMLType instance
29
Method 3 Back to the Query

SELECT REPLACE(REPLACE(REPLACE(
questions,'lt/Qgt'CHR(10)'ltQgt',
','
),'ltQgt',NULL
),'lt/Qgt',NULL
) questions,
COUNT() the_count
FROM (
SELECT DISTINCT customer_id,

Questions IDs Cust Cnt 1
1 1,2,4
2 3,4 2
2,3 1
2 1
EXTRACT (
SYS_XMLGEN(seq)
,'/SEQ/XMLTYPE/ROW/Q'
questions
).getstringval()
FROM ( SELECT customer_id,
XMLSEQUENCE
(
CURSOR
(
SELECT
question_id Q
FROM TEST t2
WHERE customer_id t.customer_id
)
) seq
FROM TEST t ) ) GROUP BY
questions
30
Method 4 MODEL (Rob van Wijk)

SELECT q "Questions",
COUNT() "Customer Count"
FROM (
SELECT SUBSTR(q,2) q,
rn
FROM a
)
WHERE rn 1
GROUP BY q

MODEL PARTITION BY (cust_id)
DIMENSION BY (ROW_NUMBER() OVER
(PARTITION BY cust_id ORDER BY
quest_id DESC) rn) MEASURES
(CAST(quest_id AS varchar2(20)) q)
RULES ( qany ORDER BY rn
DESC qcv()1 ',' qcv()
)
31
Method 4 The MODEL Portion of the Query

MODEL PARTITION BY (cust_id)
DIMENSION BY (ROW_NUMBER() OVER
(PARTITION BY cust_id
ORDER BY quest_id DESC) rn)
MEASURES (CAST(quest_id AS
varchar2(20)) q)
RULES (
qany ORDER BY rn DESC
qcv()1 ',' qcv()
)

The MODEL clause enables you to create a
multidimensional array by mapping the columns of
a query into three groups partitioning,
dimension, and measure columns
All text on this page from Oracle Database
Data Warehousing Guide
32
Method 4 Dissecting the MODEL Clause

MODEL PARTITION BY (cust_id)
DIMENSION BY (ROW_NUMBER() OVER
(PARTITION BY cust_id
ORDER BY quest_id DESC) rn)
MEASURES (CAST(quest_id AS
varchar2(20)) q)
RULES (
qany ORDER BY rn DESC
qcv()1 ',' qcv()
)

MODEL PARTITION BY (cust_id)
DIMENSION BY (ROW_NUMBER() OVER
(PARTITION BY cust_id
ORDER BY quest_id DESC) rn)
MEASURES (CAST(quest_id AS
varchar2(20)) q)
RULES (
qany ORDER BY rn DESC
qcv()1 ',' qcv()
)

The MODEL clause enables you to create a
multidimensional array by mapping the columns of
a query into three groups partitioning,
dimension, and measure columns
PARTITION columns define the logical blocks of
the result set in a way similar to the partitions
of the analytical functions. Rules in the MODEL
clause are applied to each partition independent
of other partitions
DIMENSION columns define the multi-dimensional
array and are used to identify cells within a
partition. By default, a full combination of
dimensions should identify just one cell in a
partition
All text on this page from Oracle Database
Data Warehousing Guide
34
Method 4 Dissecting the MODEL Clause

MODEL PARTITION BY (cust_id)
DIMENSION BY (ROW_NUMBER() OVER
(PARTITION BY cust_id
ORDER BY quest_id DESC) rn)
MEASURES (CAST(quest_id AS
varchar2(20)) q)
RULES (
qany ORDER BY rn DESC
qcv()1 ',' qcv()
)

The MODEL clause enables you to create a
multidimensional array by mapping the columns of
a query into three groups partitioning,
dimension, and measure columns
PARTITION columns define the logical blocks of
the result set in a way similar to the partitions
of the analytical functions. Rules in the MODEL
clause are applied to each partition independent
of other partitions
DIMENSION columns define the multi-dimensional
array and are used to identify cells within a
partition. By default, a full combination of
dimensions should identify just one cell in a
partition
MEASURES are equivalent to the measures of a fact
table in a star schema.
All text on this page from Oracle Database
Data Warehousing Guide
35
Method 4 Dissecting the MODEL Clause

MODEL PARTITION BY (cust_id)
DIMENSION BY (ROW_NUMBER() OVER
(PARTITION BY cust_id
ORDER BY quest_id DESC) rn)
MEASURES (CAST(quest_id AS
varchar2(20)) q)
RULES (
qany ORDER BY rn DESC
qcv()1 ',' qcv()
)

The MODEL clause enables you to create a
multidimensional array by mapping the columns of
a query into three groups partitioning,
dimension, and measure columns
PARTITION columns define the logical blocks of
the result set in a way similar to the partitions
of the analytical functions. Rules in the MODEL
clause are applied to each partition independent
of other partitions
DIMENSION columns define the multi-dimensional
array and are used to identify cells within a
partition. By default, a full combination of
dimensions should identify just one cell in a
partition
MEASURES are equivalent to the measures of a fact
table in a star schema
RULES are used to manipulate the measure values
of the cells in the multi-dimensional array
defined by partition and dimension columns
All text on this page from Oracle Database
Data Warehousing Guide
36
Method 4 Dissecting the MODEL Clause

quest_id ROW_NUMBER
Cust ID CV() (rn) Q
------- ---- ------- ------
10001 1 1 ,1
10002 1 3 ,1
10002 2 2 ,1,2
10002 4 1 ,1,2,4
10003 3 2 ,3
10003 4 1 ,3,4
10004 2 2 ,2
10004 3 1 ,2,3
10005 1 3 1
10005 2 2 ,1,2
10005 4 1 ,1,2,4
10006 3 2 ,3
10006 4 1 ,3,4
10007 2 1 ,2

PARTITION BY (cust_id)
DIMENSION BY (ROW_NUMBER() OVER (PARTITION
BY cust_id ORDER BY quest_id DESC) rn)
MEASURES (CAST(quest_id AS
varchar2(20)) q)
RULES ( qany ORDER BY rn DESC
qcv(rn)1 ',' qcv(rn) ) Example
q2 q3,q2 NULL,3 q1
q2,q1 ,3,4
37
Method 4 Back to the Query
Questions IDs Customer Count 1
1 1,2,4
2 3,4 2 2,3
1 2 1

SELECT q "Questions",
COUNT() "Customer Count"
FROM (
SELECT SUBSTR(q,2) q,
rn
FROM a
)
WHERE rn 1
GROUP BY q

Oracle Table Functions produce a collection of
rows that can be consumed by a query much like a
table.
Rows from a collection returned by a table
function can also be PIPELINED or returned as
they are produced instead of in a complete set
upon function completion.
Two supported approached for Pipelining
Interface method
PL/SQL method

39
Method 5 The Query

SELECT COLUMN_VALUE "Questions",
COUNT () "Customer count"
FROM TABLE (f ())
GROUP BY COLUMN_VALUE

Oracle Table Functions produce a collection of
rows that can be consumed by a query much like a
table.
40
Method 5 The Pipelined Function

CREATE OR REPLACE FUNCTION f
RETURN SYS.dbms_debug_vc2coll PIPELINED
AS
l_c1 VARCHAR2 (20)
l_c2 PLS_INTEGER
BEGIN
FOR c IN
(SELECT a.,
COUNT () OVER (PARTITION BY
customer_id) cnt,
ROW_NUMBER () OVER (PARTITION BY
customer_id
ORDER BY question_id)
rn
FROM a
ORDER BY customer_id, question_id)
LOOP

41
Method 5 The Pipelined Function continued

LOOP
IF l_c2 c.customer_id OR l_c2 IS NULL THEN
l_c1 l_c1 c.question_id ','
l_c2 c.customer_id
IF c.cnt c.rn THEN
PIPE ROW (RTRIM (l_c1, ','))
l_c1 NULL
l_c2 NULL
END IF
END IF
END LOOP
RETURN
END f

42
Method 5 The Pipelined Function

SELECT COLUMN_VALUE "Questions",
COUNT () "Customer count"
FROM TABLE (f ())
GROUP BY COLUMN_VALUE

Questions IDs Customer Count 1
1 1,2,4
2 3,4 2 2,3
1 2 1
43
So many choices, so little time

If all these queries provide the same results,
which one should be chosen?
Query cost information was added to this post -
yet another topic!
The results
Michaels PIPELINED Function
Rob's MODEL
Nicolas' HIERARCHY 1
Nicolas' HIERARCHY 2
Greg's COLLECT
Warren/devmiral's HIERARCHY
Michaels'/Gregs XML

44
Not done yet! Even More Solutions

XML combined with COLLECT
Bit Pattern
User-defined Aggregate Functions (ODCIAggregate
Interface)
Old-school Oracle 7 solution

45
Wow, All of These Topics in a Single Thread!

Hierarchical Queries
SYS_CONNECT_BY_ROOT
Analytic Functions
COLLECT
CAST
CURSOR

XML Functions
MODEL Clause
Pipelined Functions
Bit Pattern Techniques
User-defined Aggregates
Explain Plans/Query Costs

BUTNever underestimate the value of using
widely-understood and accepted Oracle database
concepts
The experts here used this thread to demonstrate
alternate technologies only and never advocated
these rather esoteric solutions

46
Jumping in the Pool Forum-Decorum

Dont demand urgent help. In fact, dont demand
anything from this all-volunteer community
Search for an answer first
Use the proper code notation tags reformatting
queries is a pain
preThis is code text/pre
Dont flame the Gurusyou will likely get
scorched!
If you use information from the Forums on your
Blog or anywhere else, please reference your
source
Dont be intimidated. If you dont understand an
answer, request clarification
Thank your responders

47
Wrap-Up