oracle读取文件29283,关于在Oracle中读写文件的操作

createtabletest (

fld1VARCHAR2(20),

fld2VARCHAR2(20));CREATEORREPLACEPROCEDUREread_demo(file_nameVARCHAR2)ISvSFile   utl_file.file_type;

vNewLineVARCHAR2(200);BEGIN--打开ORALOAD目录下的file_name文件vSFile :=utl_file.fopen('ORALOAD',file_name,'r');--判断是否打开成功IFutl_file.is_open(vSFile)THEN--循环读取文件内每一行的内容LOOPBEGINutl_file.get_line(vSFile, vNewLine);IFvNewLineISNULLTHENEXIT;ENDIF;--将读取的内容插入到表中INSERTINTOtest

(fld1, fld2)VALUES(vNewLine,file_name);

EXCEPTIONWHENNO_DATA_FOUNDTHENEXIT;END;ENDLOOP;COMMIT;ENDIF;--关闭文件utl_file.fclose(vSFile);

utl_file.frename('ORALOAD','test.txt','ORALOAD','x.txt', TRUE);--捕获各种可能出现的异常，如：路径不正确，文件格式无法识别，文件打开失败等等EXCEPTIONWHENutl_file.invalid_modeTHENRAISE_APPLICATION_ERROR (-20051,'Invalid Mode Parameter');WHENutl_file.invalid_pathTHENRAISE_APPLICATION_ERROR (-20052,'Invalid File Location');WHENutl_file.invalid_filehandleTHENRAISE_APPLICATION_ERROR (-20053,'Invalid Filehandle');WHENutl_file.invalid_operationTHENRAISE_APPLICATION_ERROR (-20054,'Invalid Operation');WHENutl_file.read_errorTHENRAISE_APPLICATION_ERROR (-20055,'Read Error');WHENutl_file.internal_errorTHENRAISE_APPLICATION_ERROR (-20057,'Internal Error');WHENutl_file.charsetmismatchTHENRAISE_APPLICATION_ERROR (-20058,'Opened With FOPEN_NCHAR

But Later I/O Inconsistent');WHENutl_file.file_openTHENRAISE_APPLICATION_ERROR (-20059,'File Already Opened');WHENutl_file.invalid_maxlinesizeTHENRAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');WHENutl_file.invalid_filenameTHENRAISE_APPLICATION_ERROR (-20061,'Invalid File Name');WHENutl_file.access_deniedTHENRAISE_APPLICATION_ERROR (-20062,'File Access Denied By');WHENutl_file.invalid_offsetTHENRAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');WHENothersTHENRAISE_APPLICATION_ERROR (-20099,'Unknown UTL_FILE Error');ENDread_demo;

例子：

首先我们得先建立一个 ORACLE的目录对象 指向 C:\

create or replace directory DIR as 'C:\';

--然后我们对这个目录对象进行授权 其实这步可以忽略

grant read, write on directory DIR to 用户;

--以上前奏完成了！ 我们可以写PLSQL 进行操作文件了

declare

isto_file utl_file.file_type; --定义变量的类型为utl_file.file_type

begin

isto_file := utl_file.fopen('DIR', 'test.txt',