/* SQL 생성 스크립트 실행 파일 (execute_test) */
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET FLUSH OFF
SET VERIFY OFF
SET TERM OFF
SET HEAD OFF
SPOOL 파일명.sql;
@SQL생성스크립트 파라미터1 ...
SPOOL OFF;
...
/* SQL 생성 스크립트 */
WITH T1 AS
( SELECT '&1' OW, '&2' TN FROM DUAL)
SELECT NVL2(LAG(OWNER) OVER (ORDER BY COLUMN_ID),' , ','INSERT INTO '||TABLE_NAME||' ( '||CHR(10))
|| TC.COLUMN_NAME
|| NVL((SELECT ' -- PK' FROM ALL_IND_COLUMNS IC
WHERE IC.INDEX_NAME LIKE 'PK%'
AND IC.table_owner = tc.owner
AND IC.TABLE_NAME = tc.table_name
AND ic.column_name = TC.column_name
AND ROWNUM <= 1 ),'')
|| NVL2(LEAD(OWNER) OVER (ORDER BY COLUMN_ID),'',')')
FROM ALL_TAB_COLUMNS TC, T1
WHERE TC.OWNER = T1.O1
AND TC.TABLE_NAME = T1.TN
UNION ALL
SELECT NVL2(LAG(OWNER) OVER (ORDER BY COLUMN_ID),' , ','SELECT ')
|| NVL((SELECT REPLACE(TC.TABLE_NAME,'TB_','SQ_')||'_EAI_SEQNO.NEXTVAL -- PK : '
FROM ALL_IND_COLUMNS IC
WHERE IC.INDEX_NAME LIKE 'PK%'
AND IC.table_owner = tc.owner
AND IC.TABLE_NAME = tc.table_name
AND ic.column_name = TC.column_name
AND ROWNUM <= 1 ),'')
|| TC.COLUMN_NAME
|| NVL2(LEAD(OWNER) OVER (ORDER BY COLUMN_ID),'',CHR(10)||'FROM '||SUBSTR(TABLE_NAME,1,LENGTH(TABLE_NAME) - 5)||' WHERE ROWNUM <= 100;'
||CHR(10)||''
||CHR(10)||'COMMIT;')
FROM ALL_TAB_COLUMNS TC, T1
WHERE TC.OWNER = T1.O1
AND TC.TABLE_NAME = T1.TN;