oracle 如何使用PLSQL程序从文本文件插入Oracle 10g中的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9902492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert into Table in Oracle 10g from Text file using PLSQL program?
提问by Balakumaran
I'm trying this PL/SQL program in Oracle 10g. To Read text file (data) from loaction 'C:\Oracle' and load it into Oracle Table using PLSQL script. But, I'm getting the following errors:
我正在 Oracle 10g 中尝试这个 PL/SQL 程序。从 loaction 'C:\Oracle' 读取文本文件（数据）并使用 PLSQL 脚本将其加载到 Oracle 表中。但是，我收到以下错误：
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
Here's my Script:
这是我的脚本：
Create or Replace PROCEDURE Rfile is
line VARCHAR2(100);
namesfile UTL_FILE.FILE_TYPE;
BEGIN
-- Syntax : FOPEN ( directory alias, filename, open mode)
namesfile := UTL_FILE.FOPEN('FILESDIR1','NAMES2.TXT','R'); -- open in read mode
LOOP
UTL_FILE.GET_LINE(namesfile,line,100);
dbms_output.put_line(line);
insert into names2 values(line); -- insert into NAMES table
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Others exceptions....');
END;
回答by A.B.Cade
You can try a different approach:
Create an external table, which is a table mapped to a file, to read the file and then just insert-selectto your table from the external
(or maybe you don't even need to insert it to a different table ?)
您可以尝试不同的方法：
创建一个外部表，它是一个映射到文件的表，以读取文件，然后insert-select从外部读取到您的表
（或者您甚至不需要将其插入到不同的表中） ?)
回答by Rob Heusdens
Your loop does not check for end-of-file, so logically at some point the file read operation should raise an exception, and that is the one you got presumably.
您的循环不检查文件结尾，因此从逻辑上讲，文件读取操作在某些时候应该引发异常，而这可能是您得到的异常。
Also you have to check that the directory (FILESDIR1) is pointing to the right OS directory AND you have been granted access to that directory, and the file is on that OS directory (not on your local file system).
此外，您还必须检查目录 (FILESDIR1) 是否指向正确的操作系统目录，并且您已被授予对该目录的访问权限，并且该文件位于该操作系统目录中（不在您的本地文件系统中）。
Btw. in some cases you could better use SQL Loader to bulk load data in a table, esp. if the file is large because you can direct SQL loader to directly load the data in the datafiles, bypassing the SQL layers (generated by the INSERT statements) all together.
顺便提一句。在某些情况下，您可以更好地使用 SQL Loader 批量加载表中的数据，尤其是。如果文件很大，因为您可以直接 SQL 加载器直接加载数据文件中的数据，绕过 SQL 层（由 INSERT 语句生成）。
回答by cagcowboy
You may need a lowercase 'r' on this line...
您可能需要在此行中使用小写的“r”...
namesfile := UTL_FILE.FOPEN('FILESDIR1','NAMES2.TXT','r'); -- open in read mode
^
