Oracle regularly grabs Excel (csv format) data and writes it to database table

Oracle access to files requires UTL file, which is an Oracle built-in package. Permissions need to be granted:

Create directory TEMP as' d: / test '; -- create directory and authorize. TEMP is the given path name
 Grant read, write on directory temp to ctmpmart? WF; -- grant permission to read and write directory
 Grant execute on sys.utl'file to ctmpmart'wf; -- grant user utl'file permission

The created stored procedure is as follows:

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; --Number of processing records
      LOOP
        BEGIN
          --Read file information to COL1 in,Read one line at a time
          UTL_FILE.GET_LINE(V_FILE_HANDLE, COL1);

         IF substr(COL1,0,4)='Supermarket daily'or substr(COL1,0,4)='Supermarket name' 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 First field in file (supermarket name)
           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 Second field in file (entry time)
           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 The third field in the document (Supplier)
           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 The fourth field in the file (commodity code)
           WEIGHT := substr(COL1,INSTR(COL1,GOODS_NAME)+LENGTH(GOODS_NAME)+1,INSTR(COL1,',',1,5)-INSTR(COL1,GOODS_NAME)-LENGTH(GOODS_NAME)-1); --csv Fifth field in file (weight)
           PRICE := substr(COL1,INSTR(COL1,',',1,5)+1,INSTR(COL1,',',1,6)-INSTR(COL1,',',1,5)-1); --csv Sixth field in the document (unit price)
           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);
           --Generate new traceability code and batch number
           SELECT ID INTO SERIAL_ID FROM ctmpmart_wf.SERIAL_CODE WHERE NAME = SUPER_COMP_NAME;
           GETSERTALCODE(SERIAL_ID,BATCH_ID); --Execute stored procedure and generate batch number
           SELECT ID INTO SERIAL_ID FROM ctmpmart_wf.SERIAL_CODE WHERE NAME = 'Trace code:'||SUPER_COMP_NAME||':'||LEGAL_PEPRESENT;

           GETSERTALCODE(SERIAL_ID,PROV_ID);
           if GOODS_TYPE=1 then
              --If the item type is 1, it means the dish
              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','Weifang Haoyuan Food Co., Ltd',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','Weifang Haoyuan Food Co., Ltd',GOODS_CODE,GOODS_NAME,
             --WEIGHT,PRICE,AREA_CODE,AREA_NAME,PROV_ID,'1',SYSDATE(),0,1);
           end if;
            if GOODS_TYPE=2 then
              --If the commodity type is 2, it means meat
               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','Weifang Haoyuan Food Co., Ltd',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','Weifang Haoyuan Food Co., Ltd',GOODS_CODE,GOODS_NAME,
              --WEIGHT,PRICE,PROV_ID,SYSDATE(),'0','1');
           end if;


          V_ROWS := V_ROWS + 1;
          --Submit every 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;
      --Close
      UTL_FILE.FCLOSE(V_FILE_HANDLE);
    END;

Create a scheduled task under the current user ctmpmart? WF, call the stored procedure, and find the error: ORA-06512 in "sys. DBMS? Ijob", line 651

After Baidu, it was found that the problem was that there was no permission. It gave CTA permission to ctmpmart? WF and still reported an error. After searching for the problem, we found that it can be used normally under the sys user. All the SYS users are given the following table and stored procedure permissions of the CTMPMART WF user. Creating a timed job under the SYS DBA user directly calls the stored procedure under the CTMPMART WF user (in the stored procedure, the user name CTMPMART WF needs to be added before the operation table), and writes the data. Grant permissions as follows:

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;

Create a scheduled job code as follows:

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*Automatically generate job? ID*/  
        WHAT => 'UPDATE_CUSTOMER_RANK_BYFILE;',  /*Stored procedure name or SQL statement to execute*/  
        NEXT_DATE => sysdate+3/(24*60),  /*Initial execution time - next 3 minutes*/  
        INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /*Every 1 minute*/
      );  
  commit;
end;

Specific execution time interval can be queried by Baidu.

Keywords: Stored Procedure Oracle SQL

Added by Static Cling on Tue, 31 Dec 2019 15:31:40 +0200