分類: 微軟Office

Excel 自動報表遇 ora-12516 錯誤排除

在更新公司的營運報表(Excel直接讀取資料庫)時會出現 ORA-12516 TNS:listener could not find available handler with matching protocol stack 的錯誤，但每一個 SQL 指令去分別執行時都很正常，詢問過資料庫維護廠商後給我們的答案是 Oracle Client Driver 異常，這點我覺得不太可能是，應該是出在我在短時間對幾個不同的 DB 下查詢指令，而這些資料庫都是透過 Oracle DB 做 DBLink 連結到大陸的子公司去，造成極短時間負荷過大 (Processes 及 Sessions 被吃光)，所以調整一下 Excel 的查詢規則

如果也遇到大量查詢時出現上述的錯誤，可以試著把 “啟用幕後執行更改作業” 的勾取消，這樣不免的會導致 Excel 查詢這些SQL指令同時間只會執行一個，但是可以降低系統的負荷就不會產生這個錯誤.

快速變更 Excel 查詢資料庫密碼

最近公司開始做共用帳號密碼的變更作業，先前幫很多同事做了 Excel 自動撈取 Database 的自動更新報表，一但要變更帳號就會變得很痛苦，依照該 Excel 表內用了幾個SQL查詢，就需要變更幾次密碼，坐下來沒有幾個小時做不完。

還好 Excel 還有功能可以直些列出所有使用到的資料連線，其路徑為 資料 -> 連線 就會顯示如下，Double-click 個別的連線就可編輯這個內容。

Excel Data connect list

Excel Data connect list

閱讀全文 快速變更 Excel 查詢資料庫密碼

解決MS Query 執行時出現 「記憶體已用完」 的錯誤

Out of Memory when running MS query32
Out of Memory when running MS query32

最近發現先前做好的 Excel 表格直接讀取 Oracle DB 資料(作法請參考: 由Excel直接撈取Oracle DB的步驟與應用 ), 需要重新編輯的時候常常發現記憶體不足, 剛開始認為是Windows執行久了, 裡面總有些不乾淨去影響到軟體的執行, 花了將近一天重新安裝 Windows 7 (Sp1) 及 Windows XP (SP3) 分別來測試, 依舊得到相同的結果, 確認應該跟環境不相關, 再度利用 Google 來找答案, 這次運氣就很好直接找到對解決方案.

找到的連結 請點選 , 主要是說 MS Query 一開始執行時會主動把所有的 Table, View, 等等東西一口氣全部 Load 進來, 隨著資料庫的成長當然記憶體就被吃光光, 連編輯都不給做, 這時候隨著 Luker 的方式來改變 MS Query 一開始執行的行為來避免掉這個錯誤.

閱讀全文 解決MS Query 執行時出現 「記憶體已用完」 的錯誤

使用Excel欄位當做外部資料的查詢條件

先前嘗試出來的由Excel 直接讀取 Oracle DB 的資料, 都是把查詢的結果直接寫在 SQL 指令內, 在做簡易的 DashBoard 是可以很快的取得所需要的資料, 但是在彈性上會有所限制.

首先還是要先建立一個外部資料來源, 請參考先前一次的說明文件, 我利用 select * from All_tables 來說明其使用方式.

在填寫好 SQL 指令後, 在 檢視 -> 準則請勾選, 之後再準則欄位填入條件的欄位名稱, 值: 的這個部分填入 =[Input Owner], [] 的部份就是讓你輸入變數的地方.

è¨å®šæ¢ä»¶
設定條件

做到以上的步驟當然可以讓你在 Microsoft Query 編輯時可以自帶條件, 然後存檔回 Excel (不存檔也可以啦), 這樣你每次打開都會呈現最後一次的查詢結果, 不過這還不是我們需要的…

不要輸入任何條件後, 回 Excel 之後就可以看到系統會主動問你 Input Owner 的來源, 就可以選擇該查詢條件要抓取的欄位, 先設定成 A1.

選擇來源欄位
選擇來源欄位
指定為A1
指定為A1

這樣 Excel 就會自動的把 A1 的條件帶到剛剛設定的 Query 內.
不輸入任何數值則不回傳任何資料, 因為條件不符

條件空白, 沒有資料傳回
條件空白, 沒有資料傳回

輸入 SYS 後就會自動帶出所有 Owner = ‘SYS’的Table Name

選擇 Owner = 'SYS'
選擇 Owner =

當然如果你的參數需要下到區間的話, 當然也可以填寫兩個以上的準則來處理, 這樣就可以讓你的 Excel 表格上可以很有彈性來顯示必要的資料.

另外有一種方式可以先把所有的資料在 Sheet2 (或其他的工作表)全部撈取出來, 再用 Vlookup 的指令來對照把需要的資料再Sheet1呈現出來.

由Excel直接撈取Oracle DB的步驟與應用

1. 確認 Oracle 服務的名稱及連結參數是否設定完整

2. 進入 Excel 選擇 資料 -> 匯入外部資料 -> 新增資料庫查詢

3. 選擇<新資料來源> ->確定

4. 設定參數

  • 來源名稱: 自取
  • 驅動程式: 選擇 Microsoft ODBC for Oracle
  • 選擇”連接”, 彈出使用者帳號資訊, 這個部分要詢問一下 DBA 正確的帳密資料
  • 伺服器選擇在 Oracle Net Manager 所設定的服務名稱

5. 很有誠意的把資料庫的 Table 全部列給你參考, 但是我們直接在外部利用其他的 SQL Editor 先把 SQL 查好, 所以這邊選擇取消

6. , 我們還要繼續編輯所以選擇 “Y”

7. 接下來看的這個訊息, 因為不把算用Table 拉出來去串資料, 選擇”關閉”

8. 在選單 Icon 上面點選SQL

9. 之後會提示你 SQL 視窗, 把準備好的 SQL 填入, 選擇 “確定”

10. 完成後系統自動帶出關連表格等畫面, 跟 Access 所看到的是類似的

11. 接下來可以選擇存檔(建議啦, 免得辛苦的東西不見了), 之後關閉該視窗, 回到 Excel 之後選擇要寫回 Excel 那一個欄位, 選擇”樞紐分析表”可以達到的功能比較多.

12. 直接就跳到樞紐分析表的第三步驟

13. 把查詢出來的欄位拉到指定的地方去就可以產生不同的效果

14. 這樣就可以造成不同的報表效果, 由不同的分析面來看同一份資料

15. 與日期相關的變數使用方法

  • add_months(Sysdate,-1) 往前一個月
  • SYSDATE+NUMTOYMINTERVAL(-1,’YEAR’) 往前推一年
  • (B.DAT_REQ Between to_Char(Sysdate,’YYYY’)||’01’ And to_char(sysdate,’YYYYMM’)) 今年一月到本月份

Outlook2007及vbscript問題

安裝 Outlook 2007 或是 Office 2007 完畢後, 打開 Outlook 2007 產生一個 VBScript.dll 註冊錯誤的訊息, 經過訊息的提示執行 Regsvr32 vbscript.dll 重新註冊, 再重新啟動 Outlook 2007 還是遇到相同的錯誤。

整個移除 Office 2007 後再重新安裝，還是遇到相同的錯誤訊息，經查”骨狗”大神的協助找到可能是Office 語系不同所產生的問題.

路徑: 開始 -> 程式集 -> Microsoft Office -> Microsoft Office 工具 -> Microsoft office 2007 語言設定

檢查主要編輯語言的設定，改成是電腦的主要語言設定即可。

重新啟動Outlook就恢復正常了