declare
file_handle UTL_FILE.FILE_TYPE;
begin
file_handle := UTL_FILE.FOPEN('/tmp', '文件名', 'w');
UTL_FILE.PUTF(file_handle, '写入的信息\n');
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; PutF()过程用来以指定格式把文本写入一个文件Put_Line()过程把一个指定的字符串写入文件并在文件中开始新的一行
file_handle UTL_FILE.FILE_TYPE;
begin
file_handle := UTL_FILE.FOPEN('/tmp', '文件名', 'w');
UTL_FILE.PUTF(file_handle, '写入的信息\n');
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; PutF()过程用来以指定格式把文本写入一个文件Put_Line()过程把一个指定的字符串写入文件并在文件中开始新的一行
例如:
utl_file_dir=G:\oracle\temp //保存文本的路径为G:\oracle\temp
此过程将用户FILE_OWNER的过程FILE_NAME的代码保存到G:\oracle\temp\TEXT.TXT中
create or replace procedure PRO_TO_TXT(FILE_OWNER VARCHAR2,FILE_NAME VARCHAR2)
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=FILE_OWNER AND NAME=FILE_NAME AND TYPE='PROCEDURE';
file_handle:=utl_file.fopen('G:\oracle\temp','test.txt','a');
utl_file.put_line(file_handle,'PROCEDURE NAME:'||FILE_NAME);
WHILE I<=N LOOP
SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE OWNER=FILE_OWNER
AND NAME=FILE_NAME AND TYPE='PROCEDURE' AND LINE= I;
I:=I+1;
utl_file.put_line(file_handle,stor_text);
END LOOP;
utl_file.fclose(file_handle);
commit;
end PRO_TO_TXT;
/
取出来自己处理就行了。
12,34,45,6我想分别得到被逗号分割的值在PL/SQL中应该怎么操作阿
instr() substr()
declare
file_handle utl_file.file_type;
begin
file_handle:=utl_file.fopen('c:\temp','sss.txt','r');
utl_file.put_line(file_handle,str);
用substr截取str;
utl_file.fclose(file_handle);
END;
i number(3);
j number(3); id varchar2(10);
zh varchar2(10);
ph varchar2(10);
flag varchar2(10);
temp varchar2(300); handle utl_file.file_type;
begin
handle:=utl_file.fopen('e:\','ticket.txt','r'); loop
begin
utl_file.get_line(handle,temp);
exception
when no_data_found then exit;
end;
select replace(temp,'"') into temp from dual; i:=instr(temp,',',1,1);
id:=trim(substr(temp,1,i-1)); j:=instr(temp,',',1,2);
zh:=trim(substr(temp,i+1,j-i-1)); i:=j;
j:=instr(temp,',',1,3);
ph:=trim(substr(temp,i+1,j-i-1)); flag:=trim(substr(temp,j+1)); insert into subwaydata(id,zh,ph,flag)values(id,zh,ph,flag);
commit;
end loop; utl_file.fclose(handle);
end;
connect sys/change_on_install as sysdba
file_handle := utl_file.fopen('E:\temp', 'ReadTest.txt', 'r');运行的时候出现:无效的文件夹路径的错误。
已连接。
14:24:26 SQL> show parameter utlNAME TYPE VALUE
------------------------------------ ------- ------
utl_file_dir string *
14:24:28 SQL>
------------------------------------ ----------- -----------------
create_stored_outlines string
utl_file_dir string我的这样
是不是我放的那个init.ora文件没有起作用阿