Q: Describe the use of PL/SQL tables.

混了一個禮拜... 該是回來面對這系列問題的時候了... 其實我也不是故意要跳過的=P。因為上禮拜的問題是: "What packages (if any) has Oracle provided for use by developers?" .... 這.. 就算我去讀了一些文件，還是認為自己無法具體的回答... 所以就放棄這題了.. (如果我在面試的時候被問到這個，大概也沒時間拖過一個星期XD)

[類別] PL/SQL
[問題] 請描述PL/SQL tables該如何使用

Answer:
先講講揪竟PL/SQL table(又叫index-by tables)是什麼? 這是它在9iR2以前的稱呼，9iR2起稱為"associative arrays”。他是一種:

• collection型別
• 有index
• 可用BINARY INTEGER或VARCHAR2做索引(indexed/associated)

他跟另一個collection型別: PL/SQL nested tables很相似:

• 是一維陣列(one-dimensional arrays)
• 無(大小)限制(unbounded, 理論上...只要記憶體夠大)
• 同質性(homogeneous, 就是array裡的每個element要一樣型別的意思..)

先來看一下宣告語法，先定義一個型別，接著才宣告變數:

TYPE 型別名稱 IS TABLE OF 一已知存在型別 INDEX BY [BINART_INTEGER | VARCHAR2(5)];

變數名稱 型別名稱;

ex:

TYPE position_table IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(10);

who_list position_table;

所以上面我們就定義了一個position_table(使用者定義)data type, 是以字串(string)作index, 然後宣告變數who_list是這個型別。該怎麼使用呢?

who_list('CEO') := ‘Fisher Liang’;

who_list('CDO') := ‘Aileen Wu';

DBMS_OUTPUT.PUT_LINE('The CEO is ' || who_list('CEO'));

這樣~ 'CEO', 'CDO'就是index，而且是unique的，若是重複指定，就是取代舊有值的意思。雖然是個array，用起來卻很像是有index的table對巴~?

PL/SQL Tables/associative arrays只做暫時存放資料用，也不是一個真正的table，並無法對其使用insert或select into等SQL statement。不過可以做到session level的life cycle, 就是把以上宣告及使用放在package裡。

完畢!

Ref:

1. PLSQL Tutorial – PLSQL Collections
2. PLSQL Tutorial – Oracle PLSQL Collection Types
3. PL/SQL User’s Guide and Reference – PL/SQL Collections and Records
4. [Oracle-Base] Associative Array in Oracle9i Release 2

Batch file – Delete files by date, count the output of other commands

介紹個相當好用的指令: Forfiles

用途: 檔案選取，並可針對篩選出來的檔案再下其他指令
注意: WinSvr2003, R2, SP1, PS2才有此指令
常用參數: /m SearchMark: Searches files according to SearchMask.
             /c command: Runs the specified Command on each file.
             /d [{+ | -}] [{MM/DD/YYYY | DD}]

假如某個資料夾下每天都會有一個(以上)檔案產生，並且希望定期清理，只保留一個月份的檔案量時，可以用forfiles:

下面這個指令就是刪除日期為30天以前的檔案

> forfile /D –30 /C “cmd /c del @file”

• /C 這參數預設值是"cmd /c echo @file"，也就是只列出檔名。
• "cmd /c" 是下指令固定的寫法。
• "@file" 是可以用在/C參數內的變數之一，@file表示檔案名稱。其他還有變數可以列出檔案副檔名(@ext)、完整路徑(@path)、相對路徑(@relpath)、判斷是否為目錄(@isdir)、檔案大小(@fsize)、日期(fdate)、時間(@ftime)等。
• /D 這參數用法有很多變化(詳請參考reference)，"-30"的意思就是30天前。

另外一個是For...DO迴圈語法，用來跟上面的forfiles搭配，可以先計算出forfiles總共篩選出幾個檔案:

> set cnt=0
> FOR /F %G IN(‘forfile /D -10’) DO set /a cnt+=1

• 上面即是先以forfile找出10天前的檔案，並丟進去for迴圈內
• DO裡針對每個forfile都把變數cnt加1，所以最後只要echo %cnt%即可得到檔案數目
• for-do的詳細用法也請參考reference

Ref:

1. Forfiles
2. For(loop)

Using binding vareable with “LIKE” condition

這感覺就是一個相當簡單又會很常用到的東西... 卻一時寫不出來OTZ

通常我們用到LIKE時，大多就是想做模糊搜尋吧...

像下面這個SQL是要找名字裡包含大寫SM的人:
SELECT * FROM EMPLOYEE WHERE NAME LIKE ‘%SM%’

像上面這樣寫，就變成傳說中的hard coding，有時候為了效能考量，我們要把值的地方以變數(:variable)取代，所以這兩種組合揪竟該怎麼兜在一起呢!?

這樣… LIKE '%:variable%'          (X, :variable直接被當成字串=   =)
這樣… LIKE ''%:variable%''        (X, :variable終於不被當成字串，還是失敗)
這樣… LIKE '%'||:variable||'%'     (O, 原來要自己組....)

BUG! “Environment variable ORACLE_UNQNAME not defined”

Environment:
DB version: Oracle 11gr2
Platform: Linux RedHat EL5

今天本來是在做"Automating Database Startup and Shutdown on Linux" setp by step。照著文件做，也處理了最後"Known Issues"的部分後，發現.... Oracle em(dbconsole)的服務並沒有起來...

也不是什麼大問題，就是dbstart這支script裡並沒有把他寫進去而已，於是自己把他補在"/etc/init.d/dbora"(依照上面文件做的一個檔案)裡面:

su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole” ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^上面這行

補了一行code之後又重機一次，發現竟然還是一樣... 失敗了=   =，如果以oracle的身分直接去跑"emctl start dbconsole"是可以成功的，但是如果一開始是以root的身分切換到oracle去下指令(就是補上的那一行做的事情)，會被抱怨:

Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

ORACLE_UNQNAME... 還真沒看過這個東西... 結果在oracle文件裡看到這是一個BUG(Bug 1716161)!! 不過他只跟你說，把ORACLE_UNQNAME這個環境變數的值設成db_unique_name這個初始化參數而已... how!?!?

再來看另一篇文章，他也是做一樣的事情，並且在最後一個步驟(6.)說明:
在"/etc/profile"裡補上一段code即可!!

if [ $USER = "oracle" ] ; then
if [ $SHELL = "/bin/ksh" ] ; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
export ORACLE_UNQNAME=db_unique_name
umask 022
fi

真的只是個bug!!!!

Ref:

1. [ORACLE-BASE] Automating Database Startup and Shutdown on Linux
2. [Oracle Database Readme] section38 “Open Bugs”
3. [JOBACLE] Automatic Start/stop in 11g release 2

Q: Describe the use of %ROWTYPE and %TYPE in PL/SQL

[類別] PL/SQL
[問題] 請描述%ROWTYPE跟%TYPE該如何使用

Answer:
%ROWTYPE可以用來宣告一個record跟
(1)資料庫內某個table/view  OR  (2)從cursor fetch出來  的資料列有相同結構。
宣告語法如下:

變數名稱 表格名稱(OR view名稱 OR cursor名稱)%ROWTYPE;

ex: tmpRow employee%ROWTYPE;

如上就會有一個tmpRow結構跟employee(這個表格)裡的列一樣，包括欄位名稱與資料型別。但並不繼承constraints!!

%TYPE可以用來宣告一個data item跟
(1)已宣告的變數  OR  (2)表格裡的某欄位  有相同的資料型別。
宣告語法如下:

變數名稱 表格名稱.欄位名稱%TYPE;

ex: tmpItem employee.salary%TYPE;

以上面的例子，tmpitem是所謂的referencing item，而employee.salary是referenced item。

使用%TYPE一定會繼承資料型別，但不一定會繼承contraints。當referenced item是資料庫表格的欄位時，就不會繼承。

Linux例行性工作排程by crontab

env: Red Hat Enterprise Linux 5

如果要在linux上排定一些類似檔案備份的例行性排程，可以利用crontab這指令。

step1: 準備好要下的指令，通常都會先編一個.sh file, 像這樣:

[say_hello.sh]

#!/bin/bash

echo say hello `date` >> log.txt

    上面這個檔案會印一行say hello跟系統時間在log.txt這個檔案裡
    像這樣: say hello Wed May 12 16:20:01 CST 2010

step2: 使用crontab排程

$ crontab -e

-e是edit的意思。下了這個指令後會進入vi編輯畫面。都未曾使用過crontab的話內容就是一片空白。

step3: 新增工作排程

crontab的內容有固定格式，一個工作一行，大概長像這樣:

20 16 * * * /home/oracle/say_hello.sh

先簡單說一下這條工作內容: 每到16:20執行say_hello.sh
指令的結構是這樣(詳細說明請參閱[鳥哥]基礎文件CH16)

分	時	日	月	周	指令
0-59	0-23	1-31	1-12	0-7	指令=  =

這邊要注意指令裡有用到任何檔案，都要用絕對路徑。
編輯完存檔退出後，會有一行訊息: crontab: installing new crontab
表示排程已經排定。如果要檢視所有排定的工作，可以下:

$ crontab -l

step4: 就等囉~

等到已經排定的時間，你可能會發現.... 失敗了=   =，並且會收到一封mail，由root(Cron Deamon)寄出，內容最後有一行類似這樣的訊息:

/bin/sh: /home/oracle/say_hello.sh: Permission denied

permission denied… 也不知道在deny什麼鬼，因為明明可以在自己的環境裡直接下"sh /home/oracle/say_hello.sh"執行這個script。一直以為是路徑的問題(阿不就是寫完整路徑!?)，改來改去就是不行。

最後發現竟然真的是權限問題，下"ls -l"可以看到新建的script檔權限長這樣:
－ｒｗ－ｒ－－ｒ－－
owner自己竟然沒權限執行.... orz||，chmod一下

$ chmod u+x /home/oracle/say_hello.sh

確認一下執行權限－ｒｗｘｒ－－ｒ－－
再重新安排一次時間比較近的排程試試看，應該就OK囉。

linux排程初體驗，成功!!!

Ref:

1. [鳥哥] 第十六章、例行性工作排程 (crontab)
2. Disable The Mail Alert By Crontab Command

My favorite chrome extensions

沒看到google提供收藏最愛extensions的功能.. 只好自己做個記錄順便分享一下。

1. SearchPreview for Google: 可以替google搜尋結果加上網站預覽的小圖
2. Auto Replay for YouTube: 提供youtube自動replay功能
3. Tab Menu: 條列所有開啟的tab
4. IE Tab: 以ie核心開啟網頁(沒辦法~ 有些網站就是不得不)
5. Google 文件 PDF/PowerPoint 檢視器 (由 Google 提供): 以google文件檢視器直接開啟PDF、PPT等文件

本來就很容易分心，所以沒有裝更多其他有的沒的XD。另外列一些個人感興趣還沒使用的:

1. MegaUpload DownloadHelper: (另有RapidShare的)免倒數~ cool
2. Turn Off the Lights: 觀看有影片的網頁時，可以把背景都變黑，讓你有置身於電影院內的感覺XD
3. Robot Theme, inspired by Android™: 只是一個android佈景主題，好似很可愛
4. Clickable Links: 將原本不是做成超連結的url或e-mail add變成clickable links
5. Google Tasks: 相當簡潔的待做清單囉(應該是只有local啦)
6. 迅雷、快车、旋风专用链自动破解: 不想解釋=P
7. JoinTabs: 這個好!!! 在開了許多tab之後，可以把相關的另外拉成一個chrome window。不知道好不好用~
8. Eye Dropper: 網頁選色工具，也有color picker

Q: What is a mutating table error and how can you get around it?

相當慚愧的... 在看到這題以前，我也完全沒聽過mutating tabel這東西OTZ

[類別] PL/SQL
[問題] 什麼是mutating table error? 該如何處理這類錯誤?

Answer:
在回答什麼是mutating table error前，先來說說什麼是"mutating error"。當一個表格正在被UPDATE, DELETE或INSERT語法修改(也就是這個statement尚未commit, 表格正在"變更中")，或是一個table有可能是DELETE CASCADE的對象時，就是所謂的mutsting table。

只有row-level trigger(定義時使用FOR EACH ROW子句)嘗試要查詢或更新一個mutating table時，才會發生mutating table error! 主要是為了避免trigger存取不一致的資料。這時候trigger本身還有觸發trigger的語法(triggering statement)都會被rollback。

中文講的好爛請參閱英文講解 orz

A mutating table error (ORA-4091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).

Mutating table error的中文訊息長這樣:

ORA-04091: 表格 Schema.Table 正在變更中, 觸發程式/函數無法檢視它

一般避免mutating table error最常見的方法是:

使用compound triggers或temporary tables(or views)，讓同一session update/select不同表格

喋喋不休版
舉個會發生mutating table error的實例: 假如hr.employee的表格上有定義一個row-level的trigger: 每次有row被刪除時，會計算employee的總筆數。所以當你對hr.employee下一個SQL去刪除某列資料的同時(此時employee就是一個mutating table)，也會觸發該trigger，這個trigger嘗試要存取employee，以上條件(mutating table, row-level trigger)，即會發生mutating table error。

(It WORKS!)“could not create directory for unzipping” when installing chrome extensions

繼上篇只找到一個暫時有用的方法之後，過了不久，chrome越來越破爛，只好循重新安裝一途，沒想到重安裝之後，連想換個不景主題都會遇到一樣的問題... 真是晴天霹靂，完全不可接受，只好又花了點時間找找別的方法。

看到好多篇說要修改TEMP/TMP環境變數的，個人雖然相當不願意做這件事情，不過試了發現對我還是無效，最後找到一個相當簡易的方法!!! 只要在開啟chrome的捷徑內容幫他加個參數就好了:

沒錯，就是" --no-sandbox" 記得參數前要有一個空白。

重新啟動chrome之後就可以正常安裝主題還有擴充功能囉喔喔喔~ 不過因為個人對這個參數的意義相當不解，所以建議安裝完之後還是把這個參數拿掉吧... 有需要的時候再加回來使用。

Ref: [SECI 知識轉換] [GC] 暫時解決無法安裝擴充功能問題

(補) sandbox

Chrome 擴展也有使用 Sandbox 技術，即每個擴展是獨立運行的。在安裝一些擴展後你可發現 task manager 內會有較多的 chrome.exe 在運行。獨立運行的好處，是一個擴展死掉不會拖累另一個。
from: http://www.hksilicon.com/kb/articles/3764/1/Chrome-Extension-/Page1.html

設定PowerBuilder連接oracle資料庫之.ini file

environment

PB version: PB11 
Local computer: Oracle 10g/11g client

Pb application原本是使用ODBC來與資料庫連線:

[DataBase]
DBMS=ODBC

DbParm=ConnectString='DSN=test;UID=user_id;PWD=user_pwd'

不知道什麼原因，有少數datawindow的效率相當不好，因此想要將連線改為PB提供的oracle driver(版本對照請參考這兒)。步驟如下:

Step1: 開啟PB > Tools > Database Profile…

 

Step2: 選擇PB所提供最新的oracle driver(O10 Oracle10g)，按右鍵選擇”New Profile…”

 

Step3: 設定新連線內容

    Profile Name: 新連線名字
    Server: net manager裡設定的"服務命名"
    Login ID / Password: 資料庫user帳號密碼

    PowerBuilder Catalog Table Owner: 就是oracle的schema owner

    Enclose Table and Column Names in Quotes: 這個選項如果勾起，SQL語法的表格名稱還有欄位會自動被放在雙引號裡，這有時候會造成一些問題(像ORA-00904)，可以視需求勾選。

Step4: Preview and test

點 "Test Connection"看設定是否正確，沒問題就可以Apply並且OK。

Step5: 最後把設定檔export成*.ini檔

在剛設定好的連線按右鍵 > Export Profile(s)... 將檔案存到目的資料夾內

Step6: 檢視匯出的內容揪竟長怎樣

[DBMS_PROFILES]
Profiles=test

 

[Profile test]
DBMS=O10 Oracle10g (10.1.0)
Database=
UserId=
DatabasePassword=
LogId=user_id

LogPassword=user_pwd
ServerName=test
DBParm=PBCatalogOwner='Owner_name',DelimitIdentifier='No'
Lock=
Prompt=FALSE
AutoCommit=FALSE
NewLogic=TRUE

前3行要刪掉，改放[DataBase]，其他設定就隨自己需求增減，最後整個ini file大概會長這樣:

[DataBase]

DBMS=O10 Oracle10g (10.1.0)
LogId=user_id
LogPassword=user_pwd
ServerName=test
DBParm=PBCatalogOwner='Owner_name',DelimitIdentifier='No'

完工!