Oracle SQL Programming Tips

Execute SQL Script File Encoded in UTF-8 (Windows)

Step1. 查看DB編碼

SQL>SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = ‘NLS_CHARACTERSET’

Step2. 查看Client端編碼

SQL>HOST ECHO %NLS_LANG%

Step3. 設定Client端編碼 (Only for the current session)

C:\set NLS_LANG=.AL32UTF8

Step4. 執行Script File

C:\sqlplus user/pwd@ip:port/SID @[Script File Name] > output.log

※Script File is encoded in UTF-8 without BOM

※如果Client端的編碼不正確,可能會出現ORA-06550、PLS-00114、ORA-00933等錯誤

Programming Layout with Spool Log File

SET PAGESIZE 0
SET FEEDBACK OFF
SET HEAD ON
SET TRIMOUT OFF
SET TRIMSPOOL OFF
SET LINESIZE 200
SET ECHO OFF
SET SERVEROUTPUT ON FORMAT WRAPPED

COLUMN TM NEW_VALUE FILE_TIME NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') TM FROM DUAL;
SPOOL C:\TEMP\LOG_&FILE_TIME..log

DECLARE
   ......
BEGIN
   DBMS_OUTPUT.PUT_LINE('******BEGIN PROCESS******');
   ......
   DBMS_OUTPUT.PUT_LINE('******END PROCESS******');
END;
/
SPOOL OFF
EXIT

Using UTL_FILE Package

Step1. 授權使用UTL_FILE package

(SYSDBA)

SQL>GRANT EXECUTE ON UTL_FILE TO PUBLIC;

(PUBLIC for every user; 也可指定特定User)

* 沒有授權會出現PLS-00201: 必須宣告識別項 ‘UTL_FILE’的Exception

Step2.  建立Directory物件 (Server Site)

SQL>CREATE OR REPLACE DIRECTORY LOG_DIR AS ‘C:\logs’;

* 沒有設定Directory物件會出現ORA-29280: 目前路徑無效的Exception

Step3. 撰寫Script

SET PAGESIZE 0
SET FEEDBACK OFF
SET HEAD ON
SET TRIMOUT OFF
SET TRIMSPOOL OFF
SET LINESIZE 200
SET ECHO OFF
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
  V_LOG_FILE UTL_FILE.FILE_TYPE;
  V_FILE_NAME VARCHAR2(100);
BEGIN
  V_FILE_NAME := 'LOG-' || 
        TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') || '.log';
  -- Append File, LOG_DIR為前面設定的Directory Name
  V_LOG_FILE := UTL_FILE.FOPEN('LOG_DIR',V_FILE_NAME,'A');

  UTL_FILE.PUT_LINE(V_LOG_FILE,'******BEGIN PROCESS******');
  ......
  UTL_FILE.PUT_LINE(V_LOG_FILE,'******END PROCESS******');

  UTL_FILE.FCLOSE(V_LOG_FILE);
END;
/
SPOOL OFF
EXIT

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s