Oracle UTL_FILE的使用

合集下载

ORACLE 借助utl_file使用存储过程将oracle表中的数据导出成文本文件

ORACLE 借助utl_file使用存储过程将oracle表中的数据导出成文本文件摘要：在使用数据库的过程中，我们经常会遇到一个问题：如何将数据按照想要的格式导出到一个文本文件中。

当然，我们可以通过编写一些应用程序或通过第三方的软件工具来解决这个问题，但是其效率和灵活性并不一定是最优的解决方案。

本文就探讨使用数据库管理系统自身的功能来实现以上的需求。

本文研究的实验环境为Oracle 9i数据库系统，操作系统为Sun Solaris 10。

关键词：Oracle数据库；数据导出；数据文件；导出方法1.引言数据库已经应用比较普遍，目前各类系统的开发几乎都离不开数据库管理系统的支撑，当前占数据库管理系统市场较大份额的有Oracle、DB2、SQL Server等。

在使用数据库的过程中，难免会有将数据表中的数据导出这样的需求，特别是一些企业应用需要经常性的提取一些业务数据作为分析决策的参考，而这些提取数据的需求又具有格式经常变化等特点，如果单纯依靠编写应用程序就会显得很麻烦，成本也较大。

第三方工具因为需要另外安装等不方便的原因，所以也很难满足要求。

因此需要利用数据库自身的工具或功能来实现数据表数据的提取。

2.本文约定的实验环境Oracle 9i是由甲骨文（Oracle）公司出品的一款企业级数据库管理系统，目前最新的版本是11g，但9i 版本应用比较普遍，主要应用于电信、公安等行业的数据库管理。

Solaris 10 是由太阳（SUN）公司出品的一种UNIX操作系统，因为其稳定性及安全性较好，在一些大型的企业级应用中也比较广泛。

文中约定实验环境中的Oracle9i的服务名（SID）为example，其管理员用户（sys）的口令为passwd，普通用户的用户名为user，其口令为userpwd，在user用户方案下存在一个数据库表，其表结构如下：create table test (id number(1),name varchar2(20),address varchar2(20));向表中插入一些初始化数据，内容如下：insert into test values(1,'wanglp','sy');insert into test values(2,'gaoliang','dl');insert into test values(3,'fuyaxian','as');insert into test values(4,'wangtong','bx');insert into test values(5,'gaoqi','dd');最终实验输出的文本文件的内容及格式如下：1|wanglp|sy2|gaoliang|dl3|fuyaxian|as4|wangtong|bx5|gaoqi|dd3.实验过程接下来将采用两种方法来实现数据输出到文本的功能。

oracle--导出、导入blob类型的字段

oracle--导出、导⼊blob类型的字段以下操作记录了blob字段的导出、导⼊⽅法流程。

⽅法原理：利⽤UTL_FILE将blob字段的内容以⼆进制的形式导出到txt⽂档，然后⽤dbms_blob将⽂档内容导⼊到指定的数据库表中。

1、创建⼀个⽂本⽂档来保存blob数据。

这⾥在E盘home/dhl下创建⼀个名为text.txt的⽂件。

2、创建oracle临时⽬录1create or replace directory UTL_FILE_DIR as'E:/home/dhl/';2GRANT read ,write ON DIRECTORY UTL_FILE_DIR TO PUBLIC;注意: ⽬录下⾯要有text.txt的⽂件3、导出blob数据这⾥以SVS.SVS_ACCADM_SEALINFO表这张数据表为例⼦，其中的SEAL就是BLOB类型的字段1DECLARE2 file_handle UTL_FILE.FILE_TYPE;3 b_lob BLOB;4BEGIN5select SEAL into b_lob from SVS.SVS_ACCADM_SEALINFO where ID='100007922';67 file_handle := utl_file.fopen('UTL_FILE_DIR', 'test.txt', 'WB');8 utl_file.put_raw(file_handle , b_lob, true);9 UTL_FILE.FCLOSE(file_handle);10END;执⾏完后，会把这条数据的BLOB类型的数据以⼆进制的形式写⼊到了text.txt的⽂件中。

4、将⽂档内容导⼊到指定的数据库表中1DECLARE2 b_file bfile;3 b_lob BLOB;4BEGIN5--return a into b_lob将该列与⼀个blog类型的变量绑定在⼀起,以后只要为b_lob赋值，即等于将该值插⼊了表中6INSERT7INTO SVS.SVS_ACCADM_SEALINFO8 (9 ID,10 SEAL,11 SMALL_CODE,12 SEAL_TYPE,13 START_DATE,14 END_DATE,15 NO_USE_DATE,16 MEMO,17 SEAL_POSITION,18 ACC_NO,19 SEAL_CARD_ID,20 SEAL_CARD_NO,21 DOC_OBJECT_ID,22 CRUD_FLAG23 )24VALUES25 (26'100007922',27 EMPTY_BLOB(),28NULL,29'P',30'2019-11-26',31NULL,32NULL,33NULL,34NULL,35'2019112601',36'1199129990231314433',37'100003981',38'e19b7edfcdee436598367485605921cc57226621258',39'4'40 )41RETURN SEAL42INTO b_lob;43--将⽂件转换为bfile44 b_file := bfilename('UTL_FILE_DIR', 'test.txt');45 dbms_lob.open(b_file, dbms_lob.file_readonly);46-- 将b_file中的内容转换到b_lob47 dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));48 dbms_lob.close(b_file);49COMMIT;50END;。

oracle存储过程读写文件操作

oracle存储过程读写文件操作Oracle数据库提供了一种功能强大的存储过程来进行文件的读写操作。

这些功能可以用于读取外部的文件，将结果写入文件，以及将数据从一个文件中导入到数据库中等操作。

下面是一个示例的存储过程，将文件中的数据导入到数据库表中。

存储过程的输入参数包括文件的路径和文件名，以及表名称。

存储过程的步骤如下：1.使用UTL_FILE包进行文件的读取操作。

首先通过调用UTL_FILE.FOPEN函数打开指定路径的文件，然后通过UTL_FILE.GET_LINE 函数逐行读取数据，并将其存储到一个临时变量中。

2.使用SQL语句将读取到的数据插入到指定的表中。

可以使用INSERTINTO语句将数据插入到表中。

3.当文件的最后一行被读取后，关闭文件并结束存储过程。

下面是一个示例的存储过程：CREATE OR REPLACE PROCEDURE import_data_from_file(p_file_path IN VARCHAR2, p_file_name IN VARCHAR2,p_table_name IN VARCHAR2)ASfile_handle UTL_FILE.FILE_TYPE;file_data VARCHAR2(4000);BEGIN--打开指定路径下的文件file_handle := UTL_FILE.FOPEN(p_file_path, p_file_name, 'R');--循环读取文件中的每一行数据LOOPUTL_FILE.GET_LINE(file_handle, file_data);--将读取到的数据插入到指定的表中INSERT INTO p_table_name VALUES (file_data);--判断是否到了文件的最后一行IF UTL_FILE.IS_OPEN(file_handle) = FALSE THENEXIT;ENDIF;ENDLOOP;--关闭文件UTL_FILE.FCLOSE(file_handle);--提交事务COMMIT;--输出导入数据的信息DBMS_OUTPUT.PUT_LINE('Data imported successfully fromfile.');EXCEPTIONWHENOTHERSTHEN--输出错误信息DBMS_OUTPUT.PUT_LINE('Error: ' ， SQLERRM);--关闭文件UTL_FILE.FCLOSE(file_handle);--回滚事务ROLLBACK;END;这个存储过程可以通过传递文件路径、文件名和表名来导入数据。

oracle读取文件方法

Oracle读取文件方法及应用实践正文内容：引言：在Oracle数据库中，读取外部文件是一项常见的任务。

无论是读取文本文件、CSV文件、XML文件还是其他格式的文件，Oracle提供了多种方法来实现这个功能。

本文将介绍一些常用的Oracle读取文件的方法，并结合实际案例来说明其应用。

一、使用UTL_FILE包读取文本文件UTL_FILE包是Oracle提供的一个标准包，可以用于读取和写入文本文件。

它提供了多个过程和函数，可以实现对指定目录下的文件进行读取、写入、定位等操作。

下面是一个使用UTL_FILE包读取文本文件的示例：DECLAREfile_handle UTL_FILE.FILE_TYPE;file_line VARCHAR2(255);BEGIN-- 打开文件file_handle := UTL_FILE.FOPEN('DATA_DIR', 'example.txt', 'R');-- 逐行读取文件内容LOOPUTL_FILE.GET_LINE(file_handle, file_line);-- 处理文件内容DBMS_OUTPUT.PUT_LINE(file_line);END LOOP;-- 关闭文件UTL_FILE.FCLOSE(file_handle);EXCEPTIONWHEN UTL_FILE.INVALID_DIRECTORY THENDBMS_OUTPUT.PUT_LINE('无效的目录');WHEN UTL_FILE.INVALID_OPERATION THENDBMS_OUTPUT.PUT_LINE('无效的操作');WHEN UTL_FILE.INVALID_FILEHANDLE THENDBMS_OUTPUT.PUT_LINE('无效的文件句柄');END;二、使用外部表读取CSV文件除了使用UTL_FILE包读取文本文件外，还可以使用外部表来读取CSV文件。

oracle utl_file.fopen用法

oracle utl_file.fopen用法oracle的utl_file是一种可用来访问操作系统文件和目录的工具包，提供像fopen、fwrite、fread和fclose这样的标准文件I/O操作。

这里我们会介绍utl_file.fopen的用法。

UTL_FILE.FOPEN(location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type;参数含义：location：文件所在的路径，可以是数据库服务器本地路径或共享路径，不提供默认为null。

filename：文件名，为string型变量或文字字面值。

open_mode：文件打开模式，有“r”，“w”和“a”三种，分别表示读取、写入、追加，默认为“r”。

max_linesize：最大行数，默认为32767字节。

file_type：打开的文件流句柄。

示例：DECLAREl_file UTL_FILE.FILE_TYPE;BEGINl_file := UTL_FILE.FOPEN('FILES_DIR','myfile.txt','w');END;2.打开文件如果没有提供location，则需要为Oracle服务器提供操作系统路径，并在Oracle中运行以下命令：ALTER SYSTEM SET UTL_FILE_DIR = '/path/to/files' SCOPE=BOTH;使用utl_file.fopen打开一个文件有两种方式：1、只读方式在只读方式下，utl_file会将文件中创建的一行数据读取到内存中，并将其填充到一个bufffer数组中。

2、以追加或写入方式打开文件因为以这种方式打开的文件，utl_file不会将它们的数据存储在内存中，所以我们可以在需要的时候追加或写入。

oracle表依赖关系

oracle表依赖关系摘要：1.Oracle表依赖关系概述2.查询Oracle表依赖关系的方法3.编写存储过程导入Excel数据至Oracle数据库4.总结与建议正文：【1】Oracle表依赖关系概述在Oracle数据库中，表依赖关系是指一个表与其他表之间的关联。

这种关联可以帮助我们更好地管理和维护数据。

了解表依赖关系对于数据库设计和开发人员来说至关重要，因为它能帮助我们更好地理解数据的一致性、完整性和安全性。

【2】查询Oracle表依赖关系的方法在Oracle数据库中，可以使用以下几种方法查询表依赖关系：1.使用SQL语句查询：```SELECT level, lpad(",", 2, (level - 1), uc.tablename AS "table",uc.constraintname, uc.rconstraintnameFROM allconstraints ucWHERE uc.cons_type = "R"```2.使用数据库可视化工具，如SQL Developer或Enterprise Manager。

【3】编写存储过程导入Excel数据至Oracle数据库在Oracle数据库中，可以使用UTL_FILE包将CSV格式的Excel数据导入数据库表。

以下是一个示例存储过程：```sqlCREATE OR REPLACE PROCEDURE import_excel_data(p_file_path VARCHAR2, p_table_name VARCHAR2)ISl_file_name UTL_FILE.filename;l_file_content UTL_RAW.RAW_TYPE;l_row_count NUMBER;l_cursor SYS_REFCURSOR;l_data UTL_RAW.RAW_TYPE;BEGIN-- 创建文件对象l_file_name := UTL_FILE.open(p_file_path, "r");-- 读取文件内容l_file_content :=UTL_RAW.CAST_TO_RAW(UTL_FILE.READ_FULL(l_file_name));-- 获取文件行数l_row_count := UTL_RAW.GET_LENGTH(l_file_content) /UTL_RAW.GET_SIZE(l_file_content);-- 创建游标OPEN l_cursor FOR "SELECT * FROMTABLE(UTL_RAW.CAST_TO_VARCHAR2(l_file_content, "||p_table_name||")) AS t(column1 column2 ...);-- 循环插入数据LOOPFETCH l_cursor INTO l_data;EXIT WHEN l_cursor%NOTFOUND;INSERT INTO p_table_name (column1, column2, ...) VALUES(l_data);END LOOP;-- 关闭游标和文件CLOSE l_cursor;UTL_FILE.close(l_file_name);EXCEPTIONWHEN OTHERS THENUTL_FILE.close(l_file_name);RAISE;END;/```【4】总结与建议了解Oracle表依赖关系有助于更好地管理和维护数据库。

UTL_FILE

完整的ORACLE的UTL_FILE的资料摘录自：/alex197963/archive/2008/01/08/2030204.aspx第一步：以管理员用户登陆如：conn sys/passwo rd@sid as sy sdba第二步：设置可操作目录需要指定utl_file包可以操作的目录。

在o racle 10g以前，可以用以下方法：1、alter system set utl_file_dir='e:\utl' scope=spfile;2、在init.o ra文件中，配置如下：UTL_FILE=E:\utl或者UTL_FILE_DIR=E:\utl在oracle 10g中建议用以下方法配置：CREATE DIRECTORY utl AS 'E:\utl';参见oracle o nline:In the past, accessible directo ries fo r the UTL_FILE functions were specified i n the initialization file using the UTL_FILE_DIR parameter. Ho wever, UTL_FILE_ DIR access is not reco mmended. I t is reco mmended that y o u use the CREAT E DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer mo r e flexibility and granular co ntrol to the UTL_FILE applicatio n administrato r, can be maintained dynamically (that is, without shutting do wn the database), and are co nsistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.第三步：授权给指定用户，以便执行utl_fileGRANT EXECUTE ON utl_file TO scott;第四步：conn scott/tiger就可以正常使用utl_file了。

oracle读取远程文件方法

oracle读取远程文件方法一、概述Oracle数据库是一个非常强大的关系型数据库管理系统，它可以通过多种方式来读取远程文件。

在本文中，我们将介绍如何使用Oracle数据库来读取远程文件。

二、使用UTL_FILE包UTL_FILE是Oracle提供的一个包，它可以让我们在Oracle数据库中读写文件。

使用UTL_FILE包可以轻松地读取远程文件，下面是具体步骤：1. 创建目录对象首先，我们需要创建一个目录对象，用于指向远程服务器上的文件所在路径。

我们可以使用CREATE DIRECTORY语句来创建目录对象。

例如：CREATE DIRECTORY remote_dir AS '\\192.168.1.100\share';这个语句创建了一个名为remote_dir的目录对象，并将其指向了位于192.168.1.100服务器上的share共享文件夹。

2. 授权给用户接下来，我们需要将这个目录对象授权给需要访问它的用户。

我们可以使用GRANT语句来授权。

例如：GRANT READ, WRITE ON DIRECTORY remote_dir TO scott;这个语句将remote_dir目录对象授权给scott用户，并允许scott用户对该目录进行读写操作。

3. 打开文件现在，我们已经准备好开始读取远程文件了。

首先，我们需要打开该文件。

我们可以使用UTL_FILE.FOPEN函数来打开文件。

例如：DECLAREfile_handle UTL_FILE.FILE_TYPE;BEGINfile_handle := UTL_FILE.FOPEN('remote_dir', 'test.txt', 'R'); END;这个代码片段使用UTL_FILE.FOPEN函数打开了remote_dir目录下的test.txt文件，并将其赋值给file_handle变量。

文件IO操作open(),close(),read()和write()函数详解

文件I/O操作open(),close(),read()和write()函数详解1. open（）函数功能描述：用于打开或创建文件，在打开或创建文件时可以指定文件的属性及用户的权限等各种参数。

所需头文件:#include <sys/types.h>,#include <sys/stat.h>,#include <fcntl.h>函数原型:int open(const char *pathname,int flags,int perms)参数：pathname:被打开的文件名（可包括路径名如"dev/ttyS0"）flags:文件打开方式,O_RDONL Y:以只读方式打开文件O_WRONL Y:以只写方式打开文件O_RDWR:以读写方式打开文件O_CREAT:如果改文件不存在，就创建一个新的文件，并用第三个参数为其设置权限O_EXCL:如果使用O_CREAT时文件存在，则返回错误消息。

这一参数可测试文件是否存在。

此时open是原子操作，防止多个进程同时创建同一个文件O_NOCTTY:使用本参数时，若文件为终端，那么该终端不会成为调用open()的那个进程的控制终端O_TRUNC:若文件已经存在，那么会删除文件中的全部原有数据，并且设置文件大小为0O_APPEND:以添加方式打开文件，在打开文件的同时，文件指针指向文件的末尾，即将写入的数据添加到文件的末尾O_NONBLOCK: 如果pathname指的是一个FIFO、一个块特殊文件或一个字符特殊文件，则此选择项为此文件的本次打开操作和后续的I/O操作设置非阻塞方式。

O_SYNC:使每次write都等到物理I/O操作完成。

O_RSYNC:read 等待所有写入同一区域的写操作完成后再进行在open()函数中，falgs参数可以通过“|”组合构成，但前3个标准常量（O_RDONL Y，O_WRONL Y，和O_RDWR）不能互相组合。

oracle存储过程记录执行日志

oracle存储过程记录执行日志
1、一般来说，存储过程记录执行日志的方法如下：
（1）使用dbms_output存储过程中的输出信息
dbms_output是Oracle提供的一种存储过程中的输出记录，可以使
用DBMS_OUTPUT.PUT_LINE函数在存储过程中输出信息。

输出的信息都会
被存储在一个缓冲区中，可以使用DBMS_OUTPUT.GET_LINE函数在PL/SQL
程序外部获取缓冲区中的数据。

（2）使用utl_file输出日志
utl_file是Oracle提供的一个模块，可以从存储过程中输出文件，
使用UTL_FILE.PUT_LINE函数可以在存储过程中向文件中输出日志信息，
文件的内容存储在存储过程程序外，比如服务器的磁盘上。

（3）使用异常处理程序记录日志
如果在存储过程中引发异常，可以使用EXCEPTIONWHEN程序块来处理，EXCEPTIONWHEN块可以定义其中一种异常发生时的处理动作，比如记录日志，发送警报等。

2、记录存储过程执行日志的具体步骤：
（1）在存储过程中设置参数
在存储过程中，需要定义一些参数，比如要记录的信息的类型，日志
文件的名称，是否弹出提示框等。

（2）在存储过程中定义CURSOR
在存储过程中定义CURSOR可以提取存储过程执行时的细节信息，比如错误代码，错误信息，系统变量等。

（3）在存储过程中进行检查和异常处理
在存储过程中。

utl_file_dir参数

utl_file_dir参数UTL_FILE_DIRParameter typeStringSyntaxUTL_FILE_DIR = pathnameDefault valueThere is no default value.Parameter classStaticRange of valuesAny valid directory pathUTL_FILE_DIR lets you specify one or more directories that Oracle should use for PL/SQL file I/O. If you are specifying multiple directories, you must repeat the UTL_FILE_DIR parameter for each directory on separate lines of the initialization parameter file.All users can read or write to all files specified by this parameter. Therefore all PL/SQL users must be trusted with the information in the directories specified by this parameter.设置不能动态设置SQL>alter system set utl_file_dir='c:\oracle\oradata' scope=spfile ;重启数据库，之后SQL>show parameter utl_file；NAME TYPE VALUE------------------------------------ ----------- ------------------------------ utl_file_dir string c:\oracle\oradataSQL>declare2 fn utl_file.file_type;3 begin4 fn:=utl_file.fopen('c:\oracle\oradata','utl_test.txt','W');5 utl_file.fclose(fn);6 end;7/PL/SQL 过程已成功完成SQL>很多时候直接在utl_file.fopen的参数里设置目录不成功的原因就是没有配置好utl_file_dir这个参数。

Oracle数据库通过UTL—FILE实现磁盘文件操作

ＩＳＮ０９０４Ｓ１０－３４
Ｅｍａ：ｓ＠ｃｃ．ｅｃ — ｉｊｌｈｃｃｔｎｎ．
ｈｔ：ｗｗｄｚ．ｅ．Ｂｔ／ｗ．ｎｓｔｐ／ｎＣＴｌ８ — ５－６０６５９９４ｅ：６５１５９９３６０６＋
收稿日期：０１０９２Ｉ —１ —０
本栏目责任编辑：影代
数■摩与信息 ■毫
８８７５
Ｃｍｕｒｎｗｄｅｎｃｎｌｙｏｐｔｏｌｇｄｅｈｏｇ电脑知识与ｅＫｅａＴｏ技术
第７第３期卷４
（０１１２１年２月）
ＣｍｐｔｒｎｗｅｇｎｅｈｏｇｏｕｅｏｌｄｅｄＴｃｎｌｙ电脑知识与技术Ｋａｏ
Ｖｏ．，．４Ｄｅｅｅ０１．１Ｎｏ３，ｃｍｂｒ２７１
Ｏｒｃｅ据库通过ＵＬＦＬａｌ数Ｔ — ＩＥ实现磁盘文件操作
１ＵＬ ’ Ｅ包简介Ｔｊ５ＬＩ
ＵＬＦＬＯａｌ的内置包，ＴＩＥ是ｒｃｅ主要用于支持／Ｑ程序对服务器磁盘文件的访问和操作。下面列出了Ｔ＿ＩＥ包一些常用ＰＳＬＬＵＬＦＬ过程和函ＦｌｓｆｕｈＦ１ｓｃｏｅ
Ｐｔｕ
Ｐｕｔ
—
打开指定文件并返回一个文件句柄用于操作文件确保所有数据写入文件关闭文件
在当前行输出数据
ｌｎｉｅ
输出一傅
符串以一个与系统有关的行终止符
Ｇ￣ｌｎｅｉｅ
ｃｐｏｙ
读取指定文件的一行

oracle授予directory读权限Oracle对目录文件directory的读写操作

oracle授予directory读权限Oracle对目录文件directory的读写操作Oracle是一种强大的数据库管理系统，它提供了一种称为“directory”的特性，用于在数据库中存储和访问操作系统上的文件。

本文将重点探讨Oracle如何授予directory读权限，并介绍一些相关的读写操作。

一、什么是Oracle的directory在Oracle数据库中，directory是一种特殊的数据库对象，它充当了连接数据库和操作系统文件系统之间的桥梁。

通过directory，我们可以将操作系统上的文件和目录与数据库关联起来，从而方便地在数据库中读取、写入或操作这些文件。

二、授予directory读权限的方法要授予directory读权限，我们可以通过以下步骤来完成：1. 创建directory对象：首先，我们需要在Oracle数据库中创建一个directory对象，该对象将对应于操作系统上的一个目录。

使用CREATE DIRECTORY语句来完成此操作。

例如，我们可以创建一个名为"my_directory"的directory对象，对应于操作系统上的"/home/user/files"目录。

2. 授予读权限：完成directory对象的创建后，接下来我们需要授予读权限给指定的用户或角色。

使用GRANT语句来实现此操作。

例如，我们可以授予用户"hr"对于"my_directory"目录的读权限：GRANT READ ON DIRECTORY my_directory TO hr;3. 验证权限：授予了读权限之后，我们可以验证该权限是否有效。

用户或角色可以使用数据库函数或过程来读取directory中的文件。

例如，我们可以使用UTL_FILE包中的SUBDIRS函数列出directory中的文件和目录：SELECT * FROM TABLE(UTL_FILE.SUBDIRS('my_directory'));三、Oracle对目录文件的读写操作授予了directory的读权限后，我们可以进行一系列的读写操作。

用OracleSQL实现文件访问

用OracleSQL实现文件访问Oracle SQL是一种用于管理和操作关系数据库的编程语言。

它提供了多种功能，包括数据查询、插入、更新和删除等。

虽然Oracle SQL本身并不直接支持文件访问，但可以通过一些技巧和特性实现与文件的交互。

本文将介绍几种在Oracle SQL中实现文件访问的方法。

一、使用外部表（External Table）外部表是一种特殊类型的表，它与数据库中的表不同，它的数据存储在文件中而不是数据库中。

通过创建一个外部表，可以将文件中的数据在Oracle数据库中直接查询、操作和分析。

以下是通过外部表实现文件访问的步骤：1.在Oracle数据库中创建一个目录（Directory），指定目录的物理路径。

```CREATE DIRECTORY my_dir AS '/path/to/my/directory';```2.创建外部表，指定文件的位置和格式。

```CREATE TABLE my_external_tablecolumn1 datatype1,column2 datatype2,...ORGANIZATIONEXTERNALTYPEORACLE_LOADERDEFAULT DIRECTORY my_dirACCESSPARAMETERSRECORDSDELIMITEDBYNEWLINEFIELDSTERMINATEDBY','MISSINGFIELDVALUESARENULLLOCATION ('file.txt')REJECTLIMITUNLIMITED;```3.通过查询外部表来访问文件中的数据。

```SELECT*FROM my_external_table;```二、使用UTL_FILE包UTL_FILE是Oracle提供的一个包，用于在数据库中读写操作系统的文件。

通过UTL_FILE包，可以实现对文件的读取、写入和修改等操作。

oracle表数据导出为文本形式

oracle表数据导出为⽂本形式oracle表数据导出⽂本数据（xls或txt）今天试验了两种⽅法，记录如下1.第⼀种⽅法：采⽤utl_file包如下过程即可实现某表数据的导出CREATE OR REPLACE PROCEDURE p_tabletoxls ISv_file utl_file.file_type;CURSOR cur_emp ISSELECT ename, deptno FROM emp;BEGINIF utl_file.is_open(v_file) THENutl_file.fclose(v_file);END IF;v_file := utl_file.fopen('UTL_FILE_DIR', 'emp.xls', 'w');FOR i IN cur_emp LOOPutl_file.put_line(v_file, i.ename || chr(9) || i.deptno); --chr(9)即字段换列END LOOP;utl_file.fclose(v_file);EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLERRM); --写⼊数据IF utl_file.is_open(v_file) THENutl_file.fclose(v_file);END IF;END p_tabletoxls;注：ULT_file包的使⽤要先创建⼀个⽬录存放数据create or replace directory UTL_FILE_DIR as 'D:\dir';grant read,write on directory UTL_FILE_DIR to ltwebgis;2.第⼆种⽅法：采⽤ociuldr⼯具先下载该⼯具，如下所⽰：D:\ociuldr\ociuldr>ociuldr user=username/username@orcl query="SELECT ename, deptno FROM emp"field=0x20 record=0x0a file=emp.xls命令说明：user = username/password@tnsnamesql = SQL file name, one sql per file, do not include ";"query = select statementfield = seperator string between fieldsrecord= seperator string between recordsfile = output file name(default: uldrdata.txt)field=0x20 表⽰字段间⽤空格表⽰，也可以写成field=' '。

Oracle使用UTL_FILE文件包大批量数据导出到CSV(Excel)文件

Oracle使用UTL_FILE文件包大批量数据导出到
CSV(Excel)文件
1.创建测试表
创建多张表，模拟多张表操作。

2.创建初始化表
这张表保存了存储过程执行过程中需要执行的sql语句，格式为select * from table_name（注意：一定不能加分号）。

3.初始化数据
根据需要调整需要导出数据的表（通过调整sql语句实现）。

4.创建目录及授权
如果目录已存在，只需要授权即可
5.创建存储过程
6.执行存储过程
打开数据库系统输出后执行存储过程。

由于文件是以追加的模式打开，所以每次存储过程执行完毕后请重新初始化表EXP_TMP和最终生成的文件，否则下次打开时回忆追加的模式将数据进行合并。

Oracle之UTL_FILE包用法详解

Oracle之UTL_FILE包⽤法详解UTL_FILE包可以⽤来读写操作系统上的⽂本⽂件，UTL_FILE提供了在客户端（FORM等等）和服务器端的⽂件访问功能。

创建测试⽬录：新建⼀个command window；创建⽬录：（以system⽤户登录数据库）Sql代码收藏代码SQL> create or replace directory cux_log_dir as '/home/appltest/debug';Directory created赋权限。

Sql代码收藏代码SQL> grant read, write on directory cux_log_dir to public;Grant succeeded检查⽬录是否成功创建Sql代码收藏代码select * FROM all_directories dir WHERE dir.DIRECTORY_NAME = 'CUX_LOG_DIR';Ps:视图all_directories存放着我们能否访问的⽬录对象。

如果要删除⽬录，也需⽤system⽤户登录数据库，执⾏如下命令：Drop directory cux_log_dir;过程和函数：FOPEN描述：打开⼀个⽂件，基本上在我们对⽂件进⾏读写动作之前都需要先执⾏这个function来打开⽂件先。

语法：UTL_FILE.FOPEN ( location IN VARCHAR2,filename IN VARCHAR2,open_mode IN VARCHAR2,max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN FILE_TYPE;参数：location 略。

Filename 略。

open_mode 指明⽂件打开的模式。

有如下⼏种：■r –只读（⽂本）■ w – 只写（本⽂）■ a – 追加（⽂本）■ rb – 只读（字节）■ wb – 只写（字节）■ ab – 追加（字节）(注：当使⽤模式：a或者ab的时候，如果⽂件不存在，则会以write模式创建此⽂件)max_linesize 指定⽂件⽂本每⼀⾏存放的最⼤字符数。

utl_file包介绍

utl_file 包的应用是非常广泛的，而且也很实用，应为Oracle 虽然有SQLLoader 可以将文本的内容读到数据库里，但是不能将数据库内容输出到文本。

所以基本上是要用到utl_file 包来操作。

今天又要用的时候搜了一下以前的博客，发现都没有涉及到这个，所以今天特别来仔细得了解一下这个包的用法。

先看了一下Oracle 官方文档中的介绍，utl_file 推荐直接使用自己创建的DIRECTORY 来操作文件，而不要继续使用UTL_FILE_DIR 包来指定。

utl_file 包的工作机制是这样的：首纫褂肍OPEN 函数，将文件的路径、文件名、以及打开模式的参数传入，然后Oracle 会到ALL_DIRECTORIES 视图中查看路径是否已经创建。

如果路径和文件名均合法，则该文件被打开到一个file_type 中，然后可以进行各种操作，最后使用FCLOSE 函数将其关闭。

OK 基本上就是这样子了，接下来看一下这个包的内容取自10gR2，加点颜色标注一下，中文的注释都是我加的：--------------------------------------------------------------------- --------------- CREATE OR REPLACE PACKAGE SYS.utl_file AUTHID CURRENT_USER AS / FILE_TYPE - File handle / --utl_file 包的操作都需要指定一个file_type 型的对象用FOPEN 来装入TYPE file_type IS RECORD id BINARY_INTEGER datatypeBINARY_INTEGER byte_mode BOOLEAN / Exceptions / file_open EXCEPTION --打开请求失败文件已经被打开charsetmismatch EXCEPTION --文件使用FOPEN_NCHAR 打开但是进行了nonchar 操作如PUTF/GET_LINE invalid_path EXCEPTION --错误的路径invalid_mode EXCEPTION --提供了FOPEN 不支持的打开模式invalid_filehandle EXCEPTION --文件处理错误invalid_operation EXCEPTION --文件无法打开read_error EXCEPTION --在read 时发生操作系统错误write_error EXCEPTION --在write 时发生操作系统错误internal_error EXCEPTION --未指定的PLSQL 错误invalid_maxlinesize EXCEPTION --FOPEN 的最大行定义错误必须在1 到32767 之间invalid_filename EXCEPTION --文件名错误或无法找到access_denied EXCEPTION --无法进入指定路径invalid_offset EXCEPTION --比较复杂不管了delete_failed EXCEPTION --删除操作发生错误rename_failed EXCEPTION --重命名操作发生错误--以下都是自己定义的Exception Errcode. charsetmismatch_errcode CONSTANT PLS_INTEGER : - 29298invalid_path_errcode CONSTANT PLS_INTEGER : - 29280 invalid_mode_errcode CONSTANT PLS_INTEGER : - 29281 invalid_filehandle_errcode CONSTANTPLS_INTEGER : - 29282 invalid_operation_errcode CONSTANT PLS_INTEGER : - 29283 read_error_errcode CONSTANT PLS_INTEGER : - 29284write_error_errcode CONSTANT PLS_INTEGER : - 29285 internal_error_errcode CONSTANT PLS_INTEGER : - 29286 invalid_maxlinesize_errcode CONSTANT PLS_INTEGER : - 29287 invalid_filename_errcode CONSTANT PLS_INTEGER : - 29288 access_denied_errcode CONSTANT PLS_INTEGER : - 29289invalid_offset_errcode CONSTANT PLS_INTEGER : - 29290 delete_failed_errcode CONSTANT PLS_INTEGER : - 29291 rename_failed_errcode CONSTANTPLS_INTEGER : - 29292 --自定义的错误PRAGMA EXCEPTION_INIT charsetmismatch - 29298 PRAGMA EXCEPTION_INIT invalid_path - 29280 PRAGMA EXCEPTION_INIT invalid_mode - 29281 PRAGMA EXCEPTION_INIT invalid_filehandle - 29282 PRAGMA EXCEPTION_INIT invalid_operation - 29283 PRAGMA EXCEPTION_INIT read_error - 29284 PRAGMA EXCEPTION_INITwrite_error - 29285 PRAGMA EXCEPTION_INIT internal_error - 29286 PRAGMA EXCEPTION_INIT invalid_maxlinesize - 29287 PRAGMA EXCEPTION_INIT invalid_filename - 29288 PRAGMA EXCEPTION_INIT access_denied - 29289 PRAGMA EXCEPTION_INIT invalid_offset - 29290 PRAGMA EXCEPTION_INIT delete_failed - 29291 PRAGMA EXCEPTION_INIT rename_failed - 29292 / FOPEN - open file As of 8.0.6 you can have a maximum of 50 files open simultaneously. As of 9.0.2 UTL_FILE allows file system access for directories created as database objects. See the CREATE DIRECTORY command. Directory object names are case sensitive and must match exactly the NAME string inALL_DIRECTORIES. The LOCATION parameter may be either a directory string from the UTL_FILE_DIR init.ora parameter or a directory object name. IN location - directory location of file filename - file name including extentionopen_mode - open mode r w a rb wb ab --r：读文件清空后再写入--w：写文件--a：追加文件即不删除之前内容--rb：byte mode 读文件清空后再写入--wb：byte mode 写文件--ab：byte mode 追加文件即不删除之前内容max_linesize - maximum number of characters per line including the newline character for this file. Valid values are 1 through 32767 and NULL. A NULL value for max_linesize indicates that UTL_FILE should calculate an operating system specific value atruntime. RETURN file_type handle to open file EXCEPTIONS invalid_path - file location or name was invalid invalid_mode - the open_mode string was invalid invalid_operation - file could not be opened as requested invalid_maxlinesize - specified max_linesize is too large or toosmall access_denied - access to the directory object is denied / FUNCTION fopenlocation IN VARCHAR2 filename IN VARCHAR2 open_mode IN VARCHAR2 max_linesize IN BINARY_INTEGER DEFAULT NULL RETURN file_type PRAGMA RESTRICT_REFERENCES fopen WNDS RNDS TRUST / FOPEN_NCHAR - open file Note: since NCHAR contains mutibyte character it is highly recommended that the max_linesize is less than 6400. / FUNCTIONfopen_ncharlocation IN VARCHAR2 filename IN VARCHAR2 open_mode IN VARCHAR2 max_linesize IN BINARY_INTEGER DEFAULT NULL RETURN file_type PRAGMA RESTRICT_REFERENCES fopen_nchar WNDS RNDS TRUST / IS_OPEN - Test if file handle is open IN file - File handleRETURN BOOLEAN - Is file handle open/valid / FUNCTION is_openfile IN file_type RETURN BOOLEAN PRAGMA RESTRICT_REFERENCES is_open WNDS RNDS WNPS RNPS TRUST / FCLOSE - close an open file IN file - File handle open EXCEPTIONS invalid_filehandle - not a valid file handle write_error - OS error occured during write operation / PROCEDURE fclosefile IN OUT file_type PRAGMA RESTRICT_REFERENCES fclose WNDS RNDS TRUST /FCLOSE_ALL - close all open files for this session For Emergency/Cleanup use only. FILE_TYPE handles will not be cleared IS_OPEN will still indicate they are validIN file - File handle open EXCEPTIONS write_error - OS error occured during write operation / PROCEDURE fclose_all PRAGMA RESTRICT_REFERENCES fclose_all WNDS RNDS TRUST / GET_LINE - Get read a line of text from the file IN file - File handle open in read mode len - input buffer length default is null max is 32767 OUT buffer - next line of text in file EXCEPTIONS no_data_found - reached the end of file value_error - line to long to store in buffer invalid_filehandle - not a valid file handle invalid_operation - file is not open for reading - file is openfor byte mode access read_error - OS error occurred during read charsetmismatch - if the file is open for nchar data. / --注意这个函数会读取一行一直到line terminator然后下一次读取时接着下面的内容--当到达最后一行时返回no_data_found 错误PROCEDURE get_linefile IN file_type buffer OUT VARCHAR2 len INBINARY_INTEGER DEFAULT NULL PRAGMA RESTRICT_REFERENCESget_line WNDS RNDS WNPS RNPS TRUST / GET_LINE_NCHAR - Get read a line of nchar data from the file. IN file - File handle open in read mode len - input buffer length default is null max is 32767 OUT buffer - next line of text in file the data might be convert from UTF8 to current charset. EXCEPTIONSno_data_found - reached the end of file value_error - line to long to store in buffer invalid_filehandle - not a valid file handle invalid_operation - file is not open for reading - file is open for byte mode access read_error - OS error occurred during read charsetmismatch - if the file is open for char data. / PROCEDURE get_line_ncharfile IN file_type buffer OUT NVARCHAR2 len IN BINARY_INTEGER DEFAULTNULL PRAGMA RESTRICT_REFERENCES get_line_nchar WNDS RNDS WNPS TRUST / PUT - Put write text to file IN file - File handle open in write/append mode buffer - Text to write EXCEPTIONS invalid_filehandle - not a valid file handle invalid_operation - file is not open for writing/appending - file is open for byte mode access write_error - OS error occured during write operation charsetmismatch - if the file is open for nchar data. / --这个就是没有换行符的PUT_LINE需要自己加NEW_LINE PROCEDURE putfile IN file_type buffer IN VARCHAR2 PRAGMA RESTRICT_REFERENCES put WNDS RNDS TRUST / PUT_NCHAR - Put write nchar data to file IN file - File handle open in write/append mode buffer - Text to write. the data will convert to UTF8 if needed. EXCEPTIONSinvalid_filehandle - not a valid file handle invalid_operation - file is not open for writing/appending - file is open for byte mode access write_error - OS error occured during write operation charsetmismatch - if the file is open for char data. / PROCEDURE put_ncharfile IN file_type buffer IN NVARCHAR2 PRAGMA RESTRICT_REFERENCES put_nchar WNDS RNDS TRUST / NEW_LINE - Write line terminators to file IN file - File handle open in write/append mode lines - Number of newlines to write default 1 EXCEPTIONS invalid_filehandle - not a valid file handle invalid_operation - file is not open for writing/appending - file is open for byte mode access write_error - OS error occured during write operation/--表示在这个位置插入一个换行符也可以指定空的行数--这个还是比较实用的PROCEDURE new_linefile IN file_type lines IN NATURAL : 1 PRAGMA RESTRICT_REFERENCES new_line WNDS RNDS TRUST / PUT_LINE - Put write line to file IN file - File handle open in write/append mode buffer - Text to write. autoflush - Flush following write defaultno flush EXCEPTIONS invalid_filehandle - not a valid file handle invalid_operation - file is not open for writing/appending - file is open for byte mode access write_error - OS error occured during write operation charsetmismatch - if the file is open for nchardata./PROCEDURE put_linefile IN file_type buffer IN VARCHAR2 autoflush IN BOOLEAN DEFAULT FALSE PRAGMA RESTRICT_REFERENCES put_line WNDS RNDS TRUST / PUT_LINE_NCHAR - Put write line of nchar data to file IN file - File handle open in write/append mode buffer - Text to write. The data might convert to UTF8 if needed. EXCEPTIONS invalid_filehandle - not a valid file handle invalid_operation - file is not open for writing/appending - file is open for byte mode access write_error - OSerror occured during write operation charsetmismatch - if the file is open for char data./ PROCEDURE put_line_ncharfile IN file_type buffer IN NVARCHAR2 PRAGMA RESTRICT_REFERENCES put_line_nchar WNDS RNDS TRUST / PUTF - Put write formatted text to file Format string special characters s - substitute with next argument n - newline line terminator IN file - File handle open in write/append mode format - Formatting string arg1 - Substitution argument 1 ... EXCEPTIONS invalid_filehandle - not a valid file handle invalid_operation - file is not open for writing/appending - file is open for byte mode access write_error - OS error occured during write operation charsetmismatch - if the file is open for nchar data. / / 这是一个输入格式化内容的过程定义，相当于C 语言中的printf，具体的可以看下面这个例子：Hello world I come from Zork with greetings for all earthlings. my_world varchar24 : Zork ... PUTFmy_handle Hello worldnI come from s with s.n my_world greetings for all earthlings / procedure putffile IN file_type format IN VARCHAR2 arg1 IN VARCHAR2 DEFAULT NULL arg2 IN VARCHAR2 DEFAULT NULLarg3 IN VARCHAR2 DEFAULT NULL arg4 IN VARCHAR2 DEFAULT NULLarg5 IN VARCHAR2 DEFAULT NULL PRAGMA RESTRICT_REFERENCES putf WNDS RNDS TRUST / PUTF_NCHAR - Put write formatted text to file Format string special characters Ns - substitute with next argument Nn - newline line terminator IN file - File handle open in write/append mode format - Formatting string arg1 - Substitution argument 1 ... EXCEPTIONS invalid_filehandle - not a valid file handle invalid_operation - file is not open for writing/appending - file is open for byte mode access write_error - OS error occured during write operation charsetmismatch - if the file is open for char data. / procedure putf_ncharfile IN file_type format IN NVARCHAR2 arg1 IN NVARCHAR2 DEFAULT NULL arg2 IN NVARCHAR2 DEFAULT NULL arg3 IN NVARCHAR2 DEFAULT NULL arg4 IN NVARCHAR2 DEFAULT NULL arg5 IN NVARCHAR2 DEFAULT NULL PRAGMA RESTRICT_REFERENCES putf_nchar WNDS RNDS TRUST / FFLUSH - Force physical write of buffered output IN file - File handle open in write/append mode EXCEPTIONS invalid_filehandle - not a valid file handle .。

UTL_FILE用法详解

包UTL_FILE 提供了在操作系统层面上对文件系统中文件的读写功能。

非超级用户在使用包UTL_FILE中任何函数或存储过程前必须由超级用户授予在这个包上的EXECUTE权限。

例如：我们使用下列命令对用户mary进行授权：GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TO mary;如果使用包UTL_FILE中的函数和存储过程访问文件，那么操作系统中的用户enterprisedb必须在要访问的目录和文件上有相应的读写权限。

如果没有相应权限的话，在执行包UTL_FILE中函数或存储过程的时候，就会产生异常。

在引用文件的时候，要使用到一个文件句柄，来表示对文件的读或写。

文件句柄是通过包UTL_FILE中名称为UTL_FILE.FILE_TYPE的公有变量来定义的。

我们必须声明一个类型为FILE_TYPE的变量来接收通过函数FOPEN返回的文件句柄。

这个文件句柄将用于随后在文件上的所有操作。

对于文件系统上目录的引用是通过使用目录名称，或者由CREATE DIRECTORY命令为目录分配的化名来实现的。

UTL_FILE常用方法：FOPENIS_OPENGET_LINEPUTNEW_LINEPUT_LINEPUTFFFLUSHFCLOSEFCLOSE_ALLFRENAMEFREMOVEFFLUSHFCOPYUTL_FILE.FOPEN用法FOPEN会打开指定文件并返回一个文件句柄用于操作文件。

适用范围：所有PL/SQL版本，Oracle 8.0版及以上。

函数原型：FUNCTION UTL_FILE.FOPEN ( FUNCTION UTL_FILE.FOPEN ( location IN VARCHAR2, location IN VARCHAR2,filename IN VARCHAR2, filename IN VARCHAR2,open_mode IN VARCHAR2) open_mode IN VARCHAR2,RETURN file_type; max_linesize IN BINARY_INTEGER)RETURN file_type;参数location 文件地址filename 文件名openmode 打开文件的模式（参见下面说明）3种文件打开模式：R 只读模式。