这个是我以前写的,调试通过,步骤也写了
照着做试试先修改init.ora
例如:
utl_file_dir=/usr //路径为 oracle所在的盘:/usr
此过程将用户TEMP的P1过程的代码保存到ORACLE安装盘下/USR/TEXT.TXT中
create or replace procedure TEST
is
file_handle utl_file.file_type;
STOR_TEXT VARCHAR2(4000);
N NUMBER;
I NUMBER;
begin
I:=1;
SELECT MAX(LINE) INTO N FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1';
file_handle:=utl_file.fopen('/usr','test.txt','a');
WHILE I<=N LOOP
SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1' AND LINE= I;
I:=I+1;
utl_file.put_line(file_handle,stor_text);
END LOOP;
utl_file.fclose(file_handle);
commit;
end TEST;
/
照着做试试先修改init.ora
例如:
utl_file_dir=/usr //路径为 oracle所在的盘:/usr
此过程将用户TEMP的P1过程的代码保存到ORACLE安装盘下/USR/TEXT.TXT中
create or replace procedure TEST
is
file_handle utl_file.file_type;
STOR_TEXT VARCHAR2(4000);
N NUMBER;
I NUMBER;
begin
I:=1;
SELECT MAX(LINE) INTO N FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1';
file_handle:=utl_file.fopen('/usr','test.txt','a');
WHILE I<=N LOOP
SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1' AND LINE= I;
I:=I+1;
utl_file.put_line(file_handle,stor_text);
END LOOP;
utl_file.fclose(file_handle);
commit;
end TEST;
/
另外UTL_FILE_DIR= 应该加在INIT.ORA的什么位置,是最后加上一行吗?
我要打开的文本文件为C:\AA.TXT
ORALCE路径为:C:\ORC1\
如何设置,拜托!
你可以试一下
我试过了,重新启动后,用pl/sql连接不上出错,我试了好几个地方都不行,有空帮我看看吧!
还是sql*plus?
出错信息是什么?
我的文本文件: C:\AA.TXT
ORACLE :C:\ORC1
设置init.ora
utl_file_dir=c:
即可
file IN FILE_TYPE,
buffer IN VARCHAR2);GET_LINE procedure
This procedure reads a line of text from the open file identified by the file handle and places the text in the output buffer parameter. Text is read up to but not including the line terminator, or up to the end of the file. If the line does not fit in the buffer, then a VALUE_ERROR exception is raised. If no text was read due to "end of file," then the NO_DATA_FOUND exception is raised. Because the line terminator character is not read into the buffer, reading blank lines returns empty strings. The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of FOPEN. 根据oracle相关的文档
在程序中
exception when NO_DATA_FOUND then//表示文本读取结束使用方法跟put_line一样
看一下上面的例子
自己写写试一下。
is
file_handle utl_file.file_type;
l1 VARCHAR2(10);
l2 VARCHAR2(10);
L3 VARCHAR2(10);
L4 VARCHAR2(10);
I NUMBER;
N NUMBER;
begin
file_handle:=utl_file.fopen('C:\','test.txt','r');
utl_file.get_line(file_handle,L1);
utl_file.get_line(file_handle,L2);
utl_file.get_line(file_handle,L3);
utl_file.get_line(file_handle,L4);
utl_file.fclose(file_handle);
exception
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END ;
但是怎么得到文本文件的行数,和从文件中读取下一行,是不是用死循环,通过异常退出,
我再找找资料吧!