Oracl中的使用utl_file来读取txt文件，数据插入数据库

Question

自己试写了一下，功能没有实现，现在读取文件时，出现错误（执行到红色文字跳到异常）。
create or replace package body TEST_READ_FILE1 is
    c_process_sts_ok   BINARY_INTEGER := 0;  --
    c_process_sts_error BINARY_INTEGER := -1; --失敗
PROCEDURE PROC_WRITE_DAT(
o_sts  OUT BINARY_INTEGER,
o_errm OUT VARCHAR2)
IS
PROCESS_ERROR EXCEPTION;
w_cursor T_CURSOR;
w_content VARCHAR2(4000);
w_file_name VARCHAR2(200);
w_fexist_flg BOOLEAN;
        w_fopen_flg BOOLEAN;
w_flength NUMBER;
w_block_size BINARY_INTEGER;
  
  vSFile UTL_FILE.FILE_TYPE;
  vNewLine VARCHAR2(2000);
  w_errm   VARCHAR2(100);
BEGIN
  o_sts := c_process_sts_ok;   vSFile := utl_file.fopen('E:\TEST\','test.txt','r');
  
  w_fopen_flg := utl_file.is_open(vSFile);
  IF NOT w_fopen_flg THEN
     w_errm :='file is not open.';
     RAISE PROCESS_ERROR;
  END IF;
  
  utl_file.fgetattr(c_gst_filepath,w_file_name,w_fexist_flg,w_flength,w_block_size);
      IF NOT w_fexist_flg THEN
w_errm :='file is not have.';
RAISE PROCESS_ERROR;
      END IF;  IF utl_file.is_open(vSFile) THEN
     LOOP
        BEGIN
           utl_file.get_line (vSFile, vNewLine);
           IF vNewLine IS NULL THEN
              EXIT;
           END IF;
           FETCH w_cursor INTO w_content;
           
           
           EXIT WHEN w_cursor%NOTFOUND;
           
           --INSERT INTO test(fld1, fld2) VALUES(vNewLine, file_name);
        END;
     END LOOP;
     CLOSE w_cursor;
     COMMIT;
  END IF;
EXCEPTION
    WHEN  PROCESS_ERROR THEN
  ROLLBACK;
  o_sts:=c_process_sts_error;
          o_errm :=w_errm;
    WHEN  OTHERS  THEN
  ROLLBACK;
          o_sts:=c_process_sts_error;
          o_errm :=w_errm;
END ;
end TEST_READ_FILE1;
请各位帮我看看，错在哪里，完善一下功能，谢谢了！

Answer 1

改成vSFile := utl_file.fopen('E:\TEST','test.txt','r'); 不行的话在异常处理中打印异常结果。
DBMS_OUTPUT.PUT_LINE( dbms_utility.format_error_backtrace );

Answer 2

Use UTL_FILE to read from a file and insert into the lecturer table.CREATE TABLE lecturer (
  id               NUMBER(5) PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3)
  );INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
              VALUES (10002, 'Mar', 'Wells','History', 4);INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10004, 'Man', 'Kyte','Economics', 8);INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10005, 'Pat', 'Poll','History', 4);INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10006, 'Tim', 'Viper','History', 4);CREATE OR REPLACE PROCEDURE Loadlecturer (
  p_FileDir  IN VARCHAR2,
  p_FileName IN VARCHAR2,
  p_TotalInserted IN OUT NUMBER) AS  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NewLine  VARCHAR2(100);  -- Input line
  myFirstName lecturer.first_name%TYPE;
  v_LastName lecturer.last_name%TYPE;
  v_Major lecturer.major%TYPE;  v_FirstComma NUMBER;
  v_SecondComma NUMBER;BEGIN
  v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, 'r');  p_TotalInserted := 0;  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;    v_FirstComma := INSTR(v_NewLine, ',', 1, 1);
    v_SecondComma := INSTR(v_NewLine, ',', 1, 2);    myFirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
    v_LastName := SUBSTR(v_NewLine, v_FirstComma + 1,
                         v_SecondComma - v_FirstComma - 1);
    v_Major := SUBSTR(v_NewLine, v_SecondComma + 1);    INSERT INTO lecturer (ID, first_name, last_name, major) VALUES (1, myFirstName, v_LastName, v_Major);    p_TotalInserted := p_TotalInserted + 1;
  END LOOP;  UTL_FILE.FCLOSE(v_FileHandle);  COMMIT;
EXCEPTION
  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20051, 'Loadlecturer: Invalid Operation');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20052, 'Loadlecturer: Invalid File Handle');
  WHEN UTL_FILE.READ_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20053, 'Loadlecturer: Read Error');
  WHEN UTL_FILE.INVALID_PATH THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20054, 'Loadlecturer: Invalid Path');
  WHEN UTL_FILE.INVALID_MODE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20055, 'Loadlecturer: Invalid Mode');
  WHEN UTL_FILE.INTERNAL_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20056, 'Loadlecturer: Internal Error');
  WHEN VALUE_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20057, 'Loadlecturer: Value Error');
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE;
END Loadlecturer;
/drop table lecturer;

Answer 3

to 2楼
谢谢你给我的例子，很不错。
我想问一下，我的txt文件里的内容是：
M000219305,G83C00067110,10716,ABCD,0,20080307121211,2008
M000219305,G83C00067110,123456789012,1234567890,0,20080307121212,2008
M000219305,PMA500065012,10716,222222,0,20080307121220,2008
数据是用“，”来分隔的，取到数据后再将其插入相应数据库字段。你的例子中：v_FirstComma := INSTR(v_NewLine, ',', 1, 1);的执行结果是？？？

Answer 4

你的oracle有权限访问这个文件夹么？create directory.....  然后查看文件夹属性，添加ora_dba用户访问权限

Answer 5

楼上说的对，create directory并附上权限。

Answer 6

错误排除了，谢谢大家！
Create or replace directory UCE_DIR  AS 'E:\TEST';
vSFile := utl_file.fopen('UCE_DIR','test.txt','r');
下一个问题是：
我的txt文件里的内容是： 
M000219305,G83C00067110,10716,ABCD,0,20080307121211,2008 
M000219305,G83C00067110,123456789012,1234567890,0,20080307121212,2008 
M000219305,PMA500065012,10716,222222,0,20080307121220,2008 
数据是用“，”来分隔的，取到数据后再将其插入相应数据库字段。 
我怎么才能把数据分离出来，一行对应一条记录。

Answer 7

上述问题也解决了，但又出现了问题，就是用SUBSTR()截取出来的文字是乱码。
有人说用这个方法能解决，但我试了，不好使呀，是不是我使用的不用呀？utl_file.get_line (vSFile, vNewLine);
value1:=CONVERT(substr(vNewLine,3,40), 'UTF8', 'ZHT16BIG5');哈哈，新手的问题就是很多呀！