⑤ 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” 권한을 추가로 부여하도록 하자.

이제 다시 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 부분을 큰 따옴표로 감싸고 있다는 점에 유의하자. 결과를 보면 성공적으로 세션 파라미터가 변경됐음을 알 수 있다.