Re: challenge: v7 DBA script --> 8i, 9i, 10g

Mark C. Stock wrote:

> ok, smarties...
>
> here's a v7 style DBA script that flattens the columns list of
> USER_CONS_COLUMNS into comma separated list (part of a set of scripts to
> check if FK constraints are indexed)
>
> sample output:
>
> TABLE_NAME C CONSTRAINT_NAME COLUMNS
> ------------ - --------------- --------------------
> INV P SYS_C0016017 ID
> INV_ITM P INV_ITM_PK INV_ITM, ITM_SEQ
> INV_ITM R SYS_C0016020 INV_ITM
>
> how would you improve it for
> [_] 8i?
> [_] 9i?
> [_] 10g?
> [_] 11x?
>
> -- mcs
>
> SELECT table_name
> ,constraint_type
> ,constraint_name
> , col1
> || DECODE(col2, NULL, NULL, ', ')
> || col2
> || DECODE(col3, NULL, NULL, ', ')
> || col3
> || DECODE(col4, NULL, NULL, ', ')
> || col4
> || DECODE(col5, NULL, NULL, ', ')
> || col5 AS columns
> FROM (SELECT table_name
> ,constraint_type
> ,constraint_name
> ,MIN(DECODE(position, 1, column_name)) col1
> ,MIN(DECODE(position, 2, column_name)) col2
> ,MIN(DECODE(position, 3, column_name)) col3
> ,MIN(DECODE(position, 4, column_name)) col4
> ,MIN(DECODE(position, 5, column_name)) col5
> FROM (SELECT ucc.table_name
> ,uc.constraint_type
> ,ucc.constraint_name
> ,ucc.position
> ,ucc.column_name
> FROM user_cons_columns ucc
> ,user_constraints uc
> WHERE uc.constraint_type IN ('P', 'R')
> AND uc.constraint_name = ucc.constraint_name
> AND uc.table_name = ucc.table_name
> AND uc.owner = ucc.owner)
> GROUP BY table_name
> ,constraint_type
> ,constraint_name)
> ORDER BY table_name
> ,constraint_type
> ,constraint_name

I'm going to present this to my students tonight at class.

But in version 11x the syntax is:

SELECT whatiwant
FROM whereeveritis

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)