/*** CREATE TEMP TABLE ***/
CREATE TABLE REF_CITY_RECORD(
ACTION VARCHAR2(1),
CODE_AREA VARCHAR2(100),
CODE_LOC VARCHAR2(50),
CODE_CITY VARCHAR2(100),
CITY_DESC VARCHAR2(100),
CITY_DESC_BIG5 VARCHAR2(100),
STATUS VARCHAR2(10));
CREATE TABLE REF_ANNUAL_TURNOVER_RECORD(
ACTION VARCHAR2(1),
CODE_ANNUAL_TURNOVER VARCHAR2(10),
ANNUAL_TURNOVER_DESC VARCHAR2(50),
ANNUAL_TURNOVER_DESC_BIG5 VARCHAR2(100),
STATUS VARCHAR2(100));
/**** END ****/
DECLARE
QRYCTX DBMS_XMLGEN.CTXHANDLE;
dst_loc CLOB;
SAVCTX DBMS_XMLSAVE.CTXTYPE;
N NUMBER;
src_loc bfile := bfilename('UTL_FILE_DIR', '20080421170247_000004.xml');
-- DATES_01_REFEREENCE_20080421170247_000004.XML SHOULD BE PLACE UTL_FILE_DIRECTORY
amt number := dbms_lob.lobmaxsize;
src_offset number := 1;
dst_offset number := 1;
lang_ctx number := dbms_lob.default_lang_ctx;
warning number;
v_exists PLS_INTEGER := 0;
--v_rowtag VARCHAR2(30) := 'REF_CITY_RECORD';
BEGIN
DBMS_LOB.CREATETEMPORARY(dst_loc, true);
DBMS_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE ------------');
dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
v_exists := DBMS_LOB.fileexists(src_loc);
IF v_exists = 0 THEN
dbms_output.put_line('FILE NOT EXIST');
ELSE
dbms_output.put_line('FILE AND DIR VALID');
END IF;
dbms_lob.LOADCLOBFROMFILE(dst_loc,
src_loc,
amt,
dst_offset,
src_offset,
dbms_lob.default_csid,
lang_ctx,
warning);
dbms_output.put_line(' Amount specified ' || amt);
dbms_output.put_line(' Number of bytes read from source: ' ||
(src_offset - 1));
dbms_output.put_line(' Number of characters written to destination: ' ||
(dst_offset - 1));
if (warning = dbms_lob.warn_inconvertible_char) then
dbms_output.put_line('Warning: Inconvertible character');
end if;
dbms_lob.filecloseall();
-- DBMS_OUTPUT.PUT_LINE(dst_loc);
SAVCTX := DBMS_XMLSAVE.NEWCONTEXT('REF_CITY_RECORD');
DBMS_XMLSAVE.CLEARUPDATECOLUMNLIST(SAVCTX);
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'ACTION');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CODE_AREA');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CODE_LOC');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CODE_CITY');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CITY_DESC');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CITY_DESC_BIG5');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'STATUS');
DBMS_XMLSAVE.setRowTag(SAVCTX, 'REF_CITY');
--DBMS_XMLSave.setDateFormat(SAVCTX, 'dd/MM/yyyy HH24:mm:ss');
N := DBMS_XMLSAVE.INSERTXML(SAVCTX, dst_loc);
DBMS_OUTPUT.PUT_LINE('REF_CITY_RECORD RECORD: ' ||N);
SAVCTX := DBMS_XMLSAVE.NEWCONTEXT('REF_ANNUAL_TURNOVER_RECORD');
DBMS_XMLSAVE.CLEARUPDATECOLUMNLIST(SAVCTX);
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'ACTION');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'CODE_ANNUAL_TURNOVER');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'ANNUAL_TURNOVER_DESC');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'ANNUAL_TURNOVER_DESC_BIG5');
DBMS_XMLSAVE.SETUPDATECOLUMN(SAVCTX, 'STATUS');
DBMS_XMLSAVE.setRowTag(SAVCTX, 'REF_ANNUAL_TURNOVER');
N := DBMS_XMLSAVE.INSERTXML(SAVCTX, dst_loc);
DBMS_OUTPUT.PUT_LINE('REF_ANNUAL_TURNOVER_RECORD RECORD : ' ||N);
DBMS_XMLSAVE.CLOSECONTEXT(SAVCTX);
COMMIT;
exception when others then
rollback;
dbms_output.put_line(substr(sqlerrm,1,200));
END;
/