DBMS_LOB.READ access CLOB xml

DBMS_LOB.READ, REGEXP_REPLACE 读取CLOB里的信息.[@more@]

set serveroutput on;
SET LINESIZE 150
spool /tmp/info_cache_report.csv;
DECLARE
v_clob CLOB;
v_data VARCHAR2(1000);
v_buffer INTEGER;
v_audit_string VARCHAR2(1000);
v_audit_date DATE;

v_audit_version VARCHAR2(1000);
v_trade_type VARCHAR2(1000);
v_sub_type VARCHAR2(1000);

BEGIN
DBMS_OUTPUT.PUT_LINE('QUEUE_info_ID,info_IDENTIFIER,AUDIT_VERSION,TRADE_TYPE,EXTRACTED_IND,COB_DATE,AUDIT_TIMESTAMP,ENTRY_TIMESTAMP,info_SIZE,DELAY_SECONDS');

for i in (select * from info_CACHE_info where queue_id=53 and entry_date_time > trunc(sysdate) order by 1)

loop
v_audit_string := '';
v_clob := null;
v_buffer := 1000;
v_clob := i.info_CONTENT;
DBMS_LOB.READ(v_clob, v_buffer,1, v_data);
select REGEXP_REPLACE(v_data,'<?xml version="1.0" encoding="UTF-8"?>','') into v_data from dual;
select replace(v_data,chr(13)||chr(10),'') into v_data from dual;
select REGEXP_REPLACE(v_data,'.*(.*).*','1') into v_audit_string from dual;

select REGEXP_REPLACE(v_data,'.*(.*).*','1') into v_audit_version from dual;
select REGEXP_REPLACE(v_data,'.*(.*).*','1') into v_trade_type from dual;

select to_date(v_audit_string,'YYYYMMDD HH24:MI:SS') into v_audit_date from dual;

DBMS_OUTPUT.PUT_LINE(i.queue_info_id||','||
i.info_identifier||','||
v_audit_version||','||
v_trade_type||','||
i.extracted_ind||','||
i.cob_date||','||
to_char(v_audit_date,'DD/MM/YYYY HH24:MI:SS')||','||
to_char(i.entry_date_time,'DD/MM/YYYY HH24:MI:SS')||','||
length(i.info_content)||','||
round((i.entry_date_time-v_audit_date)*24*60*60,0)
);
END LOOP;
END;
/
spool off;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14377/viewspace-1021734/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14377/viewspace-1021734/