1.
/* 格式化对象 14/9/2017 10:58:11 (QP5 v5.287) */
DECLAREl_length NUMBER;
img_name VARCHAR2 (100);
l_amount INT;
l_offset INT;
l_buff RAW (32767);
B_BLOB BLOB;
c_clob CLOB;
l_bfile BFILE;
l_img NUMBER;
l_filetype UTL_FILE.file_type;
CURSOR c
IS
SELECT attribute2,
po_number,
item_code,
grn_num,
TO_CHAR (rcv_date, 'YYYY/MM/DD') rcv_date
FROM WST_INV_TRN_INTER_TMP;
BEGIN
l_amount := 255;
l_offset := 1;
B_BLOB := NULL;
c_clob := NULL;
l_buff := NULL;
l_bfile := NULL;
img_name := '9';
l_img := 0;
FOR o IN c
LOOP
c_clob :=
c_clob
|| o.attribute2
|| ','
|| o.po_number
|| ','
|| o.item_code
|| ','
|| o.grn_num
|| ','
|| o.rcv_date
|| CHR (13);
END LOOP;
b_blob := UTL_RAW.cast_to_raw (c_clob);
THEN
l_length := LENGTH (b_blob);
img_name := img_name || '.csv';
l_bfile := BFILENAME ('DEV_DIR2', img_name);
l_img := DBMS_LOB.fileexists (l_bfile);
IF l_img = 1 AND DBMS_LOB.ISOPEN (l_bfile) = 0
THEN
DBMS_LOB.fileopen (l_bfile, DBMS_LOB.lob_readonly);
END IF;
IF l_img = 0
THEN
DBMS_LOB.open (b_blob, DBMS_LOB.lob_readonly);
l_filetype :=
UTL_FILE.fopen ('DEV_DIR2',
img_name,
'W',
'32767');
FOR i IN 1 .. CEIL (l_length / 255)
LOOP
DBMS_LOB.read (b_blob,
l_amount,
l_offset,
l_buff);
UTL_FILE.put_raw (l_filetype, l_buff);
l_offset := l_offset + 255;
END LOOP;
END IF;
UTL_FILE.fclose (l_filetype);
DBMS_LOB.close (b_blob);
END IF;
END;
2.
/* 格式化对象 14/9/2017 9:22:31 (QP5 v5.287) */
DECLARE
b_blob BLOB;
csv_name VARCHAR2 (300);
out_file utl_file.file_type;
cursor c is select attribute2 ,to_char(rcv_date,'YYYY/MM/DD') RCV_DATE from WST_INV_TRN_INTER_TMP;
BEGIN
out_file := utl_file.fopen('DEV_DIR2', 'ccc.csv', 'W');
for o in c loop
utl_file.put(out_file,o.attribute2||',');
utl_file.put(out_file,o.rcv_date||chr(13));
end loop;
utl_file.fflush(out_file);
utl_file.fclose(out_file); --关闭文件流
--处理异常
exception
when others then
rollback;
-- prc_log_new(SYSDATE, 'error');
utl_file.fclose(out_file); --关闭文件流,防止异常关闭
END;