1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
|
conn user/user@base
drop table test_xml;
create table test_xml (
nb1 number,
chps1 varchar2(5),
chps2 varchar2(5),
chps3 varchar2(5),
nb2 number,
DAT1 date,
chps4 varchar2(5));
CREATE OR REPLACE procedure insert_xml( p_directory in varchar2,
p_filename in varchar2,
vtableName in varchar2 )
AS
v_filelocator bfile;
v_cloblocator clob;
l_ctx DBMS_XMLSAVE.ctxType;
insCtx DBMS_XMLSave.ctxType;
l_rows number;
BEGIN
DBMS_LOB.createtemporary( v_cloblocator, true );
v_filelocator := bfilename( p_directory, p_filename );
DBMS_LOB.open( v_filelocator, dbms_lob.file_readonly );
DBMS_LOB.loadfromfile( v_cloblocator, v_filelocator, dbms_lob.getlength(
v_filelocator ) );
l_ctx := DBMS_XMLSAVE.newContext( vTableName );
DBMS_XMLSave.setDateFormat(insCtx, 'dd/MM/yyyy HH:mm:ss');
l_rows := DBMS_XMLSAVE.insertxml( l_ctx, v_cloblocator );
DBMS_XMLSAVE.closeContext( l_ctx );
DBMS_OUTPUT.put_line( l_rows || ' rows inserted...');
DBMS_LOB.close( v_filelocator );
DBMS_LOB.FREETEMPORARY( v_cloblocator );
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.filecloseall;
raise;
END insert_xml;
/
CREATE OR REPLACE DIRECTORY path AS '/partage';
exec insert_xml( 'PATH','xml2.xml','test_xml');
commit;
select * from test_xml; |
Partager