DBMS_XMLGEN.GETXMLTYPE 테이블정의서

DBMS_XMLGEN.GETXMLTYPE 는 동적쿼리 처럼 쓸수 있어서 활용도가 높다.
lob 타입의 경우 가공이 불가한 제약이 있는데 이 함수를 써서 타입을 변경 할 수 있다.

-- 테이블 정의서 gen
SELECT rownum as "순번"
, A.COMMENTS AS "항목명"
, A.COLUMN_NAME AS "항목ID"
, ( SELECT 'PK'
FROM DBA_IND_COLUMNS IC
WHERE IC.TABLE_OWNER = :OWNER
AND IC.TABLE_NAME = A.TABLE_NAME
AND IC.COLUMN_NAME = A.COLUMN_NAME
AND IC.INDEX_NAME LIKE 'PK#_%' ESCAPE '#'
) AS "KEY"
, A.DATA_TYPE AS "TYPE"
, A.DATA_LENGTH AS "길이"
, ' ' AS "항목설명"
,(SELECT '시퀀스 '||'SQ_'||Q.TAB_NM||'_01'
FROM SEQ_INFO Q
WHERE Q.DBNAME = A.OWNER
AND Q.TAB_NM = A.TABLE_NAME
AND Q.COL_NM = A.COLUMN_NAME
) AS "COLUMN RULE"
, ' ' AS "개인신용정보구분"
, DECODE(A.NULLABLE,'N', 'NOT NULL','')||' '||DATA_DEFAULT AS "NULL (DEFAULT)"
FROM (
SELECT T.OWNER,
T.TABLE_NAME,
M.COMMENTS,
C.COLUMN_NAME,
C.DATA_TYPE,
C.DATA_LENGTH,
C.NULLABLE,
CASE WHEN DATA_DEFAULT IS NOT NULL THEN
'('|| REPLACE(DBMS_XMLGEN.GETXMLTYPE('SELECT DATA_DEFAULT FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='
||''''||C.TABLE_NAME||''''||' AND COLUMN_NAME='||''''||C.COLUMN_NAME||'''').EXTRACT('//text()')
,'&'||'apos;','''') || ')' END DATA_DEFAULT,
C.COLUMN_ID
FROM DBA_TABLES T
,DBA_TAB_COLUMNS C
,DBA_COL_COMMENTS M
WHERE 1=1
AND T.TABLE_NAME = :TAB_NM
AND T.TABLE_NAME = C.TABLE_NAME
AND C.TABLE_NAME = M.TABLE_NAME(+)
AND C.COLUMN_NAME = M.COLUMN_NAME(+)
ORDER BY COLUMN_ID
) A



-- 전체 테이블 rows 조회
SELECT OWNER,
TABLE_NAME,
DBMS_XMLGEN.GETXMLTYPE('SELECT COUNT(*) CNT FROM '
||T.OWNER||'.'||TABLE_NAME ).EXTRACT('//text()') CNT
FROM DBA_TABLES T
WHERE TABLE_NAME LIKE 'T%'
AND OWNER ='AA'