文件数据导入

CREATE OR REPLACE PACKAGE CUX_AMW_FILE_INTO IS

  /*==========================================================================+
  |             Copyright (c) 2009 Ares Huang                                 |
  |                   All rights reserved.                                    |
  +===========================================================================+
  |-- Author  : MIS HuangYunqiang                                             |
  |-- Created : 2009-05-14 10:08:22                                           |
  *****************************************************************************
  ****************************************************************************/
  PROCEDURE ME_OPT(P_FILE_ID IN NUMBER
                  ,P_ORG_ID  IN NUMBER);

  PROCEDURE NMCC_GZ(P_FILE_ID IN NUMBER
                   ,P_ORG_ID  IN NUMBER);

END CUX_AMW_FILE_INTO;
/
CREATE OR REPLACE PACKAGE BODY CUX_AMW_FILE_INTO IS

  /*==========================================================================+
  |             Copyright (c) 2009 Ares Huang                                 |
  |                   All rights reserved.                                    |
  +===========================================================================+
  |-- Author  : MIS HuangYunqiang                                             |
  |-- Created : 2009-05-14 10:08:22                                           |
  *****************************************************************************
  ****************************************************************************/
  PROCEDURE ME_OPT(P_FILE_ID IN NUMBER
                  ,P_ORG_ID  IN NUMBER) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    -- l_file_id NUMBER;
    ----------------------------
    --w_integer INTEGER;
    W_BLOB    BLOB;
    W_RAW     RAW(10);
    W_BUFF    VARCHAR2(30000);
    W_LINE    VARCHAR2(30000);
    W_LEN     INTEGER;
    EOFSW     BOOLEAN := FALSE;
    OFFSET    INTEGER;
    W_B_LEN   NUMBER := 0;
    W_NUM     NUMBER;
    W_SEQ     NUMBER := 0;
    V_LOOP    NUMBER := 0;
    V_TYPE    VARCHAR2(50);
    V_PD_NAME VARCHAR2(50);
    V_PD_SUB  VARCHAR2(50);
    V_RE      NUMBER;
    V_TMP     NUMBER;
    ----------------------------
    I       NUMBER;
    LINEBUF VARCHAR2(30000);
    TYPE COMMA_POS_TYPE IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
    T_COMMA_POS COMMA_POS_TYPE;
    V_NO        VARCHAR2(22);
  BEGIN
    SELECT TO_CHAR(SYSDATE, 'YYYYMM') || LPAD(TO_CHAR(NVL(COUNT(DISTINCT IN_NO), 0) + 1), 4, '0')
      INTO V_NO
      FROM CUX.CUX_AMW_ME_OPT T
     WHERE T.CREATION_DATE >= TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM') || '-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
       AND T.CREATION_DATE < TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYY-MM') || '-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
    SELECT FILE_DATA INTO W_BLOB FROM FND_LOBS WHERE FILE_ID = P_FILE_ID;
    OFFSET := 1;
    V_LOOP := 0;
    LOOP
      EXIT WHEN EOFSW;
      W_SEQ   := W_SEQ + 1;
      W_RAW   := UTL_RAW.CAST_TO_RAW(CHR(10));
      W_NUM   := DBMS_LOB.INSTR(W_BLOB, W_RAW, OFFSET, 1);
      W_LEN   := W_NUM - W_B_LEN;
      W_B_LEN := W_NUM;
      IF W_NUM = 0 THEN
        W_LEN := 20000;
        EOFSW := TRUE;
      END IF;
      BEGIN
        DBMS_LOB.READ(W_BLOB, W_LEN, OFFSET, W_BUFF);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        WHEN OTHERS THEN
          RAISE;
      END;
      W_LINE := UTL_RAW.CAST_TO_VARCHAR2(W_BUFF);
      SELECT REPLACE(W_LINE, CHR(10), NULL) INTO W_LINE FROM DUAL;
      SELECT REPLACE(W_LINE, CHR(13), NULL) INTO W_LINE FROM DUAL;
      ------------------   
      LINEBUF := W_LINE;
      I       := 1;
      WHILE INSTR(LINEBUF, ',', 1, I) > 0 LOOP
        T_COMMA_POS(I) := INSTR(LINEBUF, ',', 1, I);
        I := I + 1;
      END LOOP;
      IF V_LOOP > 0 THEN
        BEGIN
          INSERT INTO CUX.CUX_AMW_ME_OPT
            (FILE_ID
            ,ORG_ID
            ,M_NO
            ,E_NO
            ,JOB_NO
            ,E_QTY
            ,M_QTY
            ,M_ITEM_NAME
            ,PXH
            --,E_ITEM_NAME
            ,LAST_UPDATE_DATE
            ,LAST_UPDATED_BY
            ,CREATION_DATE
            ,CREATED_BY
            ,IN_NO)
          VALUES
            (P_FILE_ID
            ,P_ORG_ID
            ,TRIM(SUBSTR(LINEBUF, 1, T_COMMA_POS(1) - 1)) --M NO
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(1) + 1, T_COMMA_POS(2) - T_COMMA_POS(1) - 1)) --E NO
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(2) + 1, T_COMMA_POS(3) - T_COMMA_POS(2) - 1)) --JOB NO
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(3) + 1, T_COMMA_POS(4) - T_COMMA_POS(3) - 1)) --E QTY
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(4) + 1, T_COMMA_POS(5) - T_COMMA_POS(4) - 1)) --M ITEM
            ,0 --M QTY
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(5) + 1)) -- PXH
            --,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(5) + 1)) -- E ITEM
            ,SYSDATE
            ,APPS.FND_GLOBAL.USER_ID
            ,SYSDATE
            ,APPS.FND_GLOBAL.USER_ID
            ,V_NO);
        EXCEPTION
          WHEN OTHERS THEN
            FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
            FND_MESSAGE.SET_TOKEN('ERRNO', -20000);
            FND_MESSAGE.SET_TOKEN('REASON', '导入失败,请确认数据 ' || LINEBUF || SUBSTR(LINEBUF, 1, T_COMMA_POS(1) - 1));
            FND_MESSAGE.SET_TOKEN('ROUTINE', 'CUX_AMW_FILE_INTO');
            ROLLBACK;
            APP_EXCEPTION.RAISE_EXCEPTION;
        END;
      END IF;
      OFFSET := OFFSET + W_LEN;
      V_LOOP := V_LOOP + 1;
    END LOOP;
    COMMIT;
  END;

  PROCEDURE NMCC_GZ(P_FILE_ID IN NUMBER
                   ,P_ORG_ID  IN NUMBER) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    -- l_file_id NUMBER;
    ----------------------------
    --w_integer INTEGER;
    W_BLOB    BLOB;
    W_RAW     RAW(10);
    W_BUFF    VARCHAR2(30000);
    W_LINE    VARCHAR2(30000);
    W_LEN     INTEGER;
    EOFSW     BOOLEAN := FALSE;
    OFFSET    INTEGER;
    W_B_LEN   NUMBER := 0;
    W_NUM     NUMBER;
    W_SEQ     NUMBER := 0;
    V_LOOP    NUMBER := 0;
    V_TYPE    VARCHAR2(50);
    V_PD_NAME VARCHAR2(50);
    V_PD_SUB  VARCHAR2(50);
    V_RE      NUMBER;
    V_TMP     NUMBER;
    ----------------------------
    I       NUMBER;
    LINEBUF VARCHAR2(30000);
    TYPE COMMA_POS_TYPE IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
    T_COMMA_POS COMMA_POS_TYPE;
    V_NO        VARCHAR2(22);
  BEGIN
    DELETE MIS_WEIGHT.CUX_AMW_PD_INPUT WHERE PD_ID=-518 AND SID=-518;
    COMMIT;
    SELECT FILE_DATA INTO W_BLOB FROM FND_LOBS WHERE FILE_ID = P_FILE_ID;
    OFFSET := 1;
    V_LOOP := 0;
    LOOP
      EXIT WHEN EOFSW;
      W_SEQ   := W_SEQ + 1;
      W_RAW   := UTL_RAW.CAST_TO_RAW(CHR(10));
      W_NUM   := DBMS_LOB.INSTR(W_BLOB, W_RAW, OFFSET, 1);
      W_LEN   := W_NUM - W_B_LEN;
      W_B_LEN := W_NUM;
      IF W_NUM = 0 THEN
        W_LEN := 20000;
        EOFSW := TRUE;
      END IF;
      BEGIN
        DBMS_LOB.READ(W_BLOB, W_LEN, OFFSET, W_BUFF);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        WHEN OTHERS THEN
          RAISE;
      END;
      W_LINE := UTL_RAW.CAST_TO_VARCHAR2(W_BUFF);
      SELECT REPLACE(W_LINE, CHR(10), NULL) INTO W_LINE FROM DUAL;
      SELECT REPLACE(W_LINE, CHR(13), NULL) INTO W_LINE FROM DUAL;
      ------------------   
      LINEBUF := W_LINE;
      I       := 1;
      WHILE INSTR(LINEBUF, ',', 1, I) > 0 LOOP
        T_COMMA_POS(I) := INSTR(LINEBUF, ',', 1, I);
        I := I + 1;
      END LOOP;
      IF V_LOOP > 0 THEN
        BEGIN
          INSERT INTO MIS_WEIGHT.CUX_AMW_PD_INPUT
            (FILE_NAME
            ,PD_ID
            ,SID
            ,ITEM_NAME
            ,QTY_SYSTEM
            ,LAST_UPDATE_DATE
            ,LAST_UPDATED_BY
            ,CREATION_DATE
            ,CREATED_BY)
          VALUES
            (P_FILE_ID
            ,-518
            ,-518
            ,TRIM(SUBSTR(LINEBUF, 1, T_COMMA_POS(1) - 1)) --ITEM_NAME
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(1) + 1)) -- E ITEM
            ,SYSDATE
            ,APPS.FND_GLOBAL.USER_ID
            ,SYSDATE
            ,APPS.FND_GLOBAL.USER_ID);
        EXCEPTION
          WHEN OTHERS THEN
            FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
            FND_MESSAGE.SET_TOKEN('ERRNO', -20000);
            FND_MESSAGE.SET_TOKEN('REASON', '导入失败,请确认数据 ' || LINEBUF || SUBSTR(LINEBUF, 1, T_COMMA_POS(1) - 1));
            FND_MESSAGE.SET_TOKEN('ROUTINE', 'CUX_AMW_FILE_INTO');
            ROLLBACK;
            APP_EXCEPTION.RAISE_EXCEPTION;
        END;
      END IF;
      OFFSET := OFFSET + W_LEN;
      V_LOOP := V_LOOP + 1;
    END LOOP;
    COMMIT;
  END;

END CUX_AMW_FILE_INTO;
/

阅读更多
想对作者说点什么? 我来说一句

exl文件导入数据库中

2014年10月31日 23.31MB 下载

数据导入数据导入数据导入

2008年10月28日 100KB 下载

csv 文件导入导出

2011年12月22日 2KB 下载

把.xls文件数据导入SQL Server

2012年12月12日 59KB 下载

db2导入文本数据的方法

2010年12月22日 280KB 下载

没有更多推荐了,返回首页

不良信息举报

文件数据导入

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭