tag:blogger.com,1999:blog-59465307047421309702024-03-06T16:20:07.273+08:00Jax 的工作紀錄除了在整理學習上的經驗,同時也能幫助其他需要的人Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-5946530704742130970.post-7912197072555984552012-03-07T14:06:00.002+08:002013-06-10T22:40:22.436+08:00[Ubuntu] 安裝 Oracle Client 與 PDO_OCI於 <a target="_blank" href="http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html">Oracle Database Instant Client</a> 下載 Client/SDK <br />
(Version 10.2.0.4 Instant Client Package - Basic, Instant Client Package - SDK)<br />
<strong>oracle-instantclient-basic-10.2.0.4-1.i386.rpm</strong><br />
<strong>oracle-instantclient-devel-10.2.0.4-1.i386.rpm</strong><br />
<br />
<pre class="sh" name="code"># 安裝套件轉換器
apt-get install alien
# 轉換 rpm 套件到 deb,並安裝
alien -i oracle-instantclient-basic*.rpm
alien -i oracle-instantclient-devel*.rpm
# 安裝 Apache2,PHP,MySQL
apt-get install apache2 php5 mysql-server php5-mysql libapache2-mod-php5
# 安裝 PEAR
apt-get install php-pear php5-dev dh-make-php make re2c
# 下載 PDO_OCI 原始檔
pecl download pdo
pecl download pdo_oci
tar zxvf PDO-1.0.3.tgz
tar zxvf PDO_OCI-1.0.tgz
mkdir -p PDO_OCI-1.0/include/php/ext/
mv PDO-1.0.3 PDO_OCI-1.0/include/php/ext/pdo
cd PDO_OCI-1.0/
phpize
./configure --with-pdo-oci=instantclient,/usr,10.2.0.4
make -j$(grep processor /proc/cpuinfo |wc -l)
make install # /usr/lib/php5/20xxxxxx+lfs/pdo_oci.so
vim /etc/php5/conf.d/pdo_oci.ini # 建立 pdo_oci.ini, 內容如下:
extension=pdo_oci.so
vim /etc/apache2/envvars # 在最後面加入環境變數, 內容如下:
export NLS_LANG="TRADITIONAL CHINESE_TAIWAN.UTF8"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
# 重新啟動 Apache
service apache2 restart
</pre><br />
參考來源:<br />
<a target="_blank" href="http://blog.longwin.com.tw/2008/06/debian_php_oracle_ext_pdo_oci_2008/">Debian 安裝設定 PHP 連 Oracle extension 使用 PDO(PDO_OCI)</a><br />
<a target="_blank" href="https://help.ubuntu.com/community/Oracle%20Instant%20Client">Oracle Instant Client</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-28242200963367864992011-04-19T09:54:00.000+08:002012-02-12T23:32:39.084+08:00[Oracle] PDO-OCI 的 DSN 連接字串最近重裝我的開發環境,結果連 oracle 一直連不上,出現了以下幾種訊息,最後是因為我的 TNS listener 沒設好,或是我的 DSN 錯誤,整理出我試過的幾個 DSN 連接字串:<br />
<br />
ORA-12154: TNS:could not resolve service name<br />
ORA-06401: NETCMN: invalid driver designator<br />
ORA-12514: TNS:listener could not resolve SERVICE_NAME<br />
ORA-12505: TNS:listener could not resolve SID given in connect descriptor<br />
<br />
<pre class="php" name="code">$DSN="oci:dbname=jaxdb;charset=utf-8";
$DSN="oci:dbname=127.0.0.1:1521/jaxdb;charset=utf-8";
$DSN = "oci:dbname=(
DESCRIPTION = (
ADDRESS_LIST = (
ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)
)
)(CONNECT_DATA=
(SID = jaxdb)
)
);charset=utf8";
$DSN = "oci:dbname=(
DESCRIPTION = (
ADDRESS_LIST = (
ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)
)
)(CONNECT_DATA=
(SERVICE_NAME = jaxdb)
)
);charset=utf8";
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-21184720711783309322011-01-19T16:38:00.000+08:002012-02-12T23:32:39.086+08:00[Regedit] Oracle 日期格式及語言編碼設定上次用到時沒有記下來,結果忘記了,這次先把他記下來。<br />
<br />
<pre class="sh" name="code">Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Oracle]
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"
[HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ALL_HOMES\ID0]
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"
[HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOME0]
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-6679719024598504972011-01-19T02:36:00.001+08:002012-02-12T23:32:39.088+08:00使用批次檔開關 Oracle 服務因為在 NB 上灌了 Oracle 做開發用,可是每次開機都變得很慢,而且不是每天都會用到 Oracle,但常常開開關關服務真的很麻煩,索性找了一下批次檔開關服務的方法,寫了一個簡單的小工具,至少在處理這瑣碎的事可以快樂一點。<br />
<br />
<pre class="sh" name="code">@echo off
set /p STATUS=Oracle Service status to [start/stop] :
net %STATUS% "OracleMTSRecoveryService"
net %STATUS% "OracleOraHome92Agent"
net %STATUS% "OracleOraHome92TNSListener"
net %STATUS% "OracleServiceMYDB"
pause
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-54728118763539296252010-07-16T18:42:00.007+08:002013-06-10T23:25:22.711+08:00[Ubuntu] PHP 連接 Oracle 語系設定PHP 的環境變數必須在 Apache 中設定<br />
<br />
開啟:<br />
vim /etc/apache2/envvars<br />
<br />
在最後面加入:<br />
export NLS_LANG="AMERICAN_AMERICA.UTF8"<br />
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"<br />
<br />
參考來源:<br />
<a target="_blank" href="http://tony.strongniche.com.tw/linux/contents.php?sn=534">如何新增apache使用的環境變數</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com2tag:blogger.com,1999:blog-5946530704742130970.post-75151881781516744462010-07-16T16:38:00.002+08:002013-08-06T22:48:49.283+08:00[Oracle] 修改 SESSION 的日期格式<pre class="sql" name="code">ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
</pre><br />
<table class="table_list" cellspacing="0" cellpadding="3" border="1"><tbody>
<tr class="header"><th width="93">Parameter</th><th>Explanation</th></tr>
<tr><td>YEAR</td><td>Year, spelled out</td></tr>
<tr><td>YYYY</td><td>4-digit year</td></tr>
<tr><td>YYY<br />
YY<br />
Y</td><td>Last 3, 2, or 1 digit(s) of year.</td></tr>
<tr><td>IYY<br />
IY<br />
I</td><td>Last 3, 2, or 1 digit(s) of ISO year.</td></tr>
<tr><td>IYYY</td><td>4-digit year based on the ISO standard</td></tr>
<tr><td>Q</td><td>Quarter of year (1, 2, 3, 4; JAN-MAR = 1).</td></tr>
<tr><td>MM</td><td>Month (01-12; JAN = 01).</td></tr>
<tr><td>MON</td><td>Abbreviated name of month.</td></tr>
<tr><td>MONTH</td><td>Name of month, padded with blanks to length of 9 characters.</td></tr>
<tr><td>RM</td><td>Roman numeral month (I-XII; JAN = I).</td></tr>
<tr><td>WW</td><td>Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.</td></tr>
<tr><td>W</td><td>Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.</td></tr>
<tr><td>IW</td><td>Week of year (1-52 or 1-53) based on the ISO standard.</td></tr>
<tr><td>D</td><td>Day of week (1-7).</td></tr>
<tr><td>DAY</td><td>Name of day.</td></tr>
<tr><td>DD</td><td>Day of month (1-31).</td></tr>
<tr><td>DDD</td><td>Day of year (1-366).</td></tr>
<tr><td>DY</td><td>Abbreviated name of day.</td></tr>
<tr><td>J</td><td>Julian day; the number of days since January 1, 4712 BC.</td></tr>
<tr><td>HH</td><td>Hour of day (1-12).</td></tr>
<tr><td>HH12</td><td>Hour of day (1-12).</td></tr>
<tr><td>HH24</td><td>Hour of day (0-23).</td></tr>
<tr><td>MI</td><td>Minute (0-59).</td></tr>
<tr><td>SS</td><td>Second (0-59).</td></tr>
<tr><td>SSSSS</td><td>Seconds past midnight (0-86399).</td></tr>
<tr><td>FF</td><td>Fractional seconds.</td></tr>
</tbody></table>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-56310585762989407632010-05-22T23:32:00.004+08:002012-02-12T23:32:39.092+08:00[Oracle] Sequence 取號函數<pre class="sql" name="code"><br />-- [新增]<br />CREATE SEQUENCE my_sequence<br /> INCREMENT BY 1<br /> START WITH 1<br /> MAXVALUE 99999999999<br /> NOCYCLE<br /> CACHE 10<br />;<br /><br />-- 參數說明:<br />INCREMENT BY n -- 每次疊加的值(正負數)<br /><br />START WITH n -- 指定初始值,建立後將無法透過 ALTER 修改<br /><br />MAXVALUE n -- 最大編號<br />NOMAXVALUE -- (預設)不設置最大值,由系統上限決定<br /><br />MINVALUE n -- 最小編號<br />NOMINVALUE -- (預設 1)不設置最小值<br /><br />CYCLE -- 當取至最大值後,是否循環再由最小值開始<br />NOCYCLE -- (預設)不設置循環,當超過限制時將返回異常<br /><br />CACHE n -- (預設 20)系統會一次取出 n 個數作為快取,但會造成跳號的現象<br />NOCACHE -- 不使用快取,保證在產生的編號中沒有跳號,但這樣會降低性能.<br /><br /><br /><br />-- [修改]<br />ALTER SEQUENCE my_sequence<br /> INCREMENT BY 10 <br /> MAXVALUE 10000<br /> CYCLE<br /> NOCACHE<br />;<br /><br /><br /><br />-- [刪除]<br />DROP SEQUENCE my_sequence;<br /><br /><br /><br />-- [使用方式]<br />my_sequence.CURRVAL -- 返回序號的當前值<br />my_sequence.NEXTVAL -- 增加序號的值,然後返回序號值<br /><br /></pre><br /><br />參考來源:<br /><a href="http://proxy.gtn.com.tw/forum/index.php?topic=25.0">Oracle 產生自動編號方式</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-57420022102342594902010-05-15T02:12:00.004+08:002012-02-12T23:32:39.094+08:00[Oracle] 基礎 Table Schema 指令<pre class="sql" name="code"><br />-- 建立資料表<br />CREATE TABLE table_name(<br /> column1 NUMBER,<br /> column2 CHAR,<br /> column3 VARCHAR2,<br /> column4 DATE,<br /> <br /> -- 主鍵(PRIMARY KEY)<br /> CONSTRAINT pk_table_name PRIMARY KEY(column1,column2),<br /> -- 唯一鍵(UNIQUE KEY)<br /> CONSTRAINT unique_table_name UNIQUE(column1,column2),<br /> -- 外來鍵(FOREIGN KEY)<br /> CONSTRAINT fk_table_name_column1<br /> FOREIGN KEY(column1, column2)<br /> REFERENCES parent_table(column1, column2)<br /> [ON DELETE [CASCADE|SET NULL]]<br /> -- 當參考鍵刪除時,相對應做的處理:<br /> -- 預設 UPDATE No Action & DELETE No Action<br /> -- ON DELETE CASCADE: 刪除參考鍵時連同刪除<br /> -- ON DELETE SET NULL: 刪除參考鍵時將外鍵設為 NULL<br />);<br /><br /><br />-- 為資料表標示註釋<br />COMMENT ON TABLE table_name IS '資料表註釋';<br /><br />-- 為資料欄位標示註釋<br />COMMENT ON COLUMN table_name.column1 IS '資料欄位註釋';<br /><br /><br />-- 附加主鍵(PRIMARY KEY)<br />ALTER TABLE table_name<br />ADD CONSTRAINT pk_table_name PRIMARY KEY(column1,column2);<br /><br /><br />-- 附加唯一鍵(UNIQUE KEY)<br />ALTER TABLE table_name<br />ADD CONSTRAINT unique_table_name UNIQUE(column1,column2);<br /><br /><br />-- 附加外來鍵(FOREIGN KEY)<br />ALTER TABLE table_name<br />ADD CONSTRAINT fk_table_name_column1<br /> FOREIGN KEY(column1, column2)<br /> REFERENCES parent_table(column1, column2)<br /> [ON DELETE [CASCADE|SET NULL]];<br /><br /><br />-- 建立索引(Index) <br />CREATE [UNIQUE] INDEX idx_table_name ON table_name(column1, column2);<br /></pre><br />最近剛剛接觸 Oracle<br />很多觀念一時轉不過來<br />雖然 SQL 都大同小異<br />但就是這些被這些小異搞翻掉 <br /><br />整體架構跟 MySQL 差蠻多<br />只能說 MySQL 真的很容易上手<br />而且社群跟文獻都很多<br />所以要找參考資料比較容易<br /><br />參考連結:<br /><a href="http://ss64.com/ora/">Oracle Commands</a><br /><a href="http://www.adp-gmbh.ch/ora/sql/">Oracle SQL</a><br /><a href="http://www.techonthenet.com/oracle/index.php">Oracle/PLSQL Topics</a><br /><a href="http://blog.miniasp.com/post/2007/10/Oracle-Development-Note.aspx">Oracle 開發筆記</a><br /><a href="http://www.builder.com.cn/2007/0828/474815.shtml">Oracle基本语法集锦</a><br /><a href="http://blog.chinaunix.net/u/20571/showart_1316507.html">oracel 數據完整性</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-64235130607324390492010-05-15T01:12:00.005+08:002012-02-12T23:32:39.095+08:00[Oracle] 快速建立使用者<pre class="sql" name="code"><br />CREATE USER user_name IDENTIFIED BY user_password; -- 建立使用者<br />GRANT dba TO user_name; -- 授與最大權力<br /></pre><br />為了這個簡單的指令上 google 找半天<br />最後還是去請教 MIS 的前輩Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0