-- WARNING : Tests should be performed by OJUTILS schema
-- 1. Go to a directory where you have a sample of files (binary, text based, xml), in my case i will set my oracle-jutils
--documentation, just replace mine with yours :
-- cd $OJUTILS_HOME

-- 2. Perform the grants :
conn / as sysdba
sho user
exec dbms_java.grant_permission('OJUTILS', 'java.io.FilePermission','D:\myopensource\oracle-jutils\doc\*','write, read, delete');
commit;

conn OJUTILS/OJUTILS
sho user

set serverout on size unlimited;
exec dbms_java.set_output(2000000);

PROMPT Create test tables
drop table T_BLOBS_TEST purge;
create table T_BLOBS_TEST(
	FILENAME NVARCHAR2(50),
	CRC32 VARCHAR2(8),
	CONTENTS BLOB
)
/

drop table T_CLOBS_TEST purge
/
create table T_CLOBS_TEST(
	FILENAME NVARCHAR2(50),
	CRC32 VARCHAR2(8),
	CONTENTS CLOB
)
/

drop table T_XMLTYPE_TEST purge
/
create table T_XMLTYPE_TEST(
	FILENAME NVARCHAR2(50),
	CRC32 VARCHAR2(8),
	CONTENTS XMLTYPE
)
/

PROMPT ##################################################""
PROMPT Test BLOBs functions
truncate table T_BLOBS_TEST;
purge recyclebin;
insert into T_BLOBS_TEST(FILENAME, CONTENTS) values ('oracle-jutils.xml', JDBMS_LOB.FILE_TO_BLOB('D:\myopensource\oracle-jutils\doc', 'oracle-jutils.xml'));
insert into T_BLOBS_TEST(FILENAME, CONTENTS) values ('oracle-jutils.xml.chm', JDBMS_LOB.FILE_TO_BLOB('D:\myopensource\oracle-jutils\doc', 'oracle-jutils.xml.chm'));
insert into T_BLOBS_TEST(FILENAME, CONTENTS) values ('oracle-jutils.xml.pdf', JDBMS_LOB.FILE_TO_BLOB('D:\myopensource\oracle-jutils\doc', 'oracle-jutils.xml.pdf'));
insert into T_BLOBS_TEST(FILENAME, CONTENTS) values ('oracle-jutils.xml.ps', JDBMS_LOB.FILE_TO_BLOB('D:\myopensource\oracle-jutils\doc', 'oracle-jutils.xml.ps'));
update T_BLOBS_TEST set CRC32 = JDBMS_CHECKSUM.GET_CRC32(CONTENTS);
commit;

select FILENAME, length(CONTENTS), CRC32 from T_BLOBS_TEST;


PROMPT Test CLOBs functions
insert into T_CLOBS_TEST(FILENAME, CONTENTS) values ('oracle-jutils.xml', JDBMS_LOB.FILE_TO_CLOB('D:\myopensource\oracle-jutils\doc', 'oracle-jutils.xml'));
commit;

select FILENAME, length(CONTENTS), CRC32 from T_CLOBS_TEST;

PROMPT Test XMLTYPEs functions
insert into T_XMLTYPE_TEST(FILENAME, CONTENTS) values ('oracle-jutils.xml', JDBMS_LOB.FILE_TO_XMLTYPE('D:\myopensource\oracle-jutils\doc', 'oracle-jutils.xml'));
commit;


PROMPT Bring back all T_BLOBS_TEST BLOBs to O/S filesystem
declare
	l_FILENAME		T_BLOBS_TEST.filename%TYPE;
	l_DUMP_FILENAME	T_BLOBS_TEST.filename%TYPE;
	l_BLOB			BLOB;
	cursor blob_cursor is
		select filename, contents
		from T_BLOBS_TEST for update;
begin
	open blob_cursor;
	loop
		fetch blob_cursor into l_FILENAME, l_BLOB;
		l_DUMP_FILENAME := l_FILENAME || '_from_blob_';
		dbms_output.put_line('Parsing BLOB with filename : ' || l_FILENAME);
		JDBMS_LOB.BLOB_TO_FILE(l_BLOB, 'D:\myopensource\oracle-jutils\doc',
			'dmp_' || l_FILENAME, 	-- pre-append dmp pattern so you can make the difference between dumped versions and original versions
			'');					-- Don't need to put an extension as i do have the inital filename kept in the table
		exit when blob_cursor%NOTFOUND;
	end loop;
	close blob_cursor;
	EXCEPTION
		WHEN OTHERS THEN
		RAISE;
	commit;
end;
/
commit;


PROMPT Bring back all T_CLOBS_TEST CLOBs to O/S filesystem
declare
	l_FILENAME		T_CLOBS_TEST.filename%TYPE;
	l_DUMP_FILENAME	T_CLOBS_TEST.filename%TYPE;
	l_CLOB			CLOB;
	cursor clob_cursor is
		select filename, contents
		from T_CLOBS_TEST for update;
begin
	open clob_cursor;
	loop
		fetch clob_cursor into l_FILENAME, l_CLOB;
		l_DUMP_FILENAME := l_FILENAME || '_from_clob_';
		dbms_output.put_line('Parsing CLOB with filename : ' || l_FILENAME);
		JDBMS_LOB.CLOB_TO_FILE(l_CLOB, 'D:\myopensource\oracle-jutils\doc',
			'dmp_' || l_FILENAME, 	-- pre-append dmp pattern so you can make the difference between dumped versions and original versions
			'');					-- Don't need to put an extension as i do have the inital filename kept in the table
		exit when clob_cursor%NOTFOUND;
	end loop;
	close clob_cursor;
	EXCEPTION
		WHEN OTHERS THEN
		RAISE;
	commit;
end;
/
commit;

PROMPT Bring back all T_XMLTYPE_TEST XMLTYPEs to O/S filesystem
declare
	l_FILENAME		T_XMLTYPE_TEST.filename%TYPE;
	l_DUMP_FILENAME	T_XMLTYPE_TEST.filename%TYPE;
	l_XML			XMLTYPE;
	cursor xml_cursor is
		select filename, contents
		from T_XMLTYPE_TEST for update;
begin
	open xml_cursor;
	loop
		fetch xml_cursor into l_FILENAME, l_XML;
		l_DUMP_FILENAME := l_FILENAME || '_from_xml_';
		dbms_output.put_line('Parsing XMLTYPE with filename : ' || l_FILENAME);

		JDBMS_LOB.XMLTYPE_TO_FILE(l_XML, 'D:\myopensource\oracle-jutils\doc',
			'xmltype_dmp_' || l_FILENAME);					-- Don't need to put an extension as i do have the inital filename kept in the table
		exit when xml_cursor%NOTFOUND;
	end loop;
	close xml_cursor;
	EXCEPTION
		WHEN OTHERS THEN
		RAISE;
	commit;
end;
/
commit;




