SELECT *
FROM
(SELECT DECODE(recdescr,' ',''''
||recname
||'''',recdescr),
recname ,
rcount,
'Select '''
||DECODE(recdescr,' ',recname,recdescr)
||''''
||DECODE(rcount,4,','' ''')
||REPLACE(rtrim(MAX(SYS_CONNECT_BY_PATH(fieldname,'^')),'^'),'^',',')
||' from '
||recname
||' where LASTUPDOPRID <> ''PPLSOFT'' union '
FROM
(SELECT a.recname,
b.recdescr,
DECODE(a.fieldname,'DESCRLONG','DBMS_LOB.SUBSTR('
||a.fieldname
||', 4000, 1)',a.fieldname) AS fieldname,
a.fieldnum ,
rank() over(partition BY a.recname order by DECODE(SUBSTR(fieldname,1,1),'L',fieldname,DECODE(fieldnum,1,'0','1')
||fieldname),fieldnum) AS rn ,
COUNT(1) over(partition BY a.recname) AS rcount
FROM psrecfield a,
psrecdefn b
WHERE a.recname LIKE 'PS%'
AND ((fieldname LIKE '%DESCR%'
OR fieldname IN ('LASTUPDDTTM','LASTUPDOPRID' )
OR (fieldnum =1))
AND a.recname =b.recname
AND a.recname IN
(SELECT recname
FROM psrecfield
WHERE fieldname IN ('LASTUPDDTTM')
)
AND a.recname IN
(SELECT recname
FROM psrecfield
WHERE fieldname IN ('LASTUPDOPRID')
)
AND a.recname IN
(SELECT recname FROM psrecfield WHERE fieldname LIKE '%DESCR%'
))
AND rectype=0
ORDER BY 1,3
)
START WITH rn =1
CONNECT BY prior rn=rn-1
AND prior recname = recname
GROUP BY recname,
recdescr,
rcount
ORDER BY 2
)
WHERE rcount BETWEEN 4 AND 5
AND recname IN
(SELECT table_name FROM all_tables
);