Getting Started
In order to use UTL_FILE package, which is owned by SYS, you have to grant EXECUTE privilege to user after you log in as SYS.
Execute the following commands at SQL> prompt after starting SQL*PLUS.
connect sys/password as sysdba
Provide the password of SYS account, which is given at the time of installation of Oracle 10g XE.
Give EXECUTE privilege to required user or PUBLIC (all users) as follows.
grant execute on UTL_FILE to public;
The next important step is to create a logical directory (directory alias) in Oracle that points to physical directory in filesystem. However, directory alias can be created only by DBA (SYS or SYSTEM). So after you logged in as SYS, create directory alias for ORACLE folder in C:\ and grant read and write permissions to PUBLIC as follows.
create directory filesdir as 'c:\oracle'; grant read on directory filesdir to public; grant write on directory filesdir to public;
Now, you can log into HR account and create a table - NAMES as follows.
connect hr/hr; create table names ( name varchar2(30) );
We also need a simple text file NAMES.TXT in C:\ORACLE as follows. NAMES.TXT contains one line for one name.
Kevin Tom Steve George
Now, we are ready to write programs that use UTL_FILE package.
Reading NAMES.TXT
The following program uses UTL_FILE package to read one line at a time from NAMES.TXT file and inserts it into NAMES table. It uses FOPEN, GET_LINE and FCLOSE methods of UTL_FILEpackage.
DECLARE line VARCHAR2(100); namesfile UTL_FILE.FILE_TYPE; BEGIN -- Syntax : FOPEN ( directory alias, filename, open mode) namesfile := UTL_FILE.FOPEN('FILESDIR','NAMES.TXT','R'); -- open in read mode LOOP -- read a line from NAMES.TXT -- NO_DATA_FOUND exception occurs when you reach end of file -- Syntax : GET_LINE( filename, variable , max length) UTL_FILE.GET_LINE(namesfile,line,100); insert into names values( line); -- insert into NAMES table END LOOP; EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE(namesfile); -- close file WHEN OTHERS THEN dbms_output END;
After running the above program, you will see names inserted into NAMES table. Use the following query to list rows from NAMES table.
select * from names;
Writing into JOBS.TXT
UTL_FILE provides PUT_LINE method to write a complete line into file along with new line. For this to work, the file must be opened in write(w) mode. The following program takes job titles from JOBS table and writes each title on a separate line in JOBS.TXT file. However, note you must have WRITE permission on the directory to write into file, which we have granted to PUBLIC from SYS in getting started section.
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT JOB_TITLE FROM JOBS ORDER BY JOB_TITLE; BEGIN -- OPEN FILE FOR WRITING JOBSFILE := UTL_FILE.FOPEN('FILESDIR','JOBS.TXT','W'); -- TAKE ONE RECORD FROM CURSOR FOR REC IN JOBSCUR LOOP UTL_FILE.PUT_LINE(JOBSFILE, REC.JOB_TITLE); -- WRITE INTO FILE END LOOP; UTL_FILE.FCLOSE(JOBSFILE); -- CLOSE FILE EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 'ERROR -->' || SQLERRM); END;
After you run the above program, open JOBS.TXT file from C:\ORACLE folder to see job titles written into it.
UTL_FILE package does provide some more interesting methods. For more details on this Oracle supplied package, please read Oracle documentation on UTL_FILE package.
相关推荐
Oracle学习]ORACLE的UTL_FILE包简析 包UTL_FIle用于读写操作系统的文件,前提是首先创建Directory对象、授权。然后就可以使用UTL_FILE包中提供的类型、过程、函数来读取
oracle utl_file包函数与存储过程的用法
描述oracle utl_file包,大家在使用oralce生成文件过程中可以使用。
UTL_FILE用法详解
在oracle9i中设置utl_file_dir参数.txt
oracle plsql 通过utl_http调用 webservice,包括webservice服务端的源码,以及pro的客户端代码。已测试通过。
UTL_FILE操作文件代码,可以操作所有文件,以二进制的方式读取文件,保存到数据库的blob字段
通过ORACLE的UTL_HTTP工具包发送包含POST参数的请求,包含完成的原创代码、测试说明、相关文档,希望对您的学习有帮助。
在oracle中utl_file包提供了一些操作文本文件的函数和过程,学习了一下他的基本操作 1.创建directory,并给用户授权 代码如下: –创建directory create or replace directory TESTFILE as ‘/home/oracle/zxx/test’...
oracle的utl_file工具包。。。
utl_http.request包探测基本信息.txt
utl_mail oracle scheduler对发邮件的ORACLE包UTL_MAIL以及任务作业详细分析
oracle注入utl_http方法.doc
delphi7做Socket的服务器端,oracle的utl_tcp做Socket的Cliient端.Utl_tcp向server发送信息,并从Server端接收反馈。utl_tcp在PL/SQL Developer 中实现。全部源码。
Add version information in the product name banner Add message to remind user to run BoMC with administrator privilege New System Support Lenovo ThinkServer SR590 7D4M Lenovo ThinkAgile HX
IBM 服务器安装Windows2003引导镜像,可以安装Windows2003和Windows2003R2,只是引导镜像,需要刻录光盘,需X58老机器亲测可用,X79机型未使用过
oracle 范文http request接口获取XML,创建表并把样例结果入库 <?xml version="1.0" encoding="utf-8"?> <linked-hash-map> <string>name 字段名称 <string>alias 中文备注 ...
附件带截图 博文链接:https://wm02091070.iteye.com/blog/930174
utl_file_and_directory_utilities_for_all_operating_systems 关键字:sas sql join合并大数据分析宏oracle teradata mysql sas社区stackoverflow statistics人工智慧AI Python R Java Javascript WPS Matlab SPSS ...