/*
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 程序.
