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