Bug has Fixed when using Merge Statement with Database Link - Oracle 10g

/*
先前已經測過 Merge 的語法透過 DB2 Federation 做更新是不可行的.
Oracle 的話要上patch，聽說11g已經沒有這個bug了

My Scenario:建兩個 Database 與 兩個 Table 分別如下:
　　DB1: Orion　　UserName:donkey 　　Table: Oriontb
　　DB2: Odyssey　UserName:voyager　　Table: Odysseytb
測試使用傳統update語法與Merge Update
*/


/*　用User:voyager登進odyssey建立測試table與資料　*/

create table odysseytb
(
　　col1 integer not null primary key,
　　col2 varchar2(32)
);
insert into odysseytb values (1,'1 from odysseytb');
insert into odysseytb values (2,'2 from odysseytb');
commit;

 
/*　用User:donkey登進orion建立database link及測試table與資料　*/

create table oriontb
(
　　col1 integer not null primary key,
　　col2 varchar2(32)
);
insert into oriontb values (1,'1 from oriontb');
commit;

 

1. user donkey　已經由 grant create database link to donkey 而有權限 create database link
2. 使用 odyssey 資料庫 user voyager的 權限


create database link odyssey
connect to voyager
identified by voyager
using 'odyssey'

 
/*　測試連線是否成功　*/

select * from odysseytb@odyssey;

　　　COL1　COL2
---------- --------------------------------
　　　　　1　1 from odysseytb
　　　　　2　2 from odysseytb

 
/*　測試傳統update語法,用oriontb去update odysseytb@odyssey 　*/

update (select oriontb.col1,oriontb.col2,
　　　　　　　　　t.col1 link_col1,t.col2 link_col2
　　　　from oriontb,odysseytb@odyssey t
　　　　where oriontb.col1 = t.col1) set link_col2 = col2

已更新 1 個資料列.

select * from odysseytb@odyssey;

　　　COL1　COL2
---------- ---------------------------------
　　　　　1　1 from oriontb
　　　　　2　2 from odysseytb

 
/*　Rollback。範例資料還需再使用　*/

/*　測試傳統update語法,用odysseytb@odyssey來update oriontb　*/

update (select oriontb.col1,oriontb.col2,
　　　　　　　　　t.col1 link_col1,t.col2 link_col2
　　　　from oriontb,odysseytb@odyssey t
　　　　where oriontb.col1 = t.col1) set col2 = link_col2;

已更新 1 個資料列.

select * from oriontb;

　　　COL1　COL2
---------- ----------------------------------
　　　　　1　1 from odysseytb


 
/*　測試 Merge Update 語法，結果得到以下錯誤　*/


merge into oriontb using odysseytb@odyssey t on (oriontb.col1 = t.col1)
when matched then
update set oriontb.col2 = t.col2;


merge into oriontb using odysseytb@odyssey t on (oriontb.col1 = t.col1)
*
ERROR 在行 1:
ORA-02064: 不支援分散式作業

 
/*　測試 Merge Insert 語法，結果得到以下錯誤　*/

merge into oriontb using odysseytb@odyssey t on (oriontb.col1 = t.col1)
when not matched then insert (oriontb.col1,oriontb.col2)
values (t.col1,t.col2);

merge into oriontb using odysseytb@odyssey t on (oriontb.col1 = t.col1)
*
ERROR 在行 1:
ORA-02064: 不支援分散式作業


Use Least function - Oracle 10g

/*
　　Scenario:
　　User 說，正常的情況下Column A 的值要>= Column B 且
　　　　　　　　　　　　　Column B 的值要>= Column C 且
　　　　　　　　　　　　　Column C 的值要>= Column D
　　User 要找出不正常的資料。

　　My solution: 使用Least Function
　　Least((A-B),(B-C),(C-D)) < 0
*/

/*　建立測試Table&資料　*/


create table test
(
　　col_id varchar2(5),
　　col_a integer,
　　col_b integer,
　　col_c integer,
　　col_d integer
);

SQL> insert into test values ('00001',4,3,2,1);
已建立 1 個資料列.
SQL> insert into test values ('00002',9,1,2,5);
已建立 1 個資料列.
SQL> insert into test values ('00003',7,5,5,5);
已建立 1 個資料列.
SQL> insert into test values ('00004',3,4,9,5);
已建立 1 個資料列.
SQL> commit;

 
/*　找非正常的資料　*/


select * from test
where least((col_a-col_b),(col_b - col_c),(col_c - col_d)) < 0;

COL_ID　　　COL_A　　　COL_B　　　　COL_C　　　COL_D
---------- ---------- ---------- ---------- ----------
00002　　　　　　　　　9　　　　　1　　　　　　2　　　　　　5
00004　　　　　　　　　3　　　　　4　　　　　　9　　　　　　5


Oracle Common Table Expression (CTE) - Oracle10g

/*
　　DB2 CTE 沒有辦法使用 Set or Select into 將結果設給變數，
　　Oracle CTE 卻可以做到.
　　implement CTE,Recursive SQL in the Scenario:
　　寫一段 PL/SQL, 取得系統日期第三季的起始日及迄日,並將值設給變數
*/

/* PL/SQL 內容如下: */

DECLARE
　　vSTARTDT DATE;
　　vENDDT DATE;
BEGIN
　　WITH TEMP AS
　　(
　　　SELECT　STARTDT + LEVEL COL1
　　　FROM　　(SELECT TO_DATE(EXTRACT(YEAR FROM SYSDATE) ||
　　　　　　　　　　　　　　　　　'0101','YYYYMMDD') STARTDT
　　　　　　　　FROM　　DUAL) A1
　　　CONNECT BY LEVEL <= 365
　　)
　　SELECT　MIN(COL1),MAX(COL1)
　　INTO　　vSTARTDT,vENDDT
　　FROM　　TEMP
　　WHERE　　EXTRACT(YEAR FROM COL1) =
　　　　　　　EXTRACT(YEAR FROM SYSDATE)
　　AND　　　TO_CHAR(COL1,'Q') = 3;

　　DBMS_OUTPUT.PUT_LINE(vSTARTDT);
　　DBMS_OUTPUT.PUT_LINE(vENDDT);
END;
/

/* 執行後傳回結果 */

01-7月 -08
30-9月 -08

已順利完成 PL/SQL 程序.


Finding Multi-line Data via DUMP function using Oracle10g

/*
　　從字串型態的欄位找出夾雜換行特殊字元的資料
*/


/* create testing table */

CREATE TABLE TSTRING
(
　　COL0 INTEGER,
　　COL1 VARCHAR2(32)
);

/* create testing data */

INSERT INTO TSTRING VALUES (1,'KX-
123C958');
INSERT INTO TSTRING VALUES (2,'OD-129C' || CHR(10) || '900');
INSERT INTO TSTRING VALUES (3,'OC-125X' || CHR(10) || '9002');
INSERT INTO TSTRING VALUES (4,'OC-127Y' || CHR(10) || '15');
INSERT INTO TSTRING VALUES (5,'LC-124C901');
INSERT INTO TSTRING VALUES (6,'LC-124C902');
INSERT INTO TSTRING VALUES (7,'LC-124C903');
INSERT INTO TSTRING VALUES (8,'
');
INSERT INTO TSTRING VALUES (9,'1
');
INSERT INTO TSTRING VALUES (10,CHR(10));
INSERT INTO TSTRING VALUES (11,'北緯
10.5度');
COMMIT;

/* dump 出來的結果為ascii code，找出夾雜有10的字元 */

SELECT COL0,COL1,COL2,SUBSTR(COL2,INSTR(COL2,':')+1) COL3
FROM
(SELECT COL0,COL1,DUMP(COL1) COL2 FROM TSTRING) T
WHERE SUBSTR(COL2,INSTR(COL2,':')+1) LIKE '%10%';

/* 查詢結果如下: */




/*
　　不過上述語法還是有問題，
　　ex:當 ascii 是 100 及以上的即使沒有換行符號，仍舊會被 select 出來
　　還是直接用 instr 去找，最為保險
　　謝謝熊熊
*/

SELECT * FROM TSTRING WHERE INSTR(COL1,CHR(10)) > 0;


Sorting an alphanumeric column in Oracle10g

/*
　　sorting alphanumeric column.
　　say, column COL1 values with below:
　　C1
　　C2
　　C3
　　C10
　　after order by COL1,it turns out to be
　　C1
　　C10
　　C2
　　C3
　　but it should be
　　C1
　　C2
　　C3
　　C10
*/


/* create testing table */

CREATE TABLE TEST
(
　　COL0 CHAR(3)
);

/* insert testing data */

INSERT INTO TEST VALUES ('C1');
INSERT INTO TEST VALUES ('C2');
INSERT INTO TEST VALUES ('C3');
INSERT INTO TEST VALUES ('C4');
INSERT INTO TEST VALUES ('C5');
INSERT INTO TEST VALUES ('C6');
INSERT INTO TEST VALUES ('C7');
INSERT INTO TEST VALUES ('C8');
INSERT INTO TEST VALUES ('C9');
INSERT INTO TEST VALUES ('C10');
INSERT INTO TEST VALUES ('C11');
INSERT INTO TEST VALUES ('C12');
INSERT INTO TEST VALUES ('C13');
INSERT INTO TEST VALUES ('CE1');
INSERT INTO TEST VALUES ('CE2');
INSERT INTO TEST VALUES ('E1');
INSERT INTO TEST VALUES ('B8');
INSERT INTO TEST VALUES ('BE1');
INSERT INTO TEST VALUES ('BE2');
INSERT INTO TEST VALUES ('CE');
INSERT INTO TEST VALUES ('教');
INSERT INTO TEST VALUES ('教1');
INSERT INTO TEST VALUES ('教2');
INSERT INTO TEST VALUES ('教10');

/* using TRANSLATE Function to separate alphabets and numbers */

SELECT COL0 FROM
(
SELECT COL0,
　　　　TRIM(TRANSLATE(COL0,'1234567890',' ')) COL1,
　　　　TO_NUMBER(COALESCE(TRIM(TRANSLATE(COL0,
　　　　　　　　　　　　　　　　　　　　　　　TRANSLATE(COL0,'1234567890',' '),
　　　　　　　　　　　　　　　　　　　　　　　' ')
　　　　　　　　　　　　　　　),NULL,'0')) COL2
FROM　TEST
) T
ORDER BY COL1,COL2;

/* The Result */

COL0
--------
B8
BE1
BE2
C1
C2
C3
C4
C5
C6
C7
C8
C9
C10
C11
C12
C13
CE
CE1
CE2
E1
教
教1
教2
教10


Pipe multi records to one column using Oracle10g

/*
　　將多筆資料pipe成一個column
　　使用 Recursive SQL + Self join Table
*/

/* Create testing Table & Data */

CREATE TABLE SAMPLE
(
　　COL1 INTEGER,
　　COL2 CHAR(3)
) IN USERSPACE1

INSERT INTO SAMPLE VALUES (1,'111');
INSERT INTO SAMPLE VALUES (1,'222');
INSERT INTO SAMPLE VALUES (1,'333');
INSERT INTO SAMPLE VALUES (1,'444');

INSERT INTO SAMPLE VALUES (2,'AAA');
INSERT INTO SAMPLE VALUES (2,'BBB');
INSERT INTO SAMPLE VALUES (2,'CCC');

COMMIT;


/*
　　想辦法將資料做成可以 self join 的資料
　　以下面 SQL 為例，self join 時可以用 PRECURSOR 欄位 join 到 RID 欄位
*/


SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR
FROM　　SAMPLE




/* 使用剛才的 SQL 再搭配 Recursive SQL 將 COL2 PIPE 起來，展開資料如下 */

SELECT COL1,SUBSTR(SYS_CONNECT_BY_PATH(COL2,','),2) HIST
FROM
(
SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR
FROM　　SAMPLE
) TMP
START WITH RID = 1
CONNECT BY PRIOR RID = PRECURSOR AND PRIOR COL1 = COL1




/*
　　對SQL再做些微修改：
　　1. 增加欄位 CNT 記錄 PIPE 起來的筆數
　　2. 增加WHERE條件，取 RID = CNT 的資料
*/

SELECT COL1,SUBSTR(SYS_CONNECT_BY_PATH(COL2,','),2) HIST
FROM
(
SELECT　COL1,COL2,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
　　　　ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR,
　　　　COUNT(*) OVER(PARTITION BY COL1) CNT
FROM　　SAMPLE
) TMP
WHERE RID = CNT
START WITH RID = 1
CONNECT BY PRIOR RID = PRECURSOR AND PRIOR COL1 = COL1


Get numbers from Multi or Single-byte characters using Oracle10g

/*
　　Purpose: eliminate none digital data from strings
　　Using functions: TO_SINGLE_BYTE、ASCII、SYS_CONNECT_BY_PATH、OLAP Function
　　And Recursive SQL: Connect by
*/


/* Marvelous Function: TO_SINGLE_BYTE */

SELECT TO_SINGLE_BYTE('０９７年０１０１＃NO.００１') FROM DUAL

/* Result is */

097年0101#NO.001

/* Chinese characters also turn out to be single-byte */

SELECT SUBSTR(TO_SINGLE_BYTE('０９７年０１０１＃NO.００１'),4,1) FROM DUAL

/* Result is */

年


/*
　　Using Recursive SQL to separate each character
　　Choose number whichever ascii code between 48 and 59
　　Columns (ONO、PRECURSOR) made this inner view a self join-like table
　　Column LENS made of Olap function which counting numbers
*/


SELECT　ROWNUM ONO,DATA,ROWNUM - 1 PRECURSOR,COUNT(*) OVER() LENS
FROM　　(SELECT TXT,
　　　　　　　　(CASE WHEN ASCII(SUBSTR(TXT,LEVEL,1)) BETWEEN 48 AND 59 THEN
　　　　　　　　 　　　SUBSTR(TXT,LEVEL,1) END) DATA
　　　　　FROM　(SELECT TO_SINGLE_BYTE('０９７＃０１－ＮＯ－２９００１３９７６') TXT
　　　　　　　　　FROM DUAL) T
　　　　　CONNECT BY LEVEL <= LENGTH(TXT)
　　　　　) T
WHERE DATA IS NOT NULL


/* Result is */




/*
　　use inner view above to make up numbers
　　SYS_CONNECT_BY_PATH keeps each recursive result of data
*/


SELECT　TXT,REPLACE(SYS_CONNECT_BY_PATH(DATA,' '),' ','') HIST
FROM　　(SELECT TXT,ROWNUM ONO,DATA,ROWNUM - 1 PRECURSOR,
　　　　　　　　　COUNT(*) OVER() LENS
　　　　　FROM　(SELECT TXT,
　　　　　　　　　　　　　(CASE WHEN ASCII(SUBSTR(TXT,LEVEL,1)) BETWEEN 48 AND 59
　　　　　　　　　　　　　　　　　THEN SUBSTR(TXT,LEVEL,1) END) DATA
　　　　　　　　　FROM (SELECT TO_SINGLE_BYTE('０９７＃０１－ＮＯ－２９００１３９７６') TXT
　　　　　　　　　　　　　　FROM DUAL) T
　　　　　　　　　CONNECT BY LEVEL <= LENGTH(TXT)
　　　　　　　　) T WHERE DATA IS NOT NULL
　　　　　)T2
WHERE ONO = LENS
START WITH ONO = 1
CONNECT BY PRIOR ONO = PRECURSOR


/* Result is */

Find the least values of columns using Oracle10g

/*
　　朋友負責的專案裡有個Table是這樣的：
　　在同一筆record中存在三個不同的欄位組，
　　報表則須要從這三個欄位組找出最接近系統日期的那一組。
　　她希望是用最精簡的SQL來處理掉這個需求，於是我就借來玩玩了。
　　1) 不使用 least function
　　2) 使用 least function
*/
　　
/* ----- 1)不使用 least function ----- */
-- 1) 建立一個Table裡面存在三組不同的欄位組

DROP TABLE TRIPLE_REC
CREATE TABLE TRIPLE_REC
(
　　　KEYNO　　　　INTEGER PRIMARY KEY,
　　　TYPE1　　　　INTEGER,
　　　TYPEUSER1　　VARCHAR2(5),
　　　TYPEDT1　　　DATE,
　　　TYPE2　　　　INTEGER,
　　　TYPEUSER2　　VARCHAR2(5),
　　　TYPEDT2　　　DATE,
　　　TYPE3　　　　INTEGER,
　　　TYPEUSER3　　VARCHAR2(5),
　　　TYPEDT3　　　DATE
);

--測試新增兩筆資料

INSERT INTO TRIPLE_REC VALUES (1,1,'ADMIN',TO_DATE('20070831','YYYYMMDD'),
　　　　　　　　　　　　　　　　　　　2,'XC001',TO_DATE('20080701','YYYYMMDD'),
　　　　　　　　　　　　　　　　　　　3,'XC019',TO_DATE('20050729','YYYYMMDD'));
INSERT INTO TRIPLE_REC VALUES (2,1,'XC005',TO_DATE('20020821','YYYYMMDD'),
　　　　　　　　　　　　　　　　　　　2,'XC008',TO_DATE('20080501','YYYYMMDD'),
　　　　　　　　　　　　　　　　　　　3,'XC001',TO_DATE('20060729','YYYYMMDD'));

COMMIT;

--看一下資料內容

SELECT * FROM TRIPLE_REC ORDER BY KEYNO;

KEYNO TYPE1 TYPEUSER1 TYPEDT1 TYPE2 TYPEUSER2 TYPEDT2 TYPE3 TYPEUSER3 TYPEDT3
----- ----- --------- ------- ----- --------- ------- ----- --------- --------
1　　　1　　ADMIN　　2007/8/31　　2　　XC001　　2008/7/1　　3　　XC019　　2005/7/29
2　　　1　　XC005　　2002/8/21　　2　　XC008　　2008/5/1　　3　　XC001　　2006/7/29


--從三組不同的資料欄位中選一組日期最接近系統日的

SELECT *
FROM
(SELECT KEYNO,TYPE,TYPEUSER,TYPEDT,DIFF,
　　　　　ROW_NUMBER() OVER(PARTITION BY KEYNO ORDER BY DIFF) CLOSEDAYDIFF
　FROM　　(SELECT KEYNO,TYPE1 TYPE,TYPEUSER1 TYPEUSER,TYPEDT1 TYPEDT,
　　　　　　　　　　ABS(TRUNC(TYPEDT1) - TRUNC(SYSDATE)) DIFF
　　　　　　FROM TRIPLE_REC
　　　　　　UNION　ALL
　　　　　　SELECT KEYNO,TYPE2 TYPE,TYPEUSER2 TYPEUSER,TYPEDT2 TYPEDT,
　　　　　　　　　　ABS(TRUNC(TYPEDT2) - TRUNC(SYSDATE)) DIFF
　　　　　　FROM　　TRIPLE_REC
　　　　　　UNION　ALL
　　　　　　SELECT KEYNO,TYPE3 TYPE,TYPEUSER3 TYPEUSER,TYPEDT3 TYPEDT,
　　　　　　　　　　ABS(TRUNC(TYPEDT3) - TRUNC(SYSDATE)) DIFF
　　　　　　FROM　　TRIPLE_REC) TMP
) TMP2
WHERE CLOSEDAYDIFF = 1;


--得到以下結果

KEYNO TYPE　　TYPEUSER　TYPEDT　　　　　DIFF　　　CLOSEDAYDIFF
------ ------ --------- ------------- --------- ------------
1　　　2　　　　XC001　　2008/7/1　　　　56　　　　　1
2　　　2　　　　XC008　　2008/5/1　　　　117　　　　 1


/* ----- 2)使用 least function ----- */
/* 建立測試table */

create table least_test
(
　　seqno　integer,
　　id1　　char(5),
　　col1　date,
　　id2　　char(5),
　　col2　date,
　　id3　　char(5),
　　col3　date,
　　id4　　char(5),
　　col4　date
);

/* 建立測試資料 */

insert into least_test values (1,'00123',to_date('20080115','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00576',to_date('20080222','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00123',to_date('20080305','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00456',to_date('20080510','yyyymmdd'));
insert into least_test values (2,'00123',to_date('20080530','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00123',to_date('20080202','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00576',to_date('20071230','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00576',to_date('20080620','yyyymmdd'));
insert into least_test values (3,'00456',to_date('20081030','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00456',to_date('20080622','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00456',to_date('20081105','yyyymmdd'),
　　　　　　　　　　　　　　　　　　　'00123',to_date('20080430','yyyymmdd'));
commit;

/* 用 least 找 col1,col2,col3,col4 中最接近系統日期的欄位*/

select seqno,case least(diff1,diff2,diff3,diff4)
　　　　　　　　　when diff1 then id1
　　　　　　　　　when diff2 then id2
　　　　　　　　　when diff3 then id3
　　　　　　　　　when diff4 then id4 end,
　　　　　　　case least(diff1,diff2,diff3,diff4)
　　　　　　　　　when diff1 then col1
　　　　　　　　　when diff2 then col2
　　　　　　　　　when diff3 then col3
　　　　　　　　　when diff4 then col4 end
from (select seqno,id1,id2,id3,id4,col1,col2,col3,col4,
　　　　　　　　abs(trunc(col1) - trunc(sysdate)) diff1,
　　　　　　　　abs(trunc(col2) - trunc(sysdate)) diff2,
　　　　　　　　abs(trunc(col3) - trunc(sysdate)) diff3,
　　　　　　　　abs(trunc(col4) - trunc(sysdate)) diff4
　　　from　least_test) t

/* 結果 */

1　　　00456　　　2008/5/10
2　　　00576　　　2008/6/20
3　　　00456　　　2008/11/5


Bulk Binds to enhance cursor performance using Oracle10g

～ FORALL and BULK COLLECT INTO ～


-- 比較使用BULK COLLECT INTO以及既有Cursor的執行效能
-- 建立測試Tables

SQL>CREATE TABLE MY_ALL_OBJECTS
　　AS SELECT * FROM ALL_OBJECTS;

SQL>CREATE TABLE RESULT_OBJECTS
　　AS SELECT * FROM MY_ALL_OBJECTS WHERE 1 = 0;

SQL>SELECT COUNT(*) FROM MY_ALL_OBJECTS;

COUNT(*)
------------
40997

-- 建立測試Procedures BULK_TEST

CREATE OR REPLACE PROCEDURE BULK_TEST (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
　　TYPE ARRAY IS TABLE OF my_all_objects%ROWTYPE;
　　l_data ARRAY;
　　CURSOR OBJ_CUR IS SELECT * FROM my_all_objects;
BEGIN

　　OPEN OBJ_CUR;
　　LOOP
　　FETCH OBJ_CUR BULK COLLECT INTO l_data LIMIT p_array_size;
　　
　　FORALL i IN 1..l_data.COUNT
　　　　INSERT INTO RESULT_OBJECTS VALUES l_data(i);
　　　　EXIT WHEN l_data.COUNT = 0;
　　END LOOP;
　　CLOSE OBJ_CUR;
　　COMMIT;
END BULK_TEST;


-- 建立測試Procedures BULK_TEST2用既有CURSOR語法

CREATE OR REPLACE PROCEDURE BULK_TEST2
IS
　　vOWNER　　　　　　my_all_objects.OWNER%TYPE;
　　vOBJECT_NAME　　　my_all_objects.OBJECT_NAME%TYPE;
　　vSUBOBJECT_NAME　my_all_objects.SUBOBJECT_NAME%TYPE;
　　vOBJECT_ID　　　　my_all_objects.OBJECT_ID%TYPE;
　　vDATA_OBJECT_ID　my_all_objects.DATA_OBJECT_ID%TYPE;
　　vOBJECT_TYPE　　　my_all_objects.OBJECT_TYPE%TYPE;
　　vCREATED　　　　　my_all_objects.CREATED%TYPE;
　　vLAST_DDL_TIME 　my_all_objects.LAST_DDL_TIME%TYPE;
　　vTIMESTAMP 　　　my_all_objects.TIMESTAMP%TYPE;
　　vSTATUS　　　　　my_all_objects.STATUS%TYPE;
　　vTEMPORARY　　　my_all_objects.TEMPORARY%TYPE;
　　vGENERATED　　　my_all_objects.GENERATED%TYPE;
　　vSECONDARY　　　my_all_objects.SECONDARY%TYPE;

　　CURSOR OBJ_CUR IS SELECT * FROM my_all_objects;
BEGIN
　　OPEN OBJ_CUR;
　　LOOP
　　FETCH OBJ_CUR INTO vOwner,vObject_name,vSubObject_name,
　　　　　　　　　　　　　vOBJECT_ID,vDATA_OBJECT_ID,vOBJECT_TYPE,
　　　　　　　　　　　　　vCREATED,vLAST_DDL_TIME,vTIMESTAMP,
　　　　　　　　　　　　　vSTATUS,vTEMPORARY,vGENERATED,vSECONDARY;
　　EXIT WHEN OBJ_CUR%NOTFOUND;

　　INSERT INTO RESULT_OBJECTS
　　VALUES (vOwner,vObject_name,vSubObject_name,vOBJECT_ID,vDATA_OBJECT_ID,
　　　　　　　vOBJECT_TYPE,vCREATED,vLAST_DDL_TIME,vTIMESTAMP,vSTATUS,
　　　　　　　vTEMPORARY,vGENERATED,vSECONDARY);

　　END LOOP;
　　CLOSE OBJ_CUR;
　　COMMIT;
END BULK_TEST2;


--將測試程式包在bulk.sql，內容如下

select current_timestamp from dual;
exec BULK_TEST;
select current_timestamp from dual;
select count(*) from RESULT_OBJECTS;


--執行

SQL>@c:\bulk.sql;


--測試執行時間結果:

CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.43.612000 下午 +08:00

已順利完成 PL/SQL 程序.

CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.44.112000 下午 +08:00

COUNT(*)
----------
　　40997

--將測試程式同樣包在bulk.sql，內容如下

select current_timestamp from dual;
exec BULK_TEST2;
select current_timestamp from dual;
select count(*) from RESULT_OBJECTS;

--執行

SQL>@c:\bulk.sql;

--測試執行時間結果:

CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.44.453000 下午 +08:00

已順利完成 PL/SQL 程序.

CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.47.056000 下午 +08:00

COUNT(*)
----------
　　40997

--使用舊有的cursor語法執行時間明顯慢了幾秒
※ Bulk binds reduce the number of context switches between the PL/SQL engine and the database engine

※ The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." - By Steven Feuerstein.
http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html

Generate 9x9 using Oracle10g SQL Statement


SELECT X,Y,X*Y
FROM
(SELECT level x FROM DUAL CONNECT BY LEVEL <= 9),
(SELECT level y FROM DUAL CONNECT BY LEVEL <= 9)


Difference between VARCHAR2(n),VARCHAR2(n BYTE),VARCHAR2(n CHAR)

/* 20080122
　　Difference between VARCHAR2(n),VARCHAR2(n BYTE),VARCHAR2(n CHAR)
　　reference url:
　　http://www.oracle-base.com/articles/9i/CharacterSemanticsAndGlobalization9i.php
　　使用 ORACLE 參數NLS_LENGTH_SEMANTICS
　　設定值: BYTE / CHAR
　　from:　nls_database_parameters
　　　　　　nls_session_parameters
　　　　　　nls_instance_parameters
*/


/*　當 ORACLE 參數 NLS_LENGTH_SEMANTICS = BYTE 時
　　NOTE: VARCHAR2(n) = VARCHAR2(n BYTE)
*/

CREATE TABLE TBLA
(
COL1 VARCHAR2(20) -- 等同TBLB之設定VARCHAR2(20 BYTE) 即看BYTE 不看字數
);

INSERT INTO TBLA VALUES ('一二三四五六七');

=> 執行後錯誤訊息如下:

ORA-12899: 資料欄 "ORION"."TBLA"."COL1" 的值太大 (實際: 21, 最大值: 20)


INSERT INTO TBLA VALUES ('一二三四五六');

=>執行ok

CREATE TABLE TBLB
(
COL1 VARCHAR2(20 BYTE)
);
INSERT INTO TBLB VALUES ('一二三四五六');

=>執行ok

CREATE TABLE TBLC
(
COL1 VARCHAR2(20 CHAR) -- 看字數不看BYTE數
);

INSERT INTO TBLC VALUES ('一二三四五六七八九十一二三四五六七八九十');

=>執行ok

-- 檢查TABLE使用之EXTENT => 以資料量來說看不出來

SELECT TABLESPACE_NAME, SEGMENT_NAME, EXTENTS,MAX_EXTENTS,
　　　　(EXTENTS/MAX_EXTENTS)*100 AS USED_EXTS
FROM 　USER_SEGMENTS
WHERE 　SEGMENT_NAME IN ('TBLA','TBLB','TBLC');

-- 檢查欄位BYTE數

SELECT 'TBLA' TBLNAME,SUM(VSIZE(COL1)) TBLSIZE FROM TBLA
UNION ALL
SELECT 'TBLB' TBLNAME,SUM(VSIZE(COL1)) TBLSIZE FROM TBLB
UNION ALL
SELECT 'TBLC' TBLNAME,SUM(VSIZE(COL1)) TBLSIZE FROM TBLC;

-- 執行結果

TBLNAME　　　TBLSIZE
----------- ---------
TBLA　　　　　18
TBLB　　　　　18
TBLC　　　　　60

-- TBLA LAYOUT

DESC TBLA

名稱　　　　　　　空值?　　類型
--------------- ------- ------------------------
COL1　　　　　　　　　　　VARCHAR2(20)


/*　將SESSION 的設定改掉　*/

ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

/*　因為 ORACLE 參數 NLS_LENGTH_SEMANTICS = CHAR 時
NOTE: VARCHAR2(n) = VARCHAR2(n CHAR)
*/

CREATE TABLE TBLD
(
COL1 VARCHAR2(20) -- 等同TBLC之設定VARCHAR2(20 CHAR) 即字數不看BYTE數
);


-- TBLD LAYOUT

名稱　　　　　　　空值?　　類型
--------------- ------- ------------------------
COL1　　　　　　　　　　　VARCHAR2(20)

--再看一下改SESSION設定後TBLA LAYOUT

DESC TBLA

名稱　　　　　　　空值?　　類型
--------------- ------- ------------------------
COL1　　　　　　　　　　　 VARCHAR2(20 BYTE)

-- 再將SESSION 改BYTE

ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE;

-- 再看一下 TABLE TBLD LAYOUT

名稱　　　　　　　空值?　　類型
--------------- ------- ------------------------
COL1　　　　　　　　　　　 VARCHAR2(20 CHAR)


Generate Table layouts using Oracle10g system tables

-- Generate table layouts

SELECT (CASE WHEN COLUMN_ID = 1 THEN 'CREATE TABLE ' || TABLE_NAME || '('
ELSE ',' END) || COLUMN_NAME || ' ' ||
DATA_TYPE ||
(CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN '(' || RTRIM(TO_CHAR(CHAR_LENGTH)) || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN
'(' || RTRIM(TO_CHAR(DATA_PRECISION)) || ',' || DATA_SCALE || ')'
END) ||
(CASE WHEN NULLABLE = 'Y' THEN '' ELSE ' NOT NULL ' END) ||
(CASE WHEN COLUMN_ID = MAX_COLNO THEN ');' ELSE '' END)
FROM ALL_TAB_COLUMNS,
(SELECT TABLE_NAME TBNAME,MAX(COLUMN_ID) MAX_COLNO
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'ORION'
GROUP BY TABLE_NAME) T,
(SELECT TABLE_NAME UTABLE
FROM ALL_TABLES
WHERE OWNER = 'ORION'
AND DROPPED = 'NO'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)) T1
WHERE OWNER = 'ORION'
AND TABLE_NAME = TBNAME
AND TABLE_NAME = UTABLE
ORDER BY TABLE_NAME,COLUMN_ID;


-- GENERATE ALTER PRIMARY KEY

SELECT (CASE WHEN POSITION = MINPO THEN
'ALTER TABLE ' || RTRIM(T0.TABLE_NAME) || ' ADD CONSTRAINT ' || T0.CONSTRAINT_NAME ||
' PRIMARY KEY ('
ELSE ',' END) || COLUMN_NAME ||
(CASE WHEN POSITION = MAXPO THEN ');' ELSE '' END)
FROM ALL_CONS_COLUMNS,
(SELECT CONSTRAINT_NAME,TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'P'
) T0,
(SELECT CONSTRAINT_NAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME
) T1
WHERE OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = T0.CONSTRAINT_NAME
AND T0.CONSTRAINT_NAME = T1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO');

-- GENERATE FOREIGN KEY

SELECT STRSQL
FROM
(
SELECT (CASE WHEN ALL_CONS_COLUMNS.POSITION = R1.MINPO THEN
'ALTER TABLE ' || RTRIM(R0.TABLE_NAME) || ' ADD CONSTRAINT ' || R0.CONSTRAINT_NAME ||
' FOREIGN KEY ('
ELSE ',' END) || ALL_CONS_COLUMNS.COLUMN_NAME ||
(CASE WHEN ALL_CONS_COLUMNS.POSITION = R1.MAXPO THEN ')' ELSE '' END) STRSQL,
ALL_CONS_COLUMNS.TABLE_NAME,R_CONSTRAINT_NAME,0 OID
FROM ALL_CONS_COLUMNS,
(SELECT CONSTRAINT_NAME,TABLE_NAME,R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'R') R0,
(SELECT CONSTRAINT_NAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME) R1
WHERE ALL_CONS_COLUMNS.OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = R0.CONSTRAINT_NAME
AND R0.CONSTRAINT_NAME = R1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
UNION ALL
SELECT (CASE WHEN ALL_CONS_COLUMNS.POSITION = P1.MINPO THEN
' REFERENCES ' || RTRIM(P1.PNAME) || ' ('
ELSE ',' END) || ALL_CONS_COLUMNS.COLUMN_NAME ||
(CASE WHEN ALL_CONS_COLUMNS.POSITION = P1.MAXPO THEN ');' ELSE '' END) STRSQL,
ALL_CONS_COLUMNS.TABLE_NAME,P1.CONSTRAINT_NAME,1 OID
FROM (SELECT CONSTRAINT_NAME,TABLE_NAME,R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'R') R0,
(SELECT CONSTRAINT_NAME,TABLE_NAME PNAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME,TABLE_NAME) P1,
ALL_CONS_COLUMNS
WHERE R0.R_CONSTRAINT_NAME = P1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = R0.R_CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
) TMP
ORDER BY R_CONSTRAINT_NAME,OID;

-- EASIEST WAY TO GENERATE DOCUMENT OF CURRENT TABLES LAYOUTS

SELECT 'TableName: ' || TABLE_NAME TNAME,'Description: ' || COALESCE(COMMENTS,'') TDESC
FROM ALL_TAB_COMMENTS
WHERE OWNER = 'ORION'
AND TABLE_TYPE = 'TABLE'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)
ORDER BY TABLE_NAME;



SELECT * FROM ALL_COL_COMMENTS WHERE OWNER = 'ORION'


SELECT TMP.TABLE_NAME,TMP.COLUMN_NAME,COMMENTS,DATA_TYPE,NULLABLE,
COALESCE(PCTYPE,'N') PK,COALESCE(RCTYPE,'N') FK
FROM
(
SELECT TABLE_NAME,COLUMN_NAME,
DATA_TYPE ||
(CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN '(' || RTRIM(TO_CHAR(CHAR_LENGTH)) || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN
'(' || RTRIM(TO_CHAR(DATA_PRECISION)) || ',' || DATA_SCALE || ')'
END) DATA_TYPE,NULLABLE,COLUMN_ID
FROM ALL_TAB_COLUMNS,
(SELECT TABLE_NAME UTABLE
FROM ALL_TABLES
WHERE OWNER = 'ORION'
AND DROPPED = 'NO'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)) T1
WHERE OWNER = 'ORION'
AND TABLE_NAME = UTABLE
) TMP
LEFT JOIN ALL_COL_COMMENTS ON OWNER = 'ORION'
AND ALL_COL_COMMENTS.TABLE_NAME = TMP.TABLE_NAME
AND ALL_COL_COMMENTS.COLUMN_NAME = TMP.COLUMN_NAME
LEFT JOIN (SELECT ALL_CONSTRAINTS.TABLE_NAME PNAME,'Y' PCTYPE,
COLUMN_NAME PCNAME
FROM ALL_CONSTRAINTS,ALL_CONS_COLUMNS
WHERE ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME
AND ALL_CONSTRAINTS.OWNER = 'ORION'
AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P') P ON P.PNAME = TMP.TABLE_NAME
AND P.PCNAME = TMP.COLUMN_NAME
LEFT JOIN (SELECT ALL_CONSTRAINTS.TABLE_NAME RNAME,'Y' RCTYPE,
COLUMN_NAME RCNAME
FROM ALL_CONSTRAINTS,ALL_CONS_COLUMNS
WHERE ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME
AND ALL_CONSTRAINTS.OWNER = 'ORION'
AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'R') R ON R.RNAME = TMP.TABLE_NAME
AND R.RCNAME = TMP.COLUMN_NAME
ORDER BY TMP.TABLE_NAME,TMP.COLUMN_ID;