Oracle dbms_output vs utl_file 使用(三)
补充 utl_file 的例子
补充 utl_file写字符串去目标log文件的例子,设置buffer(缓冲区)长度是2001。主要是想证明一下utl_file 写文件是也受缓冲区长度的限制,每行的长度(缓冲区)最大32767。
SET SERVEROUTPUT ON;
DECLARE
FILE UTL_FILE.FILE_TYPE;
FILE_EXIST BOOLEAN := FALSE;
FILE_FILESIZE NUMERIC;
FILE_BLOCKSIZE NUMERIC;
FILE_PATH VARCHAR2(200);
FILE_NAME VARCHAR2(200);
str_2000_length VARCHAR2(2000);
str_767_length VARCHAR2(2000);
BEGIN
--OUT_LOG是Oracle DIRECTORIES的设置
FILE_PATH := 'OUT_LOG';
FILE_NAME := 'energyno8_utl_file.log';
dbms_output.put_line('------dbmsoutp------');
str_2000_length := '123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-';
str_767_length := '123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-1234567';
UTL_FILE.FGETATTR(FILE_PATH, FILE_NAME, FILE_EXIST, FILE_FILESIZE, FILE_BLOCKSIZE);
--check文件是否存在
IF (FILE_EXIST) THEN
--remove it when file existed
UTL_FILE.FREMOVE(FILE_PATH, FILE_NAME);
END IF;
--打开目标文件,操作方式是W(write),buffer长度2001,最大32767
FILE := UTL_FILE.FOPEN(FILE_PATH,FILE_NAME,'W',2001);
--UTL_FILE.PUT_LINE(FILE,To_Char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') || ' -> write one line LOG......');
UTL_FILE.PUT_LINE(FILE,str_2000_length);
--UTL_FILE.PUT_LINE(FILE,'line 1:');
--UTL_FILE.PUT_LINE(FILE,str_767_length);
--UTL_FILE.PUT_LINE(FILE,'line 2:');
--UTL_FILE.PUT_LINE(FILE,str_767_length);
--UTL_FILE.PUT_LINE(FILE,'line 3:');
--UTL_FILE.PUT_LINE(FILE,str_767_length);
UTL_FILE.FCLOSE(FILE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('sqlplus UTL_FILE test failed');
DBMS_OUTPUT.PUT_LINE('Exception: ' || SQLERRM || Chr(10));
DBMS_OUTPUT.PUT_LINE('ERROR exit!' || Chr(10));
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
END;
/
SET SERVEROUTPUT OFF;
EXIT;
上面的例子如果将29行的buffer长度改成2000运行会报错。因为输出一个长度2000的字符串输出完成之后会再输出一个结束符,buffer设置成2001执行就成功了。
buffer长度2000,连续输出3行长767的字符串,运行成功。
SET SERVEROUTPUT ON;
DECLARE
FILE UTL_FILE.FILE_TYPE;
FILE_EXIST BOOLEAN := FALSE;
FILE_FILESIZE NUMERIC;
FILE_BLOCKSIZE NUMERIC;
FILE_PATH VARCHAR2(200);
FILE_NAME VARCHAR2(200);
str_2000_length VARCHAR2(2000);
str_767_length VARCHAR2(2000);
BEGIN
--OUT_LOG是Oracle DIRECTORIES的设置
FILE_PATH := 'OUT_LOG';
FILE_NAME := 'energyno8_utl_file.log';
dbms_output.put_line('------dbmsoutp------');
str_2000_length := '123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-';
str_767_length := '123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-1234567';
UTL_FILE.FGETATTR(FILE_PATH, FILE_NAME, FILE_EXIST, FILE_FILESIZE, FILE_BLOCKSIZE);
--check文件是否存在
IF (FILE_EXIST) THEN
--remove it when file existed
UTL_FILE.FREMOVE(FILE_PATH, FILE_NAME);
END IF;
--打开目标文件,操作方式是W(write),buffer长度2001
FILE := UTL_FILE.FOPEN(FILE_PATH,FILE_NAME,'W',2000);
UTL_FILE.PUT_LINE(FILE,To_Char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') || ' -> write one line LOG......');
--UTL_FILE.PUT_LINE(FILE,str_2000_length);
UTL_FILE.PUT_LINE(FILE,'line 1:');
UTL_FILE.PUT_LINE(FILE,str_767_length);
UTL_FILE.PUT_LINE(FILE,'line 2:');
UTL_FILE.PUT_LINE(FILE,str_767_length);
UTL_FILE.PUT_LINE(FILE,'line 3:');
UTL_FILE.PUT_LINE(FILE,str_767_length);
UTL_FILE.FCLOSE(FILE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('sqlplus UTL_FILE test failed');
DBMS_OUTPUT.PUT_LINE('Exception: ' || SQLERRM || Chr(10));
DBMS_OUTPUT.PUT_LINE('ERROR exit!' || Chr(10));
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
END;
/
SET SERVEROUTPUT OFF;
EXIT;
energyno8_utl_file.log的内容(太长截图没截完整)
使用put方法(put没有换行,要显式代码换行)。在例子中要留意FFLUSH的使用。
SET SERVEROUTPUT ON;
DECLARE
FILE UTL_FILE.FILE_TYPE;
FILE_EXIST BOOLEAN := FALSE;
FILE_FILESIZE NUMERIC;
FILE_BLOCKSIZE NUMERIC;
FILE_PATH VARCHAR2(200);
FILE_NAME VARCHAR2(200);
str_2000_length VARCHAR2(2000);
str_767_length VARCHAR2(2000);
BEGIN
--OUT_LOG是Oracle DIRECTORIES的设置
FILE_PATH := 'OUT_LOG';
FILE_NAME := 'energyno8_utl_file.log';
dbms_output.put_line('------dbmsoutp------');
str_2000_length := '123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-';
str_767_length := '123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-123456789-1234567';
UTL_FILE.FGETATTR(FILE_PATH, FILE_NAME, FILE_EXIST, FILE_FILESIZE, FILE_BLOCKSIZE);
--check文件是否存在
IF (FILE_EXIST) THEN
--remove it when file existed
UTL_FILE.FREMOVE(FILE_PATH, FILE_NAME);
END IF;
--打开目标文件,操作方式是W(write),buffer长度2001
FILE := UTL_FILE.FOPEN(FILE_PATH,FILE_NAME,'W',2000);
UTL_FILE.PUT_LINE(FILE,To_Char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') || ' -> write one line LOG......');
--UTL_FILE.PUT_LINE(FILE,str_2000_length);
UTL_FILE.PUT(FILE,'line 1:');
UTL_FILE.PUT(FILE,str_767_length);
UTL_FILE.PUT(FILE,'line 2:');
UTL_FILE.PUT(FILE,str_767_length);
--刷新缓冲区,相当于将缓冲区的内容输出到文件。
UTL_FILE.FFLUSH(FILE);
--如果没有NEW_LINE,PUT第3次str_767_length会出错,因为一行超出缓冲区长度2000
UTL_FILE.NEW_LINE(FILE,1);
UTL_FILE.PUT(FILE,'line 3:');
UTL_FILE.PUT(FILE,str_767_length);
UTL_FILE.FFLUSH(FILE);
UTL_FILE.NEW_LINE(FILE,1);
UTL_FILE.FCLOSE(FILE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('sqlplus UTL_FILE test failed');
DBMS_OUTPUT.PUT_LINE('Exception: ' || SQLERRM || Chr(10));
DBMS_OUTPUT.PUT_LINE('ERROR exit!' || Chr(10));
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
END;
/
SET SERVEROUTPUT OFF;
EXIT;
上面的代码会运行成功,可以尝试注析上面37/39行在看看运行效果(buffer超长出错)。
下面的运行结果可以看到“line 3:”在新一行输出。
试了这几种情况不知道有用没用!!
本文只是个人(EnergyNo8)学习笔记如有错误请网友指出。
如有引用或转载请标明出处!
努力、坚持,总会有收获!
______ EnergyNo8