PL/SQL 读写OS文件

24-1:建立directory
	conn system/manager
	CREATE DIRECTORY dir1 AS 'E:\DEMO';
	GRANT READ,WRITE ON DIRECTORY EXP_DIR TO scott;
	
w：以文本方式写入OS文件；
a：以文本方式追加内容到OS文件；
wb：以字节方式写入OS文件；
ab：以字节方式追加内容到OS文件；	
24-2:写入OS文件
	CREATE OR REPLACE PROCEDURE add_emp_to_file(name VARCHAR2,filename VARCHAR2) IS
	  handle UTL_FILE.FILE_TYPE;
	  emp_record emp%ROWTYPE;
	BEGIN
	  SELECT * INTO emp_record FROM emp WHERE LOWER(ename)=LOWER(name);
	  handle:=utl_file.fopen('EXP_DIR',filename,'a');
	  utl_file.put_line(handle,emp_record.ename||'    '
	    ||emp_record.sal||'    '||emp_record.job);
	  utl_file.fclose(handle);
	END;
	/
	exec add_emp_to_file('scott','emp.txt')
	exec add_emp_to_file('smith','emp.txt')
	exec add_emp_to_file('clark','emp.txt')
	
24-3:读取OS文件
	CREATE TYPE info_table_type IS TABLE OF VARCHAR2(100);
	/
	CREATE OR REPLACE FUNCTION get_file_content(filename VARCHAR2) RETURN info_table_type IS
	  handle UTL_FILE.FILE_TYPE;
	  info_table info_table_type:=info_table_type('A');
	  f_exist BOOLEAN;
	  f_len INT;
	  blocksize INT;
	  i INT:=1;
	BEGIN
	  utl_file.fgetattr('EXP_DIR',filename,f_exist,f_len,blocksize);
	  handle:=utl_file.fopen('EXP_DIR',filename,'r');
	  LOOP
	    utl_file.get_line(handle,info_table(i));
	    EXIT WHEN utl_file.fgetpos(handle)>=f_len;
	    info_table.extend;
	    i:=i+1;
	  END LOOP;
	  utl_file.fclose(handle);
	  RETURN info_table;
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    RETURN info_table;
	END;
	/
	DECLARE
	  info_table info_table_type:=info_table_type('A');
	BEGIN
	  info_table.extend(&index-1);
	  info_table:=get_file_content('emp.txt');
	  FOR i IN 1..info_table.COUNT LOOP
	    dbms_output.put_line(info_table(i));
	  END LOOP;
	END;
	/