Oracle定时抓取Excel（csv格式）数据写入数据库表中

本文链接：https://blog.csdn.net/hellotomo/article/details/82177876

Oracle访问文件需要通过Oracle内建包UTL_FILE,需要授予权限：

CREATE DIRECTORY TEMP AS 'D:/test'; --建立目录并授权 ，TEMP是赋予的路径名
GRANT READ,WRITE ON DIRECTORY TEMP TO CTMPMART_WF; --授予读写目录的权限
GRANT   EXECUTE ON SYS.UTL_FILE TO CTMPMART_WF; --赋予用户UTL_FILE的权限

创建的存储过程如下：

CREATE OR REPLACE PROCEDURE UPDATE_CUSTOMER_RANK_BYFILE

    AUTHID CURRENT_USER AS
      V_FILE_HANDLE UTL_FILE.FILE_TYPE;
      V_FILEPATH    VARCHAR2(100);
      V_FILENAME    VARCHAR2(100);
      V_SQL         VARCHAR2(2000);
      v_sqlfalg     VARCHAR2(2000);
      SUPMART_NAME  VARCHAR2(100);
      IN_DATE VARCHAR2(100);
      SUPPLIER_NAME VARCHAR2(100);
      GOODS_CODE VARCHAR2(100);
      GOODS_NAME VARCHAR2(100);
      WEIGHT VARCHAR2(100);
      LEGAL_PEPRESENT varchar2(100);
      PRICE VARCHAR2(100);
      SERIAL_ID VARCHAR(100);
      BATCH_ID VARCHAR(16);
      AREA_CODE VARCHAR(6);
      AREA_NAME VARCHAR(50);
      PROV_ID VARCHAR(20);
      SUPER_COMP_ID   VARCHAR(100);
      SUPER_COMP_NAME VARCHAR(100);
      V_ROWS NUMBER(10);
      SUPPLIER_ID VARCHAR(13);
      IS_EXISTS NUMBER(10);
      MAX_ID    NUMBER(10);
      COL1      VARCHAR2(1000);
      GOODS_TYPE     VARCHAR2(500);
     type c_cursor       IS REF CURSOR;
     c1 c_cursor;
     --V_ROWS NUMBER(10);
    BEGIN
      insert into ctmpmart_wf.test_job_new(DATENOW)values(sysdate);
      V_FILEPATH:='D:/test';
      V_FILENAME:=to_char(sysdate,'yyyymmdd')||'.csv';
      V_FILE_HANDLE := UTL_FILE.FOPEN('MYFILEDIR', V_FILENAME, 'R');
      V_ROWS        := 0; --处理记录数
      LOOP
        BEGIN
          --将文件信息读取到COL1中,每次读取一行
          UTL_FILE.GET_LINE(V_FILE_HANDLE, COL1);

         IF substr(COL1,0,4)='超市每日'or substr(COL1,0,4)='超市名称' THEN
          continue;
         END IF;

          SUPER_COMP_NAME :=substr(COL1,0,INSTR(COL1,',')-1);
         select COMP_ID,LEGAL_PEPRESENT INTO SUPER_COMP_ID,LEGAL_PEPRESENT FROM ctmpmart_wf.base_node_info WHERE COMP_NAME = SUPER_COMP_NAME;

           SUPMART_NAME := substr(COL1,1,INSTR(COL1,',',1,1)-1);--csv文件中第一个字段（超市名称）
           IN_DATE := substr(COL1, INSTR(COL1,SUPMART_NAME)+LENGTH(SUPMART_NAME)+1,INSTR(COL1,',',1,2)-INSTR(COL1,SUPMART_NAME)-LENGTH(SUPMART_NAME)-1); --csv文件中第二个字段（进场时间）
           GOODS_CODE := substr(COL1,INSTR(COL1,IN_DATE)+LENGTH(IN_DATE)+1,INSTR(COL1,',',1,3)-INSTR(COL1,IN_DATE)-LENGTH(IN_DATE)-1); --csv文件中第三个字段（供货商）
           GOODS_NAME :=substr(COL1,INSTR(COL1,GOODS_CODE)+LENGTH(GOODS_CODE)+1,INSTR(COL1,',',1,4)-INSTR(COL1,GOODS_CODE)-LENGTH(GOODS_CODE)-1); --csv文件中第四个字段（商品编码）
           WEIGHT := substr(COL1,INSTR(COL1,GOODS_NAME)+LENGTH(GOODS_NAME)+1,INSTR(COL1,',',1,5)-INSTR(COL1,GOODS_NAME)-LENGTH(GOODS_NAME)-1); --csv文件中第五个字段（重量）
           PRICE := substr(COL1,INSTR(COL1,',',1,5)+1,INSTR(COL1,',',1,6)-INSTR(COL1,',',1,5)-1); --csv文件中第六个字段（单价）
           AREA_CODE:=substr(COL1,INSTR(COL1,',',1,7)+1,INSTR(COL1,',',1,8)-INSTR(COL1,',',1,7)-1);
           AREA_NAME:=substr(COL1,INSTR(COL1,',',1,8)+1);
           GOODS_TYPE:=substr(COL1,INSTR(COL1,',',1,6)+1,INSTR(COL1,',',1,7)-INSTR(COL1,',',1,6)-1);
           --生成新的追溯码和批次号
           SELECT ID INTO SERIAL_ID FROM ctmpmart_wf.SERIAL_CODE WHERE NAME = SUPER_COMP_NAME;
           GETSERTALCODE(SERIAL_ID,BATCH_ID); --执行存储过程，生成批次号
           SELECT ID INTO SERIAL_ID FROM ctmpmart_wf.SERIAL_CODE WHERE NAME = '追溯码:'||SUPER_COMP_NAME||':'||LEGAL_PEPRESENT;

           GETSERTALCODE(SERIAL_ID,PROV_ID);
           if GOODS_TYPE=1 then
              --如果商品类型为1，代表是菜
              execute immediate 'INSERT INTO ctmpmart_wf.TB_SUPER_MAR_VEG_IN_INFO
                (SUPERMARKET_ID,SUPERMARKET_NAME,IN_DATE,BATCH_ID,PROV_ID,SUPPLIER_ID,SUPPLIER_NAME,VEG_CODE,VEG_NAME,WEIGHT,PRICE,AREA_ORIGIN_ID,AREA_ORIGIN_NAME,SALE_TRAN_ID,VOURCHER_TYPE,LOAD_TIME,IF_BATCH_ID,IS_FORMAT)
                VALUES  (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)'
              using SUPER_COMP_ID,SUPMART_NAME,to_date(IN_DATE,'yyyy/mm/dd'),BATCH_ID,PROV_ID,'3707250040000','潍坊市皓远食品有限公司',GOODS_CODE,GOODS_NAME,WEIGHT,PRICE,AREA_CODE,AREA_NAME,PROV_ID,'1',SYSDATE(),0,1;
             --INSERT INTO TB_SUPER_MAR_VEG_IN_INFO
             --(SUPERMARKET_ID,SUPERMARKET_NAME,IN_DATE,BATCH_ID,PROV_ID,SUPPLIER_ID,SUPPLIER_NAME,VEG_CODE,VEG_NAME,
             --WEIGHT,PRICE,AREA_ORIGIN_ID,AREA_ORIGIN_NAME,SALE_TRAN_ID,VOURCHER_TYPE,LOAD_TIME,IF_BATCH_ID,IS_FORMAT)VALUES(
             --SUPER_COMP_ID,SUPMART_NAME,to_date(IN_DATE,'yyyy/mm/dd'),BATCH_ID,PROV_ID,'3707250040000','潍坊市皓远食品有限公司',GOODS_CODE,GOODS_NAME,
             --WEIGHT,PRICE,AREA_CODE,AREA_NAME,PROV_ID,'1',SYSDATE(),0,1);
           end if;
            if GOODS_TYPE=2 then
              --如果商品类型为2，代表是肉
               execute immediate 'INSERT INTO ctmpmart_wf.TB_SUPER_MAR_MEAT_IN_INFO
                (SUPERMARKET_ID,SUPERMARKET_NAME,IN_DATE,ORIGINAL_QUARANTINE_ID,TRAN_ID,SUPPLIER_ID,SUPPLIER_NAME,MEAT_CODE,MEAT_NAME,WEIGHT,PRICE,SALE_TRAN_ID,LOAD_TIME,IF_BATCH_ID,IS_FORMAT)
              VALUES  (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15)'
              using SUPER_COMP_ID,SUPMART_NAME,to_date(IN_DATE,'yyyy/mm/dd'),BATCH_ID,PROV_ID,'3707250040000','潍坊市皓远食品有限公司',GOODS_CODE,GOODS_NAME,WEIGHT,PRICE,PROV_ID,SYSDATE(),'0','1';
             -- INSERT INTO TB_SUPER_MAR_MEAT_IN_INFO
              --(SUPERMARKET_ID,SUPERMARKET_NAME,IN_DATE,ORIGINAL_QUARANTINE_ID,TRAN_ID,SUPPLIER_ID,SUPPLIER_NAME,
              --MEAT_CODE,MEAT_NAME,WEIGHT,PRICE,SALE_TRAN_ID,LOAD_TIME,IF_BATCH_ID,IS_FORMAT)VALUES(
              --SUPER_COMP_ID,SUPMART_NAME,to_date(IN_DATE,'yyyy/mm/dd'),BATCH_ID,PROV_ID,'3707250040000','潍坊市皓远食品有限公司',GOODS_CODE,GOODS_NAME,
              --WEIGHT,PRICE,PROV_ID,SYSDATE(),'0','1');
           end if;


          V_ROWS := V_ROWS + 1;
          --每5000条提交一次
          IF MOD(V_ROWS, 5000) = 0 THEN
            COMMIT;
          END IF;

        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            EXIT;
          WHEN OTHERS THEN
            NULL;
        END;
      END LOOP;
      COMMIT;
      --关闭
      UTL_FILE.FCLOSE(V_FILE_HANDLE);
    END;

在当前用户CTMPMART_WF下创建定时任务，调用存储过程，发现报错：ORA-06512 在"SYS.DBMS_IJOB",line 651

百度后，发现问题是没有权限的问题，为CTMPMART_WF赋予DBA权限，依旧报错。搜索问题后发现在SYS用户下可以正常使用。所有为SYS用户赋予CTMPMART_WF用户下表与存储过程的权限，在SYSDBA用户下创建定时作业直接调用CTMPMART_WF用户下的存储过程（在存储过程中需要在操作表之前添加用户名CTMPMART_WF），并写入数据。赋予权限如下所示：


create or replace directory BLOB_FILE_DIR as 'D:\test';

grant execute on utl_file to SYS;
Grant execute on update_customer_rank_byfile to sys;
Grant execute on getsertalcode to sys;
Grant execute on test_only to sys;

创建定时作业代码如下：

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/  
        WHAT => 'UPDATE_CUSTOMER_RANK_BYFILE;',  /*需要执行的存储过程名称或SQL语句*/  
        NEXT_DATE => sysdate+3/(24*60),  /*初次执行时间-下一个3分钟*/  
        INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /*每隔1分钟执行一次*/
      );  
  commit;
end;

具体的执行时间间隔可以百度查询。