读取文本文件以将数据插入Oracle SQL表 [英] Read text file to insert data into Oracle SQL table

问题描述

我正在研究Oracle SQL开发人员.

I am studying Oracle SQL developer.

我正在做的是从文件夹中逐行读取文本文件. 然后将数据插入到SQL表中.

What I am doing is reading text file line by line from the folder. Then Inserting data to the SQL table.

我可以编译我的程序，但是，似乎没有将数据插入文件中.

I am able to compile my PROCEDURE, however, it doesn't seem to be inserting the data to file.

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;

我有一个带有两个varchar(200)类型cols的表DATAINSERT

And I have a table DATAINSERT with two varchar(200) type cols

我不太确定PROCEDURE没有向表中插入数据的原因

I am not really sure the reasons that PROCEDURE is not inserting data to table

我刚刚检查了错误消息

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

推荐答案

不确定导致问题的原因.对我来说，这是我的示例代码

Not sure what causing problems. For me its working fine here is my example code

-参考站点 - 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;

这篇关于读取文本文件以将数据插入Oracle SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！