使用PL/SQL将数据从file.txt导入表Oracle SQL

Question

我正试图从c:\Dir读取一个txt类型的文件，并将内容插入表Oracle Sql

set SERVEROUTPUT ON
CREATE OR REPLACE DIRECTORY MYDIR AS ' C:dir';
DECLARE
  vInHandle  utl_file.file_type;
  eNoFile    exception;
  PRAGMA exception_init(eNoFile, -29283);
BEGIN
  BEGIN
    vInHandle := utl_file.Fopen('MYDIR','attachment.txt','R');
    dbms_output.put_line('The File exists');
  EXCEPTION
    WHEN eNoFile THEN
      dbms_output.put_line('The File not  exists');
  END;
END fopen;
/

我的文件不存在，但我有这个文件

Answer 1

我不知道你发布的第一条语句中目录名前面的空格是否有区别(或者只是打字错误(，但尽管如此，以下是通常的操作方法。

在硬盘上创建目录：

C:>mkdir c:dir

以SYS的身份连接到数据库(因为它拥有数据库和目录(；创建目录(Oracle对象(并将权限授予将使用该目录的用户：

C:>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on ╚et O×u 5 18:34:43 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> create or replace directory mydir as 'c:dir';
Directory created.
SQL> grant read, write on directory mydir to scott;
Grant succeeded.
SQL>

---

你不需要这个，因为你已经有了文件；我将通过后台处理表内容来创建它。

SQL> connect scott/tiger
Connected.
SQL> spool c:direxample.txt
SQL> select * From dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
SQL> spool off;
SQL> $dir c:dir*.txt
 Volume in drive C is OSDisk
 Volume Serial Number is 7635-F892
 Directory of c:dir
05.03.2020.  18:39               539 example.txt
               1 File(s)            539 bytes
               0 Dir(s)  290.598.363.136 bytes free
SQL>

---

最后，重用您编写的代码：

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    vInHandle  utl_file.file_type;
  3    eNoFile    exception;
  4    PRAGMA exception_init(eNoFile, -29283);
  5  BEGIN
  6    BEGIN
  7      vInHandle := utl_file.Fopen('MYDIR','example.txt','R');
  8      dbms_output.put_line('The File exists');
  9    EXCEPTION
 10      WHEN eNoFile THEN
 11        dbms_output.put_line('The File not  exists');
 12    END;
 13  END fopen;
 14  /
The File exists
PL/SQL procedure successfully completed.
SQL>

工作正常(祝贺你，你写的代码确实有效！(。

---

那么，你做错了什么？

• 正如我所说，c:dir:CREATE OR REPLACE DIRECTORY MYDIR AS ' C:dir';前面的空间
• 数据库不在您的计算机上，而是在一个单独的数据库服务器上
  • 这意味着您可能创建了目录，但它指向数据库服务器上的c:dir目录，而不是您自己的PC

正如Boneist所评论的，可以在不是数据库服务器的计算机上创建目录(Oracle对象(，但这不是我们通常要做的事情。如果您选择此选项，则在创建目录时必须使用UNC(通用命名约定(。

---

您可能需要考虑的另一个选项是使用SQL加载器。它是一个操作系统实用程序，与数据库或(完整的，而不是即时的(客户端软件一起安装。它的优点是它运行在您的本地PC上(即，您不必访问数据库服务器(，而且速度极快。您可以创建一个控制文件，告诉Oracle如何加载存储在源文件(.txt(中的数据。

另一个在后台使用SQL加载器的选项是使用外部表。它是另一个指向源(.txt(文件的Oracle对象，并允许您使用简单的SQL SELECT语句访问它。可能的缺点：它仍然需要访问Oracle目录(就像您的UTL_FILE选项一样(。