데이터베이스 질문과 답변
아래 SQL의 결과를 PL/SQL의 Loop문으로 실행하여 건건히 수행하고 싶은데..
어떻게 PL/SQL을 작성하면 될까요 ??
SELECT 'drop view ' || owner || '.' || view_name || ' ;' AS view_drop_script -- ' cascade constraints'
FROM dba_views
WHERE 1=1
AND owner NOT IN ( 'SYSTEM' , 'IMADVISOR' , 'WMSYS' , 'TEST_STS' , 'DBSFWUSER' , 'XDB' ,
'GSMADMIN_INTERNAL' , 'MON' , 'MONITOR' ,
'ORANGE' , 'DPI' , 'PUBLIC' , 'AUDSYS' ,
'APPQOSSYS' , 'SYS' , 'EMCDSA' ,
'ORACLE_OCM' , 'WMSYS' , 'MAXGAUGE' ,
'OUTLN' , 'DBSNMP' )
by 아발란체
[2018.06.07 14:07:36]
샘플 PROCEDURE
CREATE OR REPLACE PROCEDURE TEST_SELECT IS
BEGIN
DBMS_OUTPUT.ENABLE;
FOR TEST_LIST IN(
SELECT 1 AS no FROM DUAL
UNION ALL SELECT 2 FROM DUAL
)
LOOP
DBMS_OUTPUT.PUT_LINE(TEST_LIST.no);
END LOOP;
END;
실행
SET SERVEROUTPUT ON; EXECUTE TEST_SELECT();
참고 강좌
http://www.gurubee.net/lecture/1047
by 아발란체
[2018.06.07 14:09:38]
올려주신 SQL를 사용하면 아래와 같습니다.
CREATE OR REPLACE PROCEDURE TEST_SELECT IS
BEGIN
DBMS_OUTPUT.ENABLE;
FOR TEST_LIST IN(
SELECT
'drop view ' || owner || '.' || view_name || ' ;' AS view_drop_script
FROM
dba_views
WHERE
1=1
AND owner NOT IN (
'SYSTEM' , 'IMADVISOR' , 'WMSYS' , 'TEST_STS' , 'DBSFWUSER' , 'XDB',
'GSMADMIN_INTERNAL' , 'MON' , 'MONITOR' ,
'ORANGE' , 'DPI' , 'PUBLIC' , 'AUDSYS' ,
'APPQOSSYS' , 'SYS' , 'EMCDSA' ,
'ORACLE_OCM' , 'WMSYS' , 'MAXGAUGE' ,
'OUTLN' , 'DBSNMP'
)
)
LOOP
DBMS_OUTPUT.PUT_LINE(TEST_LIST.view_drop_script);
END LOOP;
END;
by 마농
[2018.06.07 14:12:15]
1. EXECUTE IMMEDIATE 를 이용하면 됩니다.
2. 단, 쿼리문에 ';' 은 없어야 합니다.
3. owner 이나 view_name 이 소문자나 특수문자가 포함될 수 있으니 쌍따옴표를 붙여주면 좋을 듯 하네요.
DECLARE
CURSOR cur_views IS
SELECT 'DROP VIEW "' || owner || '"."' || view_name || '"' AS view_drop_script
FROM dba_views
WHERE owner NOT IN ( 'SYSTEM', 'IMADVISOR', 'WMSYS', 'TEST_STS', 'DBSFWUSER', 'XDB'
, 'GSMADMIN_INTERNAL', 'MON', 'MONITOR'
, 'ORANGE', 'DPI', 'PUBLIC', 'AUDSYS'
, 'APPQOSSYS', 'SYS', 'EMCDSA'
, 'ORACLE_OCM', 'WMSYS', 'MAXGAUGE'
, 'OUTLN', 'DBSNMP'
)
;
BEGIN
FOR c IN cur_views
LOOP
EXECUTE IMMEDIATE(c.view_drop_script);
END LOOP;
END;
/
by 오라오라
[2018.06.07 14:36:07]
감사합니다 ^^
1가지만 더 여쭤보고 싶습니다
아래와 같이 LOB 타입의 컬럼도 SELECT 문만 바꿔서 하면 될까요 ? (dbms_metadata.get_ddl 결과 컬럼 타입이 CLOB 인걸로 알고 있습니다.)
SELECT 'create or replace view ' || 'TEST' || '.' ||
SUBSTR( dbms_metadata.get_ddl( 'VIEW' , t.view_name , t.owner ) ,( INSTR( dbms_metadata.get_ddl( 'VIEW' , t.view_name , t.owner ) , '.' ) ) + 1 , LENGTH( dbms_metadata.get_ddl( 'VIEW' , t.view_name , t.owner ) ) ) AS view_create_script
FROM dba_views t
WHERE 1=1
AND t.owner NOT IN ( 'SYSTEM' , 'IMADVISOR' , 'WMSYS' , 'TEST_STS' , 'DBSFWUSER' , 'XDB' ,
'GSMADMIN_INTERNAL' , 'MON' , 'MONITOR' ,
'ORANGE' , 'DPI' , 'PUBLIC' , 'AUDSYS' ,
'APPQOSSYS' , 'SYS' , 'EMCDSA' ,
'ORACLE_OCM' , 'WMSYS' , 'MAXGAUGE' ,
'OUTLN' , 'DBSNMP' )
by 아발란체
[2018.06.07 14:44:37]
LOB조회 문법에 스트링 연산으로 도출된 결과를
EXECUTE_IMMEDIATE하는 것이기 때문에 잘 될 것 같습니다.
by 오라오라
[2018.06.07 14:57:38]
LOB 타입도 잘 되는거 확인했습니다
거듭 감사합니다 ^^
by 오라오라
[2018.06.07 17:14:43]
죄송하지만 한가지만 더 여쭈겠습니다 ^^;
알려주신 방법대로 스크립트 생성 후 수행해보니 중간에 오류가 나면 전체가 수행이 안되는거 같습니다 ;;
알아보니 EXCEPTION 처리를 하면 되는거 같은데... 어떻게 사용해야될지 잘 모르겠습니다 ;;
궁금한점은
1. 오류가 발생하는 건은 어떻게 따로 추출해놓는지 ?
2. 중간에 오류가 나더라도 전체를 한번씩 수행할 수 있는 방법 ?
에 대해 알고 싶습니다. 사용한 스크립트는 아래와 같습니다.
DECLARE
CURSOR cur_views IS
SELECT 'create or replace view "' || 'TEST' || '".' ||
SUBSTR( dbms_metadata.get_ddl( 'VIEW' , t.view_name , t.owner ) ,( INSTR( dbms_metadata.get_ddl( 'VIEW' , t.view_name , t.owner ) , '.' ) ) + 1 , LENGTH( dbms_metadata.get_ddl( 'VIEW' , t.view_name , t.owner ) ) ) AS view_create_script
FROM dba_views t
WHERE 1=1
AND t.owner NOT IN ( 'SYSTEM' , 'IMADVISOR' , 'WMSYS' , 'TEST_STS' , 'DBSFWUSER' , 'XDB' ,
'GSMADMIN_INTERNAL' , 'MON' , 'MONITOR' ,
'ORANGE' , 'DPI' , 'PUBLIC' , 'AUDSYS' ,
'APPQOSSYS' , 'SYS' , 'EMCDSA' ,
'ORACLE_OCM' , 'WMSYS' , 'MAXGAUGE' ,
'OUTLN' , 'DBSNMP' )
;
BEGIN
FOR c IN cur_views
LOOP
EXECUTE IMMEDIATE(c.view_create_script);
END LOOP;
END;
/
by 아발란체
[2018.06.07 14:16:25]
아.. 잘못 이해를 했네요... SELECT절로 만든 쿼리 실행이군요.. OTL
by 오라오라
[2018.06.07 14:36:51]
아닙니다 ^^
친절한 답글 감사드립니다. 저 방법도 요긴하게 쓸때가 있을거 같습니다 ^^
by 마농
[2018.06.07 18:08:01]
DECLARE
CURSOR cur_views IS
SELECT owner
, view_name
, REPLACE( dbms_metadata.get_ddl('VIEW', view_name, owner)
, 'VIEW "' || owner || '".'
, 'VIEW "' || 'TEST' || '".'
) AS view_create_script
FROM dba_views
WHERE owner NOT IN ( 'SYSTEM', 'IMADVISOR', 'WMSYS', 'TEST_STS', 'DBSFWUSER', 'XDB'
, 'GSMADMIN_INTERNAL', 'MON', 'MONITOR'
, 'ORANGE', 'DPI', 'PUBLIC', 'AUDSYS'
, 'APPQOSSYS', 'SYS', 'EMCDSA'
, 'ORACLE_OCM', 'WMSYS', 'MAXGAUGE'
, 'OUTLN', 'DBSNMP'
)
;
BEGIN
FOR c IN cur_views
LOOP
BEGIN
EXECUTE IMMEDIATE(c.view_create_script);
dbms_output.put_line(c.owner || c.view_name || '(Success)');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(c.owner||'.'||c.view_name||'('||SQLCODE||')'||SQLERRM);
END;
END LOOP;
END;
/
by 오라오라
[2018.06.08 09:26:25]
정말 감사합니다 !!
) 버튼을 클릭하여 작성 하시면 됩니다.