-- ----------------------------------------------------------------------------
-- 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
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;
--