读取文本文件以将数据插入到 Oracle SQL 表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14823382/
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
Read text file to insert data into Oracle SQL table
提问by Dc Redwing
I am studying Oracle SQL developer.
我正在学习 Oracle SQL 开发人员。
What I am doing is reading text file line by line from the folder. Then Inserting data to the SQL table.
我正在做的是从文件夹中逐行读取文本文件。然后将数据插入到 SQL 表中。
I am able to compile my PROCEDURE, however, it doesn't seem to be inserting the data to file.
我能够编译我的 PROCEDURE，但是，它似乎没有将数据插入到文件中。
Create or Replace PROCEDURE Rfile is
f UTL_FILE.FILE_TYPE;
s VARCHAR2(200);
BEGIN
f := UTL_FILE.FOPEN('C:\Projects\','testdatabinary.txt','R');
IF UTL_FILE.IS_OPEN(f) THEN
LOOP
BEGIN
UTL_FILE.GET_LINE(f,s);
IF s IS NULL THEN
EXIT;
END IF;
INSERT INTO DATAINSERT
(COLUMN1, COLUMN2)
VALUES
(s, 'testdatabinary');
END;
END LOOP;
COMMIT;
END IF;
END;
And I have a table DATAINSERT with two varchar(200) type cols
我有一个带有两个 varchar(200) 类型列的表 DATAINSERT
I am not really sure the reasons that PROCEDURE is not inserting data to table
我不太确定 PROCEDURE 没有将数据插入表的原因
I just checked error message
我刚刚检查了错误消息
Error starting at line 1 in command:
EXEC Rfile
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "SYSTEM.RFILE", line 5
回答by laks
Not sure what causing problems. For me its working fine here is my example code
不确定是什么导致了问题。对我来说，它工作正常，这是我的示例代码
--Reference Site --https://community.oracle.com/thread/3633577?start=0&tstart=0
-- 参考站点 -- https://community.oracle.com/thread/3633577?start=0&tstart=0
set serveroutput on;
CREATE or replace DIRECTORY USER_DIR AS '/home/oracle';
GRANT READ ON DIRECTORY USER_DIR TO PUBLIC;
DECLARE
V1 VARCHAR2(200); --32767
F1 UTL_FILE.FILE_TYPE;
BEGIN
F1 := UTL_FILE.FOPEN('USER_DIR','temp.txt','R');
Loop
BEGIN
UTL_FILE.GET_LINE(F1,V1);
dbms_output.put_line(V1);
EXCEPTION WHEN No_Data_Found THEN EXIT; END;
end loop;
IF UTL_FILE.IS_OPEN(F1) THEN
dbms_output.put_line('File is Open');
end if;
UTL_FILE.FCLOSE(F1);
END;
/
set serveroutput off;
回答by Nizar
You can't able to enter the path directly to the file open command like this
你不能像这样直接输入文件打开命令的路径
f := UTL_FILE.FOPEN('C:\Projects\','testdatabinary.txt','R');
Instead of entering path directly, you have to create directory using the below sql query
您必须使用以下 sql 查询创建目录，而不是直接输入路径
CREATE or replace DIRECTORY USER_DIR AS 'C:\PROJECTS\';
创建或替换 DIRECTORY USER_DIR AS 'C:\PROJECTS\';
Then enter the directory name to the file open command. It will be like this
然后在文件打开命令中输入目录名。会是这样
f := UTL_FILE.FOPEN('USER_DIR ','testdatabinary.txt','R');
回答by Mahesh
First procedure trying to load the file from PC into the server whereas in second procedure file is from server to server. UTL_FILE.FOPENworks for server-side PL/SQL. So first procedure wont execute properly... If you want to load a file from PC to server, then you need to user any front end like D@K or VB.
第一个过程试图将文件从 PC 加载到服务器，而在第二个过程中，文件是从服务器到服务器。UTL_FILE.FOPEN适用于服务器端 PL/SQL。所以第一个程序不会正确执行...如果你想从 PC 加载文件到服务器，那么你需要使用任何前端，如 D@K 或 VB。
回答by Prince Sharma
The mistake your making here is using the workstation path, you actually need to be using the directory path.
您在这里犯的错误是使用工作站路径，您实际上需要使用目录路径。
The quickest way to resolve this, is just to use the WINSCPcommand.
解决这个问题的最快方法就是使用WINSCP命令。
Use WINSCPto tell you what the directory path is, then simply substitute this new information for the old path you have in your code, and everything should work ok.
使用WINSCP告诉您目录路径是什么，然后只需将此新信息替换为您代码中的旧路径，一切都应该正常。
回答by Sapna
This is very conceptual and tricky. which you can find more concepts and details below: https://ora-data.blogspot.in/2016/11/read-and-writ-text-file-using-UTLFILE.html
这是非常概念化和棘手的。您可以在下面找到更多概念和详细信息：https: //ora-data.blogspot.in/2016/11/read-and-writ-text-file-using-UTLFILE.html
Sure will be helpful:
肯定会有帮助：
create or replace procedure read_file is
f_line varchar2(2000);
f utl_file.file_type;
f_dir varchar2(250);
fname varchar2(50);
Comma1 varchar(10);
Comma2 varchar(10);
Comma3 varchar(10);
Comma4 varchar(10);
Comma5 varchar(10);
f_empno emp.empno%type;
f_ename emp.ename%type;
f_job emp.job%type;
f_mgr emp.mgr%type;
f_hiredate emp.hiredate%type;
f_sal emp.sal%type;
begin
f_dir := ‘E:\PLSQL';
fname := ‘input.txt';
f := utl_file.fopen(‘UTL_FILE_DIR',fname,'r'); –opening the file using fopen function
loop
begin
utl_file.get_line(f,f_line);
–using a loop continuously get the file's content using get_line function exception when no_data_found then
exit;
end;
Comma1 := INSTR(f_line, ‘,' ,1 , 1);
Comma2 := INSTR(f_line, ‘,' ,1 , 2);
Comma3 := INSTR(f_line, ‘,' ,1 , 3);
Comma4 := INSTR(f_line, ‘,' ,1 , 4);
Comma5 := INSTR(f_line, ‘,' ,1 , 5);
–Each field in the input record is delimited by commas.
–We need to find the location of two commas in the line.
–and use the locations to get the field from the line.
f_empno := to_number(SUBSTR(f_line, 1, Comma1-1));
f_ename := SUBSTR(f_line, Comma1+1, Comma2-Comma1-1);
f_job := SUBSTR(f_line, comma2+1, Comma3-Comma2-1);
f_mgr := to_number(SUBSTR(f_line, comma3+1, Comma4-Comma3-1));
f_hiredate := to_date(SUBSTR(f_line, comma4+1, Comma5-Comma4-1),'dd-mon-yyyy');
f_sal := to_number(SUBSTR(f_line, comma5+1),'99999′); dbms_output.put_line(f_empno ||' ‘|| f_ename || ‘ ‘ || f_job || ‘ ‘ || f_mgr ||' ‘ || f_hiredate||' ‘|| f_sal);
insert into emp12 VALUES (f_empno,f_ename,f_job,f_mgr,f_hiredate,f_sal);
end loop;
utl_file.fclose(f);
commit;
end;
/
回答by Abhinav
first login with
第一次登录
username:sys as sysdba
用户名：sys 作为 sysdba
password should be the same as used for user 'system'
密码应与用户“系统”使用的相同
now enter
现在输入
SQL> grant execute on UTL_FILE to PUBLIC;
SQL> 将 UTL_FILE 上的执行授权给 PUBLIC；
Now login with any user with which you want to create procedure
现在使用您要创建过程的任何用户登录
