⑤ DB문과 ALTER SESSION

    PL/SQL 블록 내에서는 DDL문을 실행할 수 없다. 간단한 프로시저 한 개를 만들어 보자.

    입력

        CREATE OR REPLACE PROCEDURE ch13_ddl_proc ( pd_arg1 IN DATE )
        IS
        BEGIN
          DBMS_OUTPUT.PUT_LINE('pd_arg1 : ' || pd_arg1);
        END;
    

    결과

        PROCEDURE CH13_DDL_PROC이(가) 컴파일되었습니다.
    

    이제 이 프로시저 내에서 테이블을 생성하는 DDL문을 추가한 뒤 컴파일해 보자.

    입력

        CREATE OR REPLACE PROCEDURE ch13_ddl_proc ( pd_arg1 IN DATE )
        IS
        BEGIN
          CREATE TABLE ch13_ddl_tab ( col1 VARCHAR2(30));
          DBMS_OUTPUT.PUT_LINE('pd_arg1 : ' || pd_arg1);
        END;
    

    결과

        PROCEDURE CH13_DDL_PROC이(가) 컴파일되었습니다.
        Errors: check compiler log
    

    컴파일 오류가 발생했다. 원인은 CREATE TABLE … 로 시작하는 DDL문을 사용했기 때문이다. 하지만 동적 SQL을 사용하면 오류 없이 성공적으로 DDL문을 실행할 수 있다.

    입력

        CREATE OR REPLACE PROCEDURE ch13_ddl_proc ( pd_arg1 IN DATE )
        IS
          vs_sql VARCHAR2(1000);
        BEGIN
          -- DDL문을 동적SQL로 ...
          vs_sql := 'CREATE TABLE ch13_ddl_tab ( col1 VARCHAR2(30) )' ;
          EXECUTE IMMEDIATE vs_sql;
    
          DBMS_OUTPUT.PUT_LINE('pd_arg1 : ' || pd_arg1);
        END;
    

    결과

        PROCEDURE CH13_DDL_PROC이(가) 컴파일되었습니다.
    

    이제 프로시저를 실행하고 테이블이 생성됐는지 확인해 보자.

    입력

        EXEC ch13_ddl_proc ( SYSDATE );
    

    결과

        ORA-01031: 권한이 불충분합니다
    

    권한이 불충분하다는 오류가 발생했다. 프로시저 내에서 테이블을 생성하려는데 권한이 불충분한 것이다. 이럴 때 SYS나 SYSTEM 사용자로 로그인해서 “CREATE ANY TABLE” 권한을 추가로 부여하도록 하자.

    그림 13-1 CREATE ANY TABLE 권한 부여

    이제 다시 ch13_ddl_proc 프로시저를 호출해 보자.

    입력

        EXEC ch13_ddl_proc ( SYSDATE );
    

    결과

        pd_arg1 : 2015-03-12 18:56:07
    

    ch13_ddl_tab 테이블이 실제로 생성되었는지 확인해 보자.

    입력

        EXEC ch13_ddl_tab ;
    

    결과

        이름     널 유형
        -------- ------------
        COL1     NUMBER
    

    성공적으로 테이블이 생성됐음을 알 수 있다. 사실 프로시저 내에서 테이블을 생성하는 경우는 거의 없고 주로 사용하는 DDL문은 “TRUNCATE TABLE” 문 정도이다.

    DDL문 외에도 ALTER SESSION 명령어를 실행해 가끔씩 세션 파라미터 정보를 변경해야 할 때가 있는데, 이 때도 해당 명령어를 직접 기술하면 오류가 발생한다. 이때도 동적 SQL을 사용하면 원하는 바를 이룰 수 있다. 날짜 형식 포맷인 NLS_DATE_FORMAT 파라미터 값을 변경해 볼텐데, 먼저 현재 설정된 값이 무엇인지 알아 보자.

    입력

        SELECT SYSDATE
          FROM DUAL;
    

    결과

        SYSDATE
        ----------
        2015-03-12
    

    이제 NLS_DATE_FORMAT 파라미터 값을 변경한 뒤 날짜 형식 데이터를 출력할 텐데, 성공한다면 설정한 형식대로 데이터가 출력될 것이다.

    입력

        CREATE OR REPLACE PROCEDURE ch13_ddl_proc ( pd_arg1 IN DATE )
        IS
          vs_sql VARCHAR2(1000);
        BEGIN
          -- ALTER SESSION
          vs_sql := 'ALTER SESSION SET NLS_DATE_FORMAT = "YYYY/MM/DD"';
          EXECUTE IMMEDIATE vs_sql;
          DBMS_OUTPUT.PUT_LINE('pd_arg1 : ' || pd_arg1);
        END;
    

    결과

        PROCEDURE CH13_DDL_PROC이(가) 컴파일되었습니다.
    

    입력

        EXEC ch13_ddl_proc ( SYSDATE );
    

    결과

        pd_arg1 : 2015/03/12
    

    위 명령어의 원래 형태는 ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY/MM/DD’인데, 동적 SQL로 만들기 위해 명령어 구문을 문자열로 만들고 있으므로, 설정 값인 YYYY/MM/DD 부분을 큰 따옴표로 감싸고 있다는 점에 유의하자. 결과를 보면 성공적으로 세션 파라미터가 변경됐음을 알 수 있다.

    신간 소식 구독하기
    뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.