昭佑.天翔: Oracle ERP Alert 輸入結果突破 64K 限制

在 Oracle ERP Alert 的 Mail Output 結果, 有 64K 輸出大小的限制,

要突破這個限制, 有以下幾種方式 :

採用 Oracle DB Package 方式

1) 由 Oracle DB Package 根據 Select 語法, 產生 Output 檔案.
2) 由 Oracle DB Package 將 Output 檔案, 發送給相關的人員.

這種方式, 也是目前最常用的方式 : 站長客制的 Graphic Alert.

採用 OS FTP 方式

1) 撰寫一個 FTP 的 OS shell

程式碼

#!/bin/sh
 
# ===============================================
# 接收與定義變數
# Created by : Tom 2006.12.15
# ===============================================
USERNAME=$2
PASSWORD=$3
REMOTE_DIR=$4
LOCAL_DIR=$5
LOCAL_FILE=$6
NEW_LOCAL_FILE=$7
 
# ===============================================
# 判斷資料是否輸入完整
# Created by : Tom 2006.12.15
# ===============================================
if [ "$1" = "" ]; then
  echo "please input ftp address"
  exit
fi
 
if [ "$USERNAME" = "" ]; then
  echo "please input ftp username"
  exit
fi
 
if [ "$PASSWORD" = "" ]; then
  echo "please input ftp password"
  exit
fi
 
if [ "$REMOTE_DIR" = "" ]; then
  echo "please input ftp remote directory"
  exit
fi
 
if [ "$LOCAL_DIR" = "" ]; then
  echo "please input ftp local directory"
  exit
fi
 
if [ "$LOCAL_FILE" = "" ]; then
  echo "please input ftp local file name"
  exit
fi
 
if [ "$NEW_LOCAL_FILE" = "" ]; then
  NEW_LOCAL_FILE=$LOCAL_FILE
fi
 
# ===============================================
# 判斷 Local Directory & File 是否存在
# Created by : Tom 2006.12.19
# ===============================================
if [ ! -e $LOCAL_DIR ]; then
  echo "local directory : $LOCAL_DIR is not exist"
  exit
fi
 
if [ ! -e $LOCAL_DIR/$LOCAL_FILE ]; then
  echo "local files : $LOCAL_FILE is not exist"
  exit
fi
 
# ===============================================
# 開始 ftp 上傳檔案
# Created By : Tom 2006.12.15
# ===============================================
ftp -n << EOF
open $1
user $USERNAME $PASSWORD
prompt off
  cd $REMOTE_DIR
  lcd $LOCAL_DIR
  put $LOCAL_FILE $NEW_LOCAL_FILE
quit
EOF

2) 撰寫一個 SQL File, 裡面包含 "設定各 Column 標題" 與 "spool 輸出檔案" :

3) SQL File 最後, 執行步驟 1 的 FTP Shell, 如: host $TOM_TOP/shell/myFtp.sh &1 &2 &3 &4 &5 &6 &7

4) 建立一個型態為 "SQL*PLUS" 的 Concurrent Program, 用來執行剛才的 SQL File

5) 在 Alert 中的 Action Type 設定為 "Concurrent Program", 用來執行上步驟的 Concurrent Program.

2009/10/13

Oracle ERP Alert 輸入結果突破 64K 限制