-- ---------------------------------------------------------------------------- -- Created : Marco Gralike, AMIS Services BV -- Purpose : Demonstration of XDB Repository Features -- Version : 1.0 -- ---------------------------------------------------------------------------- -- -- Thanks to: -- -- *) Mark Drake, Senior Product Manager, Oracle XML Technologies, USA, for -- ideas and initial event handling code. -- *) Thomas Baby, Principal Consultant, Oracle XMLDB Development, USA, for -- the initial Java OTN code and support, this PLSQL alternative is based on -- *) Anton Scheffer, Principal Consultant, AMIS Services, for learning me -- once again new stuff regarding LOB handling / code writing. -- -- ---------------------------------------------------------------------------- conn / as sysdba set termout on set feed on set lines 40 set long 10000000 set serveroutput on set echo on -- EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION); BEGIN $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN DBMS_OUTPUT.PUT_LINE('This method is only working on Oracle RDBMS versions 11.1 and onwards (tested on 11.2 XE)'); RAISE_APPLICATION_ERROR(-31061, 'Unsupported Oracle RDBMS version'); $ELSIF DBMS_DB_VERSION.VER_LE_10_2 $THEN DBMS_OUTPUT.PUT_LINE('This method is only working on Oracle RDBMS versions 11.1 and onwards (tested on 11.2 XE)'); RAISE_APPLICATION_ERROR(-31061, 'Unsupported Oracle RDBMS version'); $ELSIF DBMS_DB_VERSION.VER_LE_11_1 $THEN DBMS_OUTPUT.PUT_LINE('Version check: Okay. Tested on 11.2XE though...'); $ELSIF DBMS_DB_VERSION.VER_LE_11_2 $THEN DBMS_OUTPUT.PUT_LINE('Version check: Okay. Tested on 11.2XE though...'); $ELSE DBMS_OUTPUT.PUT_LINE('This method is only working on Oracle RDBMS versions 11.1 and onwards (tested on 11.2 XE)'); RAISE_APPLICATION_ERROR(-31061, 'Unsupported Oracle RDBMS version'); $END END; / -- drop user events cascade; create user events identified by event account unlock; grant dba, xdbadmin to events; -- alter session set current_schema=EVENT; connect events/events DECLARE -- TYPE v_ntt IS TABLE OF VARCHAR2(1024); v_res v_ntt := v_ntt( '/xdb/' , '/xdb/upload/' , '/xdb/res/' , '/xdb/download/' ); -- v_boolean BOOLEAN; v_exist EXCEPTION; -- PRAGMA EXCEPTION_INIT(v_exist, -31003); -- BEGIN -- FOR i IN v_res.FIRST .. v_res.LAST LOOP -- BEGIN v_boolean := DBMS_XDB.CREATEFOLDER(v_res(i)); EXCEPTION WHEN v_exist THEN NULL; END; -- END LOOP; -- COMMIT; -- END; / -- /* ----------------------------------- SELECT path(1) as "Relative Path" FROM RESOURCE_VIEW WHERE under_path(RES, 1, '/xdb', 1) = 1; -- SELECT any_path as "Absolute Path" FROM RESOURCE_VIEW WHERE under_path(RES, '/xdb') = 1; ----------------------------------- */ -- -- ---------------------------------------------------------------------------------------------------- -- -- XFILES.5 XDBPM_ZIP_UTILITIES source aka AS_ZIP/UNZIP by Anton Scheffer, AMIS, The Netherlands -- -- ---------------------------------------------------------------------------------------------------- create or replace type ZIP_CONTENTS_LIST as TABLE of VARCHAR2(4000) / grant execute on ZIP_CONTENTS_LIST to public / create or replace type ZIP_ENTRY force as OBJECT ( FILENAME VARCHAR2(4000), CONTENT BLOB ) / grant execute on ZIP_ENTRY to public / create or replace type ZIP_ENTRY_TABLE as table of ZIP_ENTRY / grant execute on ZIP_ENTRY_TABLE to public / -- ---------------------------------------------------------------------------------------------------- CREATE OR REPLACE package as_zip is /* ** Written by Anton Scheffer ** 3 April 2011 */ -- function get_file_list( p_dir in varchar2 , p_zip_file in varchar2 , p_encoding in varchar2 := null ) return zip_contents_list; -- function get_file_list( p_zipped_blob in blob , p_encoding in varchar2 := null /* Use CP850 for zip files created with a German Winzip to see umlauts, etc */ ) return zip_contents_list; -- function get_file( p_dir in varchar2 , p_zip_file in varchar2 , p_file_name in varchar2 , p_encoding in varchar2 := null ) return blob; -- function get_file( p_zipped_blob in blob , p_file_name in varchar2 , p_encoding in varchar2 := null ) return blob; -- function unzip( p_zipped_blob in blob , p_encoding in varchar2 := null ) return zip_entry_table pipelined; -- function unzip( p_dir in varchar2 , p_zip_file in varchar2 , p_encoding in varchar2 := null ) return zip_entry_table pipelined; -- procedure add1file( p_zipped_blob in out blob , p_name in varchar2 , p_content in blob ); -- procedure finish_zip( p_zipped_blob in out blob ); -- procedure save_zip( p_zipped_blob in blob , p_dir in varchar2 := 'MY_DIR' , p_filename in varchar2 := 'my.zip' ); -- /* declare g_zipped_blob blob; begin as_zip.add1file( g_zipped_blob, 'test1.txt', utl_raw.cast_to_raw( 'Dit is de laatste test! Waarom wordt dit dan niet gecomprimeerd?' ) ); as_zip.add1file( g_zipped_blob, 'test1234.txt', utl_raw.cast_to_raw( 'En hier staat wat anders' ) ); as_zip.finish_zip( g_zipped_blob ); as_zip.save_zip( g_zipped_blob, 'MY_DIR', 'my.zip' ); end; -- declare t_dir varchar2(100) := 'MY_DIR'; t_zip varchar2(100) := 'my.zip'; zip_files zip_contents_list; begin zip_files := as_zip.get_file_list( t_dir, t_zip ); for i in zip_files.first() .. zip_files.last loop dbms_output.put_line( zip_files( i ) ); dbms_output.put_line( utl_raw.cast_to_varchar2( as_zip.get_file( t_dir, t_zip, zip_files( i ) ) ) ); end loop; end; */ end; / show errors -- grant execute on AS_ZIP to public / CREATE OR REPLACE package body as_zip is -- function raw2num( p_value in raw ) return number is begin -- note: FFFFFFFF => -1 return utl_raw.cast_to_binary_integer( p_value , utl_raw.little_endian ); end; -- function file2blob( p_dir in varchar2 , p_file_name in varchar2 ) return blob is file_lob bfile; file_blob blob; begin file_lob := bfilename( p_dir , p_file_name ); dbms_lob.open( file_lob , dbms_lob.file_readonly ); dbms_lob.createtemporary( file_blob , true ); dbms_lob.loadfromfile( file_blob , file_lob , dbms_lob.lobmaxsize ); dbms_lob.close( file_lob ); return file_blob; exception when others then if dbms_lob.isopen( file_lob ) = 1 then dbms_lob.close( file_lob ); end if; if dbms_lob.istemporary( file_blob ) = 1 then dbms_lob.freetemporary( file_blob ); end if; raise; end; -- function raw2varchar2( p_raw in raw , p_encoding in varchar2 ) return varchar2 is begin return nvl ( utl_i18n.raw_to_char( p_raw , p_encoding ) , utl_i18n.raw_to_char ( p_raw , utl_i18n.map_charset( p_encoding , utl_i18n.generic_context , utl_i18n.iana_to_oracle ) ) ); end; -- function get_file_list( p_dir in varchar2 , p_zip_file in varchar2 , p_encoding in varchar2 := null ) return zip_contents_list is begin return get_file_list( file2blob( p_dir , p_zip_file ) , p_encoding ); end; -- function get_file_list( p_zipped_blob in blob , p_encoding in varchar2 := null ) return zip_contents_list is t_ind integer; t_hd_ind integer; t_rv zip_contents_list; begin t_ind := dbms_lob.getlength( p_zipped_blob ) - 21; loop exit when dbms_lob.substr( p_zipped_blob , 4 , t_ind ) = hextoraw( '504B0506' ) or t_ind < 1; t_ind := t_ind - 1; end loop; -- if t_ind <= 0 then return null; end if; -- t_hd_ind := raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_ind + 16 ) ) + 1; t_rv := zip_contents_list(); t_rv.extend( raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_ind + 10 ) ) ); for i in 1 .. raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_ind + 8 ) ) loop t_rv( i ) := raw2varchar2 ( dbms_lob.substr( p_zipped_blob , raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 28 ) ) , t_hd_ind + 46 ) , p_encoding ); t_hd_ind := t_hd_ind + 46 + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 28 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 30 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 32 ) ); end loop; -- return t_rv; end; -- function get_file( p_dir in varchar2 , p_zip_file in varchar2 , p_file_name in varchar2 , p_encoding in varchar2 := null ) return blob is begin return get_file( file2blob( p_dir , p_zip_file ) , p_file_name , p_encoding ); end; -- function get_file( p_zipped_blob in blob , p_file_name in varchar2 , p_encoding in varchar2 := null ) return blob is t_tmp blob; t_ind integer; t_hd_ind integer; t_fl_ind integer; begin t_ind := dbms_lob.getlength( p_zipped_blob ) - 21; loop exit when dbms_lob.substr( p_zipped_blob , 4 , t_ind ) = hextoraw( '504B0506' ) or t_ind < 1; t_ind := t_ind - 1; end loop; -- if t_ind <= 0 then return null; end if; -- t_hd_ind := raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_ind + 16 ) ) + 1; for i in 1 .. raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_ind + 8 ) ) loop if p_file_name = raw2varchar2 ( dbms_lob.substr( p_zipped_blob , raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 28 ) ) , t_hd_ind + 46 ) , p_encoding ) then if dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 10 ) = hextoraw( '0800' ) -- deflate then t_fl_ind := raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_hd_ind + 42 ) ); t_tmp := hextoraw( '1F8B0800000000000003' ); -- gzip header dbms_lob.copy( t_tmp , p_zipped_blob , raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_fl_ind + 19 ) ) , 11 , t_fl_ind + 31 + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_fl_ind + 27 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_fl_ind + 29 ) ) ); dbms_lob.append( t_tmp , dbms_lob.substr( p_zipped_blob , 4 , t_fl_ind + 15 ) ); dbms_lob.append( t_tmp , dbms_lob.substr( p_zipped_blob, 4, t_fl_ind + 23 ) ); return utl_compress.lz_uncompress( t_tmp ); end if; -- if dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 10 ) = hextoraw( '0000' ) -- The file is stored (no compression) then t_fl_ind := raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_hd_ind + 42 ) ); return dbms_lob.substr( p_zipped_blob , raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_fl_ind + 19 ) ) , t_fl_ind + 31 + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_fl_ind + 27 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_fl_ind + 29 ) ) ); end if; end if; t_hd_ind := t_hd_ind + 46 + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 28 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 30 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 32 ) ); end loop; -- return null; end; -- function unzip( p_dir in varchar2 , p_zip_file in varchar2 , p_encoding in varchar2 := null ) return zip_entry_table pipelined is t_zipped_blob blob; cursor getcontent is select value(a) ZIP_ENTRY from table(as_zip.unzip(t_zipped_blob,p_encoding)) a; begin t_zipped_blob := file2blob( p_dir , p_zip_file ); for t in getContent loop pipe row (t.ZIP_ENTRY); end loop; end; -- function unzip( p_zipped_blob in blob , p_encoding in varchar2 := null ) return zip_entry_table pipelined is t_tmp blob; t_ind integer; t_hd_ind integer; t_fl_ind integer; t_file_name varchar2(4000); begin t_ind := dbms_lob.getlength( p_zipped_blob ) - 21; loop exit when dbms_lob.substr( p_zipped_blob , 4 , t_ind ) = hextoraw( '504B0506' ) or t_ind < 1; t_ind := t_ind - 1; end loop; -- if t_ind <= 0 then return; end if; -- t_hd_ind := raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_ind + 16 ) ) + 1; for i in 1 .. raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_ind + 8 ) ) loop t_file_name := raw2varchar2 ( dbms_lob.substr( p_zipped_blob , raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 28 ) ) , t_hd_ind + 46 ) , p_encoding ); if dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 10 ) = hextoraw( '0800' ) -- deflate then t_fl_ind := raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_hd_ind + 42 ) ); t_tmp := hextoraw( '1F8B0800000000000003' ); -- gzip header dbms_lob.copy( t_tmp , p_zipped_blob , raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_fl_ind + 19 ) ) , 11 , t_fl_ind + 31 + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_fl_ind + 27 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_fl_ind + 29 ) ) ); dbms_lob.append( t_tmp , dbms_lob.substr( p_zipped_blob , 4 , t_fl_ind + 15 ) ); dbms_lob.append( t_tmp , dbms_lob.substr( p_zipped_blob, 4, t_fl_ind + 23 ) ); pipe row( zip_entry( t_file_name, utl_compress.lz_uncompress( t_tmp ) ) ); end if; -- if dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 10 ) = hextoraw( '0000' ) -- The file is stored (no compression) then t_fl_ind := raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_hd_ind + 42 ) ); pipe row( zip_entry( t_file_name , dbms_lob.substr( p_zipped_blob , raw2num( dbms_lob.substr( p_zipped_blob , 4 , t_fl_ind + 19 ) ) , t_fl_ind + 31 + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_fl_ind + 27 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_fl_ind + 29 ) ) ) ) ); end if; -- t_hd_ind := t_hd_ind + 46 + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 28 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 30 ) ) + raw2num( dbms_lob.substr( p_zipped_blob , 2 , t_hd_ind + 32 ) ); end loop; -- return; end; -- function little_endian( p_big in number , p_bytes in pls_integer := 4 ) return raw is begin return utl_raw.substr ( utl_raw.cast_from_binary_integer( p_big , utl_raw.little_endian ) , 1 , p_bytes ); end; -- procedure add1file( p_zipped_blob in out blob , p_name in varchar2 , p_content in blob ) is t_now date; t_blob blob; t_clen integer; begin t_now := sysdate; t_blob := utl_compress.lz_compress( p_content ); t_clen := dbms_lob.getlength( t_blob ); if p_zipped_blob is null then dbms_lob.createtemporary( p_zipped_blob , true ); end if; dbms_lob.append ( p_zipped_blob , utl_raw.concat ( hextoraw( '504B0304' ) -- Local file header signature , hextoraw( '1400' ) -- version 2.0 , hextoraw( '0000' ) -- no General purpose bits , hextoraw( '0800' ) -- deflate , little_endian ( to_number( to_char( t_now , 'ss' ) ) / 2 + to_number( to_char( t_now , 'mi' ) ) * 32 + to_number( to_char( t_now , 'hh24' ) ) * 2048 , 2 ) -- File last modification time , little_endian ( to_number( to_char( t_now , 'dd' ) ) + to_number( to_char( t_now , 'mm' ) ) * 32 + ( to_number( to_char( t_now , 'yyyy' ) ) - 1980 ) * 512 , 2 ) -- File last modification date , dbms_lob.substr( t_blob , 4 , t_clen - 7 ) -- CRC-32 , little_endian( t_clen - 18 ) -- compressed size , little_endian( dbms_lob.getlength( p_content ) ) -- uncompressed size , little_endian( length( p_name ) , 2 ) -- File name length , hextoraw( '0000' ) -- Extra field length , utl_raw.cast_to_raw( p_name ) -- File name ) ); dbms_lob.copy( p_zipped_blob , t_blob , t_clen - 18 , dbms_lob.getlength( p_zipped_blob ) + 1 , 11 ); -- compressed content dbms_lob.freetemporary( t_blob ); end; -- procedure finish_zip( p_zipped_blob in out blob ) is t_cnt pls_integer := 0; t_offs integer; t_offs_dir_header integer; t_offs_end_header integer; t_comment raw( 32767 ) := utl_raw.cast_to_raw( 'Implementation by Anton Scheffer' ); begin t_offs_dir_header := dbms_lob.getlength( p_zipped_blob ); t_offs := dbms_lob.instr( p_zipped_blob , hextoraw( '504B0304' ) , 1 ); while t_offs > 0 loop t_cnt := t_cnt + 1; dbms_lob.append ( p_zipped_blob , utl_raw.concat ( hextoraw( '504B0102' ) -- Central directory file header signature , hextoraw( '1400' ) -- version 2.0 , dbms_lob.substr( p_zipped_blob , 26 , t_offs + 4 ) , hextoraw( '0000' ) -- File comment length , hextoraw( '0000' ) -- Disk number where file starts , hextoraw( '0100' ) -- Internal file attributes , hextoraw( '2000B681' ) -- External file attributes , little_endian( t_offs - 1 ) -- Relative offset of local file header , dbms_lob.substr ( p_zipped_blob , utl_raw.cast_to_binary_integer ( dbms_lob.substr( p_zipped_blob , 2 , t_offs + 26 ) , utl_raw.little_endian ) , t_offs + 30 ) -- File name ) ); t_offs := dbms_lob.instr( p_zipped_blob , hextoraw( '504B0304' ) , t_offs + 32 ); end loop; t_offs_end_header := dbms_lob.getlength( p_zipped_blob ); dbms_lob.append ( p_zipped_blob , utl_raw.concat ( hextoraw( '504B0506' ) -- End of central directory signature , hextoraw( '0000' ) -- Number of this disk , hextoraw( '0000' ) -- Disk where central directory starts , little_endian ( t_cnt , 2 ) -- Number of central directory records on this disk , little_endian( t_cnt , 2 ) -- Total number of central directory records , little_endian( t_offs_end_header - t_offs_dir_header ) -- Size of central directory , little_endian ( t_offs_dir_header ) -- Relative offset of local file header , little_endian ( nvl( utl_raw.length( t_comment ) , 0 ) , 2 ) -- ZIP file comment length , t_comment ) ); end; -- procedure save_zip( p_zipped_blob in blob , p_dir in varchar2 := 'MY_DIR' , p_filename in varchar2 := 'my.zip' ) is t_fh utl_file.file_type; t_len pls_integer := 32767; begin t_fh := utl_file.fopen( p_dir , p_filename , 'wb' ); for i in 0 .. trunc( ( dbms_lob.getlength( p_zipped_blob ) - 1 ) / t_len ) loop utl_file.put_raw( t_fh , dbms_lob.substr( p_zipped_blob , t_len , i * t_len + 1 ) ); end loop; utl_file.fclose( t_fh ); end; -- end; / show errors / -- ---------------------------------------------------------------------------------------------------- -- -- -- CREATE PACKAGE and BODY off HANDLE_XDB_EVENTS -- -- -- ---------------------------------------------------------------------------------------------------- create or replace PACKAGE "HANDLE_XDB_EVENTS" AS -- ---------------------------------------------------------------------------- -- Created : Marco Gralike, AMIS Services BV -- Purpose : Central package to demonstrate XDB Event handling. -- Version : 1.0 -- ---------------------------------------------------------------------------- -- BINARY_CONTENT_SCHEMA CONSTANT VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary'; TEXT_CONTENT_SCHEMA CONSTANT VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#text'; -- -- -------------------------------------------- -- OS Utilities -- -------------------------------------------- PROCEDURE CreateBlobFile( p_blob BLOB , p_directory VARCHAR2 , p_filename VARCHAR2 ); -- -- -------------------------------------------- -- Event Handler procedures -- -------------------------------------------- -- -- Create PROCEDURE handlePreCreate( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostCreate( P_EVENT dbms_xevent.XDBRepositoryEvent); -- Delete PROCEDURE handlePreDelete( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostDelete( P_EVENT dbms_xevent.XDBRepositoryEvent); -- Update PROCEDURE handlePreUpdate( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostUpdate( P_EVENT dbms_xevent.XDBRepositoryEvent); -- Lock PROCEDURE handlePreLock( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostLock( P_EVENT dbms_xevent.XDBRepositoryEvent); -- Unlock PROCEDURE handlePreUnlock( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostUnlock( P_EVENT dbms_xevent.XDBRepositoryEvent); -- LinkIn PROCEDURE handlePreLinkIn( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostLinkIn( P_EVENT dbms_xevent.XDBRepositoryEvent); -- LinkTo PROCEDURE handlePreLinkTo( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostLinkTo( P_EVENT dbms_xevent.XDBRepositoryEvent); -- UnlinkIn PROCEDURE handlePreUnlinkIn( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostUnlinkIn( P_EVENT dbms_xevent.XDBRepositoryEvent); -- UnlinkFrom PROCEDURE handlePreUnlinkFrom( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostUnlinkFrom( P_EVENT dbms_xevent.XDBRepositoryEvent); -- CheckIn PROCEDURE handlePreCheckIn( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostCheckIn( P_EVENT dbms_xevent.XDBRepositoryEvent); -- CheckOut PROCEDURE handlePreCheckOut( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostCheckOut( P_EVENT dbms_xevent.XDBRepositoryEvent); -- UncheckOut PROCEDURE handlePreUncheckOut( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostUncheckOut( P_EVENT dbms_xevent.XDBRepositoryEvent); -- VersionControl PROCEDURE handlePreVersionControl( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostVersionControl( P_EVENT dbms_xevent.XDBRepositoryEvent); -- Render PROCEDURE handleRender( P_EVENT dbms_xevent.XDBRepositoryEvent); -- NFS Open PROCEDURE handlePreOpen( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostOpen( P_EVENT dbms_xevent.XDBRepositoryEvent); -- NFS Inconsistant Update PROCEDURE handlePreInConUpdate( P_EVENT dbms_xevent.XDBRepositoryEvent); PROCEDURE handlePostInConUpdate( P_EVENT dbms_xevent.XDBRepositoryEvent); -- END HANDLE_XDB_EVENTS; / -- create or replace PACKAGE BODY HANDLE_XDB_EVENTS AS -- ---------------------------------------------------------------------------- -- Created : Marco Gralike, AMIS Services BV -- Purpose : Central package to demonstrate XDB Event handling. -- Version : 1.0 -- ---------------------------------------------------------------------------- -- -- Thanks to: -- -- *) Mark Drake, Senior Product Manager, Oracle XML Technologies, USA, for -- ideas and initial event handling code. -- *) Thomas Baby, Principal Consultant, Oracle XMLDB Development, USA, for -- the initial Java code this code is based on and support -- *) Anton Scheffer, Principal Consultant, AMIS Services, for learning me -- once again new stuff regarding LOB handling / code writing. -- -- ---------------------------------------------------------------------------- -- -- handleMain -- PROCEDURE handleMain( P_EVENT dbms_xevent.XDBRepositoryEvent) AS -- BINARY_CONTENT_SCHEMA CONSTANT VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary'; TEXT_CONTENT_SCHEMA CONSTANT VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#text'; -- v_xdb_resource DBMS_XDBRESOURCE.XDBResource; -- v_resource_path VARCHAR2(700); v_event_type NUMBER; v_event_name VARCHAR2(32); v_filename VARCHAR2(128); v_directory ALL_DIRECTORIES.DIRECTORY_NAME%TYPE; v_blob_content BLOB; -- BEGIN -- ------------------------------------------ -- Get full repository path and event triggered -- ------------------------------------------ v_resource_path := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT)); v_event_type := DBMS_XEVENT.getEvent(DBMS_XEVENT.getXDBEvent(P_EVENT)); -- ------------------------------------------ -- Associate fired event with action -- ------------------------------------------ CASE V_EVENT_TYPE WHEN dbms_xevent.RENDER_EVENT THEN V_EVENT_NAME := 'Render'; WHEN dbms_xevent.PRE_CREATE_EVENT THEN V_EVENT_NAME := 'PreCreate'; WHEN dbms_xevent.POST_CREATE_EVENT THEN V_EVENT_NAME := 'PostCreate'; WHEN dbms_xevent.PRE_DELETE_EVENT THEN V_EVENT_NAME := 'PreDelete'; WHEN dbms_xevent.POST_DELETE_EVENT THEN V_EVENT_NAME := 'PostDelete'; WHEN dbms_xevent.PRE_UPDATE_EVENT THEN V_EVENT_NAME := 'PreUpdate'; WHEN dbms_xevent.POST_UPDATE_EVENT THEN V_EVENT_NAME := 'PostUpdate'; WHEN dbms_xevent.PRE_LOCK_EVENT THEN V_EVENT_NAME := 'PreLock'; WHEN dbms_xevent.POST_LOCK_EVENT THEN V_EVENT_NAME := 'PostLock'; WHEN dbms_xevent.PRE_UNLOCK_EVENT THEN V_EVENT_NAME := 'PreUnlock'; WHEN dbms_xevent.POST_UNLOCK_EVENT THEN V_EVENT_NAME := 'PostUnlock'; WHEN dbms_xevent.PRE_LINKIN_EVENT THEN V_EVENT_NAME := 'PreLinkIn'; WHEN dbms_xevent.POST_LINKIN_EVENT THEN -- V_EVENT_NAME := 'PostLinkIn'; -- insert into docstore (filename, zipfilename, xmlcontent) select v_resource_path , filename , xmltype(content,NLS_CHARSET_ID('AL32UTF8')) as xmlcontent from table(as_zip.unzip((xdburitype(v_resource_path).getBLOB()))) where filename = 'word/document.xml'; -- WHEN dbms_xevent.PRE_LINKTO_EVENT THEN V_EVENT_NAME := 'PreLinkTo'; WHEN dbms_xevent.POST_LINKTO_EVENT THEN V_EVENT_NAME := 'PostLinkTo'; WHEN dbms_xevent.PRE_UNLINKIN_EVENT THEN V_EVENT_NAME := 'PreUnlinkIn'; WHEN dbms_xevent.POST_UNLINKIN_EVENT THEN V_EVENT_NAME := 'PostUnlinkIn'; WHEN dbms_xevent.PRE_UNLINKFROM_EVENT THEN V_EVENT_NAME := 'PreUnlinkFrom'; WHEN dbms_xevent.POST_UNLINKFROM_EVENT THEN V_EVENT_NAME := 'PostUnlinkFrom'; WHEN dbms_xevent.PRE_CHECKIN_EVENT THEN V_EVENT_NAME := 'PreCheckIn'; WHEN dbms_xevent.POST_CHECKIN_EVENT THEN V_EVENT_NAME := 'PostCheckIn'; WHEN dbms_xevent.PRE_CHECKOUT_EVENT THEN V_EVENT_NAME := 'PreCheckOut'; WHEN dbms_xevent.POST_CHECKOUT_EVENT THEN V_EVENT_NAME := 'PostCheckOut'; WHEN dbms_xevent.PRE_UNCHECKOUT_EVENT THEN V_EVENT_NAME := 'PreUncheckOut'; WHEN dbms_xevent.POST_UNCHECKOUT_EVENT THEN V_EVENT_NAME := 'PostUncheckOut'; WHEN dbms_xevent.PRE_VERSIONCONTROL_EVENT THEN V_EVENT_NAME := 'PreVersionControl'; WHEN dbms_xevent.POST_VERSIONCONTROL_EVENT THEN V_EVENT_NAME := 'PostVersionControl'; WHEN dbms_xevent.PRE_OPEN_EVENT THEN V_EVENT_NAME := 'PreOpen'; WHEN dbms_xevent.POST_OPEN_EVENT THEN V_EVENT_NAME := 'PostOpen'; WHEN dbms_xevent.PRE_INCONSISTENTUPDATE_EVENT THEN V_EVENT_NAME := 'PreInconsistentUpdate'; WHEN dbms_xevent.POST_INCONSISTENTUPDATE_EVENT THEN V_EVENT_NAME := 'PostInconsistentUpdate'; ELSE V_EVENT_NAME := 'UnknownEvent'; END CASE; -- END handleMain; -- ----------------------------- -- CreateBlobFile -- ----------------------------- PROCEDURE CreateBlobFile( p_blob BLOB , p_directory VARCHAR2 , p_filename VARCHAR2 ) IS t_filehandle UTL_FILE.FILE_TYPE; t_length pls_integer := 32767; BEGIN -- t_filehandle := UTL_FILE.FOPEN( p_directory, p_filename, 'wb' ); -- FOR i IN 0 .. TRUNC( ( DBMS_LOB.GETLENGTH( p_blob ) - 1 ) / t_length ) LOOP UTL_FILE.PUT_RAW( t_filehandle, DBMS_LOB.SUBSTR( p_blob, t_length, i * t_length + 1 ) ); END LOOP; -- UTL_FILE.FCLOSE( t_filehandle ); -- END CreateBlobFile; -- ----------------------------- -- handlePreCreate -- ----------------------------- PROCEDURE handlePreCreate( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreCreate; -- ----------------------------- -- handlePostCreate -- ----------------------------- PROCEDURE handlePostCreate( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostCreate; -- ----------------------------- -- handlePreDelete -- ----------------------------- PROCEDURE handlePreDelete( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreDelete; -- ----------------------------- -- handlePostCreate -- ----------------------------- PROCEDURE handlePostDelete( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostDelete; -- ----------------------------- -- handlePreUpdate -- ----------------------------- PROCEDURE handlePreUpdate( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreUpdate; -- ----------------------------- -- handlePostUpdate -- ----------------------------- PROCEDURE handlePostUpdate( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostUpdate; -- ----------------------------- -- handlePreLock -- ----------------------------- PROCEDURE handlePreLock( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreLock; -- ----------------------------- -- handlePostLock -- ----------------------------- PROCEDURE handlePostLock( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostLock; -- ----------------------------- -- handlePreUnlock -- ----------------------------- PROCEDURE handlePreUnlock( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreUnlock; -- ----------------------------- -- handlePostUnlock -- ----------------------------- PROCEDURE handlePostUnlock( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostUnlock; -- ----------------------------- -- handlePreLinkIn -- ----------------------------- PROCEDURE handlePreLinkIn( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreLinkIn; -- ----------------------------- -- handlePostLinkIn -- ----------------------------- PROCEDURE handlePostLinkIn( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostLinkIn; -- ----------------------------- -- handlePreLinkTo -- ----------------------------- PROCEDURE handlePreLinkTo( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreLinkTo; -- ----------------------------- -- handlePostLinkTo -- ----------------------------- PROCEDURE handlePostLinkTo( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostLinkTo; -- ----------------------------- -- handlePreUnlinkIn -- ----------------------------- PROCEDURE handlePreUnlinkIn( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreUnlinkIn; -- ----------------------------- -- handlePostUnlinkIn -- ----------------------------- PROCEDURE handlePostUnlinkIn( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostUnlinkIn; -- ----------------------------- -- handlePreUnlinkFrom -- ----------------------------- PROCEDURE handlePreUnlinkFrom( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreUnlinkFrom; -- ----------------------------- -- handlePostUnlinkFrom -- ----------------------------- PROCEDURE handlePostUnlinkFrom( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostUnlinkFrom; -- ----------------------------- -- handlePreCheckIn -- ----------------------------- PROCEDURE handlePreCheckIn( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreCheckIn; -- ----------------------------- -- handlePostCheckIn -- ----------------------------- PROCEDURE handlePostCheckIn( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostCheckIn; -- -- handlePreCheckOut -- PROCEDURE handlePreCheckOut( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreCheckOut; -- ----------------------------- -- handlePostCheckOut -- ----------------------------- PROCEDURE handlePostCheckOut( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostCheckOut; -- -- handlePreUncheckOut -- PROCEDURE handlePreUncheckOut( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreUncheckOut; -- ----------------------------- -- handlePostUncheckOut -- ----------------------------- PROCEDURE handlePostUncheckOut( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostUncheckOut; -- -- handlePreVersionControl -- PROCEDURE handlePreVersionControl( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreVersionControl; -- ----------------------------- -- handlePostVersionControl -- ----------------------------- PROCEDURE handlePostVersionControl( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostVersionControl; -- -- handleRender -- PROCEDURE handleRender( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handleRender; -- ----------------------------- -- handlePreOpen -- ----------------------------- PROCEDURE handlePreOpen( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreOpen; -- -- handlePostOpen -- PROCEDURE handlePostOpen( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostOpen; -- ----------------------------- -- handlePreInConUpdate -- ----------------------------- PROCEDURE handlePreInConUpdate( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePreInConUpdate; -- ----------------------------- -- handlePostInConUpdate -- ----------------------------- PROCEDURE handlePostInConUpdate( P_EVENT dbms_xevent.XDBRepositoryEvent) AS BEGIN -- handleMain(P_EVENT); -- END handlePostInConUpdate; -- END HANDLE_XDB_EVENTS; / -- ---------------------------------------------------------------------------------------------------- -- -- End PL/SQL section -- -- ---------------------------------------------------------------------------------------------------- DECLARE b BOOLEAN := FALSE; BEGIN b := DBMS_XDB.createResource( '/xdb/res/UploadHandling.xml', ' OOXML Application EVENTS HANDLE_XDB_EVENTS PL/SQL /xdb/res/UploadHandling.xml '); END; / -- BEGIN DBMS_RESCONFIG.appendResConfig( '/xdb/upload', '/xdb/res/UploadHandling.xml', DBMS_RESCONFIG.APPEND_RESOURCE); END; / -- commit; -- /* ----------------------------------- SELECT any_path as "Absolute Path" FROM RESOURCE_VIEW WHERE under_path(RES, '/xdb/res') = 1; -- select xdburitype('/xdb/res/UploadHandling.xml').getCLOB() from dual; -- select DBMS_RESCONFIG.GETLISTENERS('/xdb/upload') from dual; -- select DBMS_RESCONFIG.GETLISTENERS('/xdb/upload').getCLOBVal() from dual; -- select xmlserialize(DOCUMENT xt.val as CLOB INDENT SIZE = 2 ) as RESOURCE_CONFIGFILE FROM XMLTABLE (xmlnamespaces(default 'http://xmlns.oracle.com/xdb/XDBResConfig.xsd') , '/event-listeners/listener' PASSING (DBMS_RESCONFIG.GETLISTENERS('/xdb/upload')) COLUMNS val XMLTYPE PATH '.' , schema VARCHAR2(30) PATH 'schema' ) xt WHERE xt.schema = user; -- select xt.schema , xt.source , xt.description FROM XMLTABLE (xmlnamespaces(default 'http://xmlns.oracle.com/xdb/XDBResConfig.xsd') , '/event-listeners/listener' PASSING (DBMS_RESCONFIG.GETLISTENERS('/xdb/upload')) COLUMNS schema VARCHAR2(30) PATH 'schema' , source VARCHAR2(30) PATH 'source' , description VARCHAR2(100) PATH 'description' ) xt WHERE xt.schema = user; -- -- select xt.schema , xt.eventlistener , xt.language , xe.roworder , xe.event FROM XMLTABLE (xmlnamespaces(default 'http://xmlns.oracle.com/xdb/XDBResConfig.xsd') , '/event-listeners/listener' PASSING (DBMS_RESCONFIG.GETLISTENERS('/xdb/upload')) COLUMNS schema VARCHAR2(30) PATH 'schema' , eventlistener VARCHAR2(30) PATH 'source' , language VARCHAR2(10) PATH 'language' , childevents XMLTYPE PATH 'events' ) xt , XMLTABLE (xmlnamespaces(default 'http://xmlns.oracle.com/xdb/XDBResConfig.xsd') , '/events/*' PASSING xt.childevents COLUMNS roworder FOR ORDINALITY , event VARCHAR2(25) PATH 'fn:local-name(.)' ) xe WHERE xt.schema = user; -- SCHEMA EVENTLISTENER LANGUAGE ROWNUMBER EVENT ------- ----------------- -------- --------- ----------- EVENTS HANDLE_XDB_EVENTS PL/SQL 1 Post-LinkIn EVENTS HANDLE_XDB_EVENTS PL/SQL 2 Post-Update EVENTS HANDLE_XDB_EVENTS PL/SQL 3 Render -- -- select xt.source as "LISTENER_SOURCE" , xt.language as "LISTENER_LANGUAGE" , xt.invoker as "SET_INVOKER" , xt.enabled as "RESCONFIG_ENABLED" FROM XMLTABLE (xmlnamespaces(default 'http://xmlns.oracle.com/xdb/XDBResConfig.xsd') , '.' PASSING xmltype((xdburitype('/xdb/res/UploadHandling.xml').getCLOB())) COLUMNS enabled VARCHAR2(30) PATH '@enable' , invoker VARCHAR2(30) PATH 'event-listeners/@set-invoker' , language VARCHAR2(10) PATH 'event-listeners/listener/language' , source VARCHAR2(30) PATH 'event-listeners/listener/source' -- , childevents XMLTYPE PATH '*' ) xt; -- LISTENER_SOURCE LISTENER_LANGUAGE SET_INVOKER RESCONFIG_ENABLED ----------------- ----------------- ----------- ----------------- HANDLE_XDB_EVENTS PL/SQL false true -- -- ----------------------------------- */ CREATE TABLE TAB_XMLDA OF XMLTYPE TABLESPACE USERS XMLTYPE STORE AS SECUREFILE BINARY XML / CREATE TABLE DOCSTORE ( filename VARCHAR2(4000) , zipfilename VARCHAR2(4000) , xmlcontent XMLTYPE) TABLESPACE USERS XMLTYPE xmlcontent STORE AS SECUREFILE BINARY XML / CREATE INDEX DOCSTORE_XMLINDEX ON DOCSTORE(xmlcontent) INDEXTYPE IS XDB.XMLINDEX PARAMETERS ('PATHS( INCLUDE( /w:document/w:body//w:sdt //w:tbl ) NAMESPACE MAPPING (xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"))' ) / -- -- drag & drop for example Windows document BM123Manual.docx in /xdb/download -- /* Package AS_ZIP has (overloaded) function "unzip" with function unzip( p_zipped_blob in blob , p_encoding in varchar2 := null ) return ZIP_ENTRY_TABLE pipelined; desc ZIP_ENTRY_TABLE user type definition ------------------------------------------ type ZIP_ENTRY_TABLE as table of ZIP_ENTRY desc ZIP_ENTRY user type definition ------------------------------ type ZIP_ENTRY force as OBJECT ( FILENAME VARCHAR2(4000), CONTENT BLOB ) */ select filename, content from table(as_zip.unzip((xdburitype('/xdb/download/BM123Manual.docx').getBLOB()))); -- select filename, content from table(as_zip.unzip((xdburitype('/xdb/download/BM123Manual.docx').getBLOB()))) where filename = 'word/document.xml'; -- select filename , xmltype(content,NLS_CHARSET_ID('AL32UTF8')) as DOCX from table(as_zip.unzip((xdburitype('/xdb/download/BM123Manual.docx').getBLOB()))) where filename = 'word/document.xml'; -- select extract((xmltype(content,NLS_CHARSET_ID('AL32UTF8'))) , '//w:t' , 'xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"' ) as "docx" from table(as_zip.unzip((xdburitype('/xdb/download/BM123Manual.docx').getBLOB()))) where filename = 'word/document.xml'; -- select extract((xmltype(content,NLS_CHARSET_ID('AL32UTF8'))) , '//w:t/text()' , 'xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"' ) as "docx" from table(as_zip.unzip((xdburitype('/xdb/download/BM123Manual.docx').getBLOB()))) where filename = 'word/document.xml'; -- -- PRINT ALL AVAILABLE TAGS -- SELECT DISTINCT(BOOK_INFO.TAG) FROM (select xmltype(content,NLS_CHARSET_ID('AL32UTF8')) as OBJECT_VALUE from table(as_zip.unzip((xdburitype('/xdb/download/BM123Manual.docx').getBLOB()))) -- where filename = 'word/document.xml' ) BOOKS , XMLTABLE(XMLNAMESPACES( 'http://schemas.openxmlformats.org/wordprocessingml/2006/main' AS "w"), '/w:document/w:body//w:sdt' PASSING BOOKS.OBJECT_VALUE COLUMNS TAG VARCHAR2(100) PATH '/w:sdt/w:sdtPr/w:tag/@w:val' ) BOOK_INFO; -- -- FIND TITLE OF ALL BOOKS IN THE "Technical Spec" CATEGORY -- VAR CATEGORY VARCHAR2(20); EXECUTE :CATEGORY := 'Technical Spec'; SELECT XMLQUERY('declare namespace w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"; /w:document/w:body//w:sdt[w:sdtPr/w:tag/@w:val="Title"]/w:sdtContent//w:r//w:t//text()' PASSING BOOKS.OBJECT_VALUE RETURNING CONTENT) TITLE FROM (select xmltype(content,NLS_CHARSET_ID('AL32UTF8')) as OBJECT_VALUE from table(as_zip.unzip((xdburitype('/xdb/download/BM123Manual.docx').getBLOB()))) -- where filename = 'word/document.xml' ) BOOKS WHERE XMLEXISTS('declare namespace w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"; /w:document/w:body//w:sdt[w:sdtPr/w:tag/@w:val="Category" and w:sdtContent//w:r//w:t//text()=$CATEGORY]' PASSING BOOKS.OBJECT_VALUE, :CATEGORY AS "CATEGORY"); / -- -- RETURN BOOK TITLE, AUTHORS AND CATEGORY IN XML FORMAT -- SELECT XMLQUERY('declare namespace w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"; {$BOOK//w:document/w:body//w:sdt[w:sdtPr/w:tag/@w:val="Title"]/w:sdtContent//w:r//w:t//text()} {$BOOK//w:document/w:body//w:sdt[w:sdtPr/w:tag/@w:val="Author"]/w:sdtContent//w:r//w:t//text()} {$BOOK//w:document/w:body//w:sdt[w:sdtPr/w:tag/@w:val="Category"]/w:sdtContent//w:r//w:t//text()} ' PASSING BOOKS.OBJECT_VALUE AS "BOOK" RETURNING CONTENT) RESULT FROM (select xmltype(content,NLS_CHARSET_ID('AL32UTF8')) as OBJECT_VALUE from table(as_zip.unzip((xdburitype('/xdb/download/BM123Manual.docx').getBLOB()))) where filename = 'word/document.xml' ) BOOKS ; -- select * from docstore; --