스크립트를 PL/SQL Loop 문으로 수행하고 싶습니다 알려주세요 ㅠㅠ 1 11 235

by 오라오라 [PL/SQL] [2018.06.07 14:00:09]


아래 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]

정말 감사합니다 !!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입