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


Exchange XML Data via Advanced Queue using Oracle10g

/*
　　using previous article "region.xsd"　　
　　1) create folder/create resource
　　2) grant privilege alter session and register schema for user taurus
　　3) create queue table/queue for user orion,
　　　　grant privilege to taurus,
　　　　start queue
　　4) enqueue by user taurus
　　5) dequeue by user taurus
　　6) xml data stored in repository
　　7) stop queue
*/


　　1)為user taurus　在repository建立folder,resource
　　(刪除folder也是用dbms_xdb.deleteresource)


SQL> declare　
　2　res boolean;
　3　begin
　4　res:=dbms_xdb.createfolder('/public/taurus/');
　5　commit;
　6　end;
　7　/

已順利完成 PL/SQL 程序.

SQL> declare
　2　res boolean;
　3　begin
　4　res:=dbms_xdb.createfolder('/public/taurus/xml');
　5　commit;
　6　end;
　7　/

已順利完成 PL/SQL 程序.

SQL> declare
　2　res boolean;
　3　begin
　4　res:=dbms_xdb.createfolder('/public/taurus/xsd');
　5　commit;
　6　end;
　7　/

已順利完成 PL/SQL 程序.

SQL> declare
　2　res boolean;
　3　begin
　4　res:=dbms_xdb.createresource('/public/taurus/xsd/region.xsd',getDocument('region.xsd'));
　5　commit;
　6　end;
　7　/

已順利完成 PL/SQL 程序.

　　2)grant privilege alter session and register
schema for user taurus

　　2.1)以sysdba登入並給定權限

SQL>connect / as sysdba
已連線.
SQL>grant alter session to orion;
順利授權.

　　2.2)以taurus登入再register schema

SQL> exec dbms_xmlschema.registerschema('http://localhost:8080/public/taurus/xsd/region.xsd',getDocument('region.xsd'));

已順利完成 PL/SQL 程序.

　　3) create queue table/queue for user orion

　　3.1)以orion登入

SQL>connect orion/orion
已連線.

　　3.2)建立queue table

SQL> BEGIN
　2　　　dbms_aqadm.create_queue_table(
　3　　　　　　queue_table => 'xml_qtab',
　4　　　　　　comment => 'Exchange XML using AQ',
　5　　　　　　multiple_consumers => FALSE,
　6　　　　　　queue_payload_type => 'SYS.XMLType',
　7　　　　　　compatible => '8.1'
　8　　　　　) ;
　9　END ;
　10　/

已順利完成 PL/SQL 程序.

　　3.3)建立queue

SQL> BEGIN
　2　dbms_aqadm.create_queue (queue_name => 'xml_queue',queue_table => 'xml_qtab');
　3　END;
　4　/

已順利完成 PL/SQL 程序.

　　3.4)將權限給定taurus

SQL> begin
　2　　dbms_aqadm.grant_queue_privilege(privilege => 'ALL',
　3　　　　　queue_name => 'orion.xml_queue',
　4　　　　　grantee => 'taurus',
　5　　　　　grant_option => FALSE
　6　　　);
　7　commit;
　8　end;
　9　/

已順利完成 PL/SQL 程序.

　　3.5)起動queue

SQL> exec dbms_aqadm.start_queue('xml_queue');

已順利完成 PL/SQL 程序.

　　4) enqueue by user taurus

SQL>connect taurus/taurus
已連線.

　　4.1)enqueue程式如下,存成xenqueue.sql

DECLARE
　　　　ENQUEUE_OPTIONS　　　DBMS_AQ.ENQUEUE_OPTIONS_T;
　　　　MESSAGE_PROPERTIES　　DBMS_AQ.message_properties_t;
　　　　MSGID　　RAW(400);

　　　　XMLDOC SYS.XMLTYPE := XMLTYPE(
　　　　'< ?xml version="1.0" encoding="UTF-8" ? >
　　　　< ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
　　　　xsi:noNamespaceSchemaLocation="http://localhost:8080/public/taurus/xsd/region.xsd" >
　　　　< REGION id="1" >< REGNAME >Northern< /REGNAME >< /REGION >
　　　　< REGION id="2" >< REGNAME >Central< /REGNAME >< /REGION >
　　　　< REGION id="3" >< REGNAME >Southern< /REGNAME >< /REGION >
　　　　< /ROWSET >');

BEGIN
　　　　DBMS_AQ.ENQUEUE ('orion.xml_queue',
　　　　　　　　　　　　　　ENQUEUE_OPTIONS,
　　　　　　　　　　　　　　MESSAGE_PROPERTIES,
　　　　　　　　　　　　　　XMLDOC,
　　　　　　　　　　　　　　MSGID);
commit;
END;
/

　　4.2)執行xenqueue.sql

SQL> @c:\xenqueue.sql

已順利完成 PL/SQL 程序.

　　4.3)查看aq$xml_qtab裡是否有queue進

SQL> select user_data from aq$xml_qtab;

USER_DATA
------------------------------------------------------------------------------
< ?xml version="1.0" encoding="BIG5"? >
< ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://
localhost:8080/public/taurus/xsd/region.xsd" >
< REGION id="1" >
< REGNAME >Northern< /REGNAME >
< /REGION >
< REGION id="2" >
< REGNAME >Central< /REGNAME >
< /REGION >
< REGION id="3" >
< REGNAME >Southern< /REGNAME >
< /REGION >
< /ROWSET >


　　5)dequeue程式如下,存成xdequeue.sql
　　　　dequeue出來的XML DATA直接createresource進repository

declare
　　　　deqOpt　dbms_aq.dequeue_options_t;
　　　　msgProp　dbms_aq.message_properties_t;
　　　　deq_order_data　SYS.XMLType;
　　　　msgHdl　RAW(16);
　　　　res　boolean;
BEGIN
　　　　dbms_aq.dequeue(queue_name => 'orion.xml_queue',
　　　　　　　　　　　　　dequeue_options => deqOpt,
　　　　　　　　　　　　　message_properties => msgProp,
　　　　　　　　　　　　　payload => deq_order_data,
　　　　　　　　　　　　　msgid => msgHdl);

　　　　res:=dbms_xdb.createresource('/public/taurus/xml/region.xml',deq_order_data);
　　　　COMMIT;
end;
/

　　5.2)執行xdequeue.sql

SQL> @c:\xdequeue.sql

已順利完成 PL/SQL 程序.

　　5.3)查看aq$xml_qtab裡是否還有queue

SQL> select count(*) from aq$xml_qtab;

　COUNT(*)
　----------
　　　　　0

　　6) xml data stored in repository裡

SQL> select count(*) from regions;

　COUNT(*)
　----------
　　　　　1

　　7) stop queue

SQL>connect orion/orion
已連線.

SQL> exec dbms_aqadm.stop_queue('xml_queue');

已順利完成 PL/SQL 程序.


Oracle XML DB (III) using Oracle10g

/*
　　1) 在 xsd 裡 xdb:defaultTable 直接建立 table
　　2) 使用 UpdateXML 更新指定的 node
*/　


-- region.xsd內容如下:

< ?xml version="1.0"? >
< xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
　　　　　　　xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="true" >
< xs:element name="ROWSET" type="ROWSET_TYPE" xdb:defaultTable="REGIONS" / >
　　　< xs:complexType name="ROWSET_TYPE" xdb:SQLType="REGION_OT" xdb:maintainDOM="false" >
　　　　< xs:sequence >
　　　　　　< xs:element name="REGION" type="REGION_TYPE" xdb:SQLCollType="REGION_ROW_NTT" minOccurs="0" maxOccurs="unbounded" xdb:maintainOrder="false"/ >
　　　　< /xs:sequence >
　　　< /xs:complexType >
　　< xs:complexType name="REGION_TYPE" xdb:SQLType="REGION_ROW_OT" xdb:maintainDOM="false" >
　　　　< xs:sequence >
　　　　　　< xs:element name="REGNAME" type="xs:string"/ >
　　　　< /xs:sequence >
　　　　< xs:attribute name="id" type="xs:string"/ >
< /xs:complexType >
< /xs:schema >


-- CREATE RESOURCE / REGISTER SCHEMA

SQL> declare
　2　res boolean;
　3　begin
　4　res:=dbms_xdb.createresource('/public/orion/xsd/region.xsd',getDocument('region.xsd'));
　5　commit;
　6　end;
　7　/

已順利完成 PL/SQL 程序.

SQL> exec dbms_xmlschema.registerschema('http://localhost:8080/public/orion/xsd/region.xsd',getDocument('region.xsd'));

已順利完成 PL/SQL 程序.

SQL> desc regions;
名稱 空值? 類型
----------------------------------------------------- -------- ------------------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://localhost:8080/public/orion/xsd/region.xsd" Element "ROWSET") STORAGE Object-relational TYPE "REGION_OT"


-- region.xml內容如下:

< ?xml version="1.0" encoding="UTF-8" ? >
< ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/public/orion/xsd/region.xsd" >
< REGION id="1" >< REGNAME >Northern< /REGNAME >< /REGION >
< REGION id="2" >< REGNAME >Southern< /REGNAME >< /REGION >
< /ROWSET >


-- CREATE RESOURCE

SQL> declare
　2　res boolean;
　3　begin
　4　res:=dbms_xdb.createresource('/public/orion/xml/region.xml',getDocument('region.xml'));
　5　commit;
　6　end;
　7　/

已順利完成 PL/SQL 程序.

-- 看新增結果

SQL> SELECT * FROM REGIONS X;

SYS_NC_ROWINFO$
--------------------------------------------------------
< ?xml version="1.0" encoding="BIG5"? >
< ROWSET >
　< REGION id="1" >
　　< REGNAME >Northern< /REGNAME >
　< /REGION >
　< REGION id="2" >
　　< REGNAME >Southern< /REGNAME >
　< /REGION >
< /ROWSET >

-- 將Northern更新為Central

SQL> update regions
　2　set object_value = updatexml(object_value,'/ROWSET/REGION[1]/REGNAME/text()','Central')
　3　where existsnode(object_value,'/ROWSET/REGION[1]') = 1;

已更新 1 個資料列.

SQL> commit;

確認完成.

SQL> select * from regions;

SYS_NC_ROWINFO$
------------------------------------------------------
< ?xml version="1.0" encoding="BIG5"? >
< ROWSET >
　< REGION id="1" >
　　< REGNAME >Central< /REGNAME >
　< /REGION >
　< REGION id="2" >
　　< REGNAME >Southern< /REGNAME >
　< /REGION >
< /ROWSET >


Running External Jobs with DBMS_SCHEDULER using Oracle10g

/*
　1) 製作一個 DOS Batch Test.bat。內容為:執行一個test.sql對index做rebuild
　2) 給定權限 CREATE JOB,CREATE EXTERNAL JOB,MANAGE SCHEDULER
　3) 給定時區 DEFAULT_TIMEZONE
　4) 啟動服務 OracleJobScheduler
　5) CREATE_PROGRAM
　6) CREATE_SCHEDULE
　7) CREATE_JOB
　8) 手動 RUN_JOB
　9) 從 ALL_SCHEDULER_JOB_RUN_DETAILS 看執行結果
　10)　將JOB,SCHEDULE,PROGRAM移除
*/

　　1)　test.bat/test.sql
　　　　test.bat內容:

sqlplus orion/orion @c:\test.sql

　　　　test.sql內容只對一個index做rebuild:

ALTER INDEX TBLBASE_IDX REBUILD;
EXIT

　　2)　改以dba身份登入給定權限

SQL> connect / as sysdba
已連線.

SQL>GRANT CREATE JOB TO ORION;

順利授權.

SQL>GRANT CREATE EXTERNAL JOB TO ORION;

順利授權.

SQL>GRANT MANAGE SCHEDULER TO ORION;

順利授權.

　　3)　回到user身份來設定DEFAULT_TIMEZONE

SQL>exec dbms_scheduler.set_scheduler_attribute('DEFAULT_TIMEZONE','ROC');

已順利完成 PL/SQL 程序.

　　4)　啟動服務

　　服務一定要起動，否則external job在執行時會失敗並出現以下錯誤:
ORA-27370: 工作僕站無法啟動類型為 EXECUTABLE 的工作ORA-27300: OS 系統相依作業:accessing execution agent 失敗, 狀態: 2
ORA-27301: OS 失敗訊息: ?t?Χ？?？?w???？？C
ORA-27302: 失敗發生於: sjsec 6a
ORA-27303: 額外資訊: ?t?Χ？?？?w???？？C


　　「開始」→「設定」→「控制台」→「控制台」→「系統管理工具」→「服務」找OracleJobScheduler + SID 的服務將之啟動

　　5)　CREATE PROGRAM內容如下，並存成crtprg.sql

begin
　　dbms_scheduler.create_program (
　　　　program_name => 'testbatchfile',
　　　　program_type => 'EXECUTABLE',
　　　　program_action => 'c:\test.bat',
　　　　number_of_arguments => 0,
　　　　enabled => true,
　　　　comments => '');
　commit;
END;
/

SQL> @c:\crtprg.sql

已順利完成 PL/SQL 程序.

　　6)　CREATE SCHEDULE內容如下，並存成crtsch.sql
　　　　我只想執行一次因此將repeat_interval及end_date設為null
　　　　另外，start_date如果為null或過期，CREATE_JOB即使完成，
　　　　all_scheduler_jobs裡也不會有這個JOB的資料

begin
　　dbms_scheduler.create_schedule (
　　　　schedule_name => 'schedule_testbatchfile',
　　　　start_date => '10-8月 -08 06.00.00.000000000 下午 ROC',
　　　　repeat_interval => NULL,
　　　　end_date => NULL,
　　　　comments => '');
　　COMMIT;
END;
/

SQL> @c:\crtsch.sql

已順利完成 PL/SQL 程序.

　　7)　CREATE JOB內容如下，並存成crtjob.sql

begin
　　dbms_scheduler.create_job (
　　　　job_name => 'job_testbatchfile',
　　　　program_name => 'testbatchfile',
　　　　schedule_name => 'schedule_testbatchfile',
　　　　enabled => TRUE,
　　　　comments => '');
　　COMMIT;
END;
/

SQL> @c:\crtjob.sql

已順利完成 PL/SQL 程序.

　　　　檢查一下JOB_TESTBATCHFILE是否CREATE成功

SQL> select job_name from all_scheduler_jobs;

JOB_NAME
------------------------------------------------------------
JOB_TESTBATCHFILE


　　8)　手動執行->RUN_JOB

SQL> exec dbms_scheduler.run_job(job_name => 'JOB_TESTBATCHFILE',use_current_session => FALSE);

已順利完成 PL/SQL 程序.

　　9)　檢查執行結果


SQL> SELECT JOB_NAME,STATUS,
　2　　　　　TO_CHAR(LOG_DATE,'YYYY-MM-DD-HH:MI:SS') LOG_DATE
　3　FROM ALL_SCHEDULER_JOB_RUN_DETAILS
　4　WHERE JOB_NAME = 'JOB_TESTBATCHFILE'
　5　AND TO_CHAR(LOG_DATE,'YYYYMMDD') >= '20080807'
　6　ORDER BY LOG_DATE;

JOB_NAME　　　　　　　　STATUS　　　　LOG_DATE
--------------------　-----------　------------------------------------
JOB_TESTBATCHFILE　　　SUCCEEDED　　2008-08-07-11:36:15

　　DOUBLE CHECK INDEX是否有被異動(p.s謝謝Betty)

SQL> SELECT OBJECT_NAME,
　2　　　　　TO_CHAR(LAST_DDL_TIME,'yyyy-mm-dd-hh:mi:ss') LAST_DDL_TIME
　3　FROM ALL_OBJECTS WHERE OBJECT_NAME = 'TBLBASE_IDX';

OBJECT_NAME　　　　　　LAST_DDL_TIME
-------------------　 --------------------------------------
TBLBASE_IDX　　　　　　2008-08-07-11:36:15



　　10)　移除JOB,SCHEDULE,PROGRAM

SQL>exec dbms_scheduler.drop_job(job_name => 'JOB_TESTBATCHFILE');

已順利完成 PL/SQL 程序.

SQL>exec dbms_scheduler.drop_schedule(schedule_name => 'SCHEDULE_TESTBATCHFILE');

已順利完成 PL/SQL 程序.

SQL>exec dbms_scheduler.drop_program(program_name => 'testbatchfile');

已順利完成 PL/SQL 程序.