论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2005-08-31 10:54 |只看该作者 |倒序浏览

简约代码如下
/* Oracle settings */
set pagesize 0
set feedback off
...
/*DECLARE variables*/
...
begin
LOOP

            UTL_FILE.GET_LINE(V_INPUT_HANDLE,V_LINE);
--get input file line by line
V_POS_1 := INSTR(V_LINE, ',', 1,1);
V_RGISNO := SUBSTR(V_LINE,0, V_POS_1-1);
--get first filed of line
SELECT ACCOUNT_NO INTO V_ARBOR_ACCT_NO FROM CUSTOMER_ID_ACCT_MAP@arbor1
WHERE external_id = V_RGISNO
AND EXTERNAL_ID_TYPE IN ( 2, 3, 5, 6, 7, 13, 16, 530, 531, 532, 533, 534, 535 )
AND INACTIVE_DATE IS NULL;

  IF  V_IGNORE != 1 THEN
         SELECT CUSTOMERID INTO V_TALLYMAN_CUS_NO FROM RL_CUSTOMERS
WHERE TMC_CMF_11 = V_ARBOR_ACCT_NO;
IF  V_IGNORE != 1 THEN
SELECT ID INTO V_TALLYMAN_ACCT_NO FROM ACCOUNTS
WHERE CUSTOMERID = V_TALLYMAN_CUS_NO
AND LEADER = 'Y';
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_TALLYMAN_ACCT_NO=' || V_TALLYMAN_ACCT_NO);
END IF;

/*UPDATE TABLE ADN INSERT TABLE */
...
...
EXCEPTION
WHEN NO_DATA_FOUND THEN

      V_IGNORE := 1;
      V_NOUPDATE_COUNTER := V_NOUPDATE_COUNTER + 1;
      UTL_FILE.PUT_LINE(V_ERROR_HANDLE, V_LINE);
WHEN OTHERS THEN
...
END;

出错信息
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.UTL_FILE", line 105
ORA-06512: at "SYS.UTL_FILE", line 255
ORA-06512: at line 78
代码中的三个sql语句都是能找到数据的。
我用的环境是unix ＋oracle 9i

小弟找了大半天实在是找不到，请高人指点，谢谢。

文库|博客

rollingpig

版主

论坛徽章:: 0

2楼 [报告]

发表于 2005-08-31 11:29 |只看该作者

关于NO_DATA_FOUND出现异常的询问

报错的是UTL_FILE。

贴出详细code

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

sky2004gy

白手起家

论坛徽章:: 0

3楼 [报告]

发表于 2005-08-31 11:33 |只看该作者

关于NO_DATA_FOUND出现异常的询问

完整代码：

/* Oracle settings */
set pagesize 0
set feedback off
set verify off
set serveroutput on
set head off
set linesize 30000
set newpage none
set timing off

DECLARE
/* technical architecture variables */
V_INPUT_HANDLE       UTL_FILE.FILE_TYPE;
      V_ACTLOG_HANDLE       UTL_FILE.FILE_TYPE;
V_CNTLOG_HANDLE       UTL_FILE.FILE_TYPE;
V_ERROR_HANDLE UTL_FILE.FILE_TYPE;
V_RETURN_HANDLE UTL_FILE.FILE_TYPE;
      V_ACTLOG_FILE          VARCHAR(80);
V_CNTLOG_FILE          VARCHAR(80);
V_ERROR_FILE VARCHAR(80);
V_RETURN_FILE VARCHAR(80);
      V_LINE                VARCHAR(500);
      V_INPUT_DIR          VARCHAR(100);
      V_LOG_DIR             VARCHAR(100);
      V_JOB_ID             VARCHAR(15);
      V_FILE_NAME          VARCHAR(100);
V_IGNORE number(1);
V_RETURN_CODE    VARCHAR2(4) := '0';
/* application specific variables */
TYPE field_num IS VARRAY (46) OF VARCHAR(100);
Field field_num:=field_num(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

CMPDTLS       COMPANYDETAILS%ROWTYPE;

comma_num NUMBER(5);
V_POS_1 NUMBER(5);
V_POS_2 NUMBER(5);
V_UPDATE_COUNTER       NUMBER(5)  ;
V_NOUPDATE_COUNTER    NUMBER(5)  ;
V_TALNUM_ACCT NUMBER(5)  ;
V_EXTRACT_DATE VARCHAR(20);
V_ARBOR_ACCT_NO NUMBER(10);
V_TALLYMAN_CUS_NO NUMBER(10);
V_TALLYMAN_ACCT_NO NUMBER(10);

SQL_CD                NUMBER;
      SQL_MSG                VARCHAR2(1000);

BEGIN
V_INPUT_DIR    := '&1';
      V_FILE_NAME    := '&2';
      V_LOG_DIR    := '&3';
      V_JOB_ID       := '&4';
      V_ACTLOG_FILE :=  V_JOB_ID || '_ACTLOG.log';
V_CNTLOG_FILE :=  V_JOB_ID || '_CNTLOG.log';
V_ERROR_FILE :=  V_JOB_ID || '_ERROR.dat';
V_RETURN_FILE :=  V_JOB_ID || '_RET.log';

      V_INPUT_HANDLE :=UTL_FILE.FOPEN(V_INPUT_DIR, V_FILE_NAME, 'R');
      V_ACTLOG_HANDLE :=UTL_FILE.FOPEN(V_LOG_DIR, V_ACTLOG_FILE , 'W');
      V_CNTLOG_HANDLE :=UTL_FILE.FOPEN(V_LOG_DIR, V_CNTLOG_FILE , 'W');
V_ERROR_HANDLE :=UTL_FILE.FOPEN(V_LOG_DIR, V_ERROR_FILE , 'W');
V_RETURN_HANDLE :=UTL_FILE.FOPEN(V_LOG_DIR, V_RETURN_FILE, 'W');
      UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'------Start SQL Process -----');
      UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'Processed File :' || V_FILE_NAME);
UTL_FILE.PUT_LINE(V_CNTLOG_HANDLE,'Processed File :' || V_FILE_NAME);

V_UPDATE_COUNTER       := 0;
V_NOUPDATE_COUNTER    := 0;
V_POS_1 := 0;
V_POS_2 := 0;
/* read header record */
UTL_FILE.GET_LINE(V_INPUT_HANDLE,V_LINE);
V_LINE  := V_LINE || ','; --convenience to get last field of the line
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'Current line :' || V_LINE);
V_POS_1 := INSTR(V_LINE, ',', 1,2);
V_POS_2 := INSTR(V_LINE, ',', 1,3);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_POS_1 :' || V_POS_1);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_POS_2 :' || V_POS_2);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_TALNUM_ACCT[' || SUBSTR(V_LINE,V_POS_1+1, V_POS_2 - V_POS_1 - 1) || ']');
V_TALNUM_ACCT :=TO_NUMBER( SUBSTR(V_LINE,V_POS_1+1, V_POS_2 - V_POS_1 - 1) );
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_TALNUM_ACCT=' || V_TALNUM_ACCT);

/*    process detail records  */
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'------Process Detail Records -----');
      LOOP

            UTL_FILE.GET_LINE(V_INPUT_HANDLE,V_LINE);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_LINE=' || V_LINE);
V_POS_1  := 0;
V_POS_2  := 0;
V_IGNORE := 0;
comma_num :=1;
V_LINE  := V_LINE || ','; --convenience to get last field of the line
--get first field of the detail line
V_POS_1 := INSTR(V_LINE, ',', 1,1);
V_EXTRACT_DATE := SUBSTR(V_LINE,0, V_POS_1-1);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_EXTRACT_DATE=' || V_EXTRACT_DATE);
LOOP
V_POS_1 := INSTR(V_LINE, ',', 1,comma_num);
V_POS_2 := INSTR(V_LINE, ',', 1,comma_num + 1);
Field(comma_num) :=SUBSTR(V_LINE,V_POS_1+1, V_POS_2 - V_POS_1 -1);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'Field(' || comma_num || ')=' || Field(comma_num));
--DBMS_OUTPUT.PUT_LINE( 'Field(' || comma_num || ')=' || Field(comma_num));
comma_num := comma_num + 1;
EXIT WHEN comma_num >;46;
END LOOP;

CMPDTLS.REGISTRATIONNO := field(1);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'CMPDTLS.REGISTRATIONNO=[' || CMPDTLS.REGISTRATIONNO || ']');

SELECT ACCOUNT_NO INTO V_ARBOR_ACCT_NO FROM CUSTOMER_ID_ACCT_MAP@arbor1
WHERE external_id= CMPDTLS.registrationno
AND EXTERNAL_ID_TYPE IN ( 2, 3, 5, 6, 7, 13, 16, 530, 531, 532, 533, 534, 535 )
AND INACTIVE_DATE IS NULL;
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_ARBOR_ACCT_NO=' || V_ARBOR_ACCT_NO);

         IF  V_IGNORE != 1 THEN
         SELECT CUSTOMERID INTO V_TALLYMAN_CUS_NO FROM RL_CUSTOMERS
WHERE TMC_CMF_11 = V_ARBOR_ACCT_NO
AND TMC_CMF_21 = V_ARBOR_ACCT_NO
AND TMC_CMF_31 = V_ARBOR_ACCT_NO
AND TMC_CMF_41 = V_ARBOR_ACCT_NO;

UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_TALLYMAN_CUS_NO=' || V_TALLYMAN_CUS_NO);
END IF;

IF  V_IGNORE != 1 THEN
SELECT ID INTO V_TALLYMAN_ACCT_NO FROM ACCOUNTS
WHERE CUSTOMERID = V_TALLYMAN_CUS_NO
AND LEADER = 'Y';
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'V_TALLYMAN_ACCT_NO=' || V_TALLYMAN_ACCT_NO);
END IF;

   CMPDTLS.ROC_EXTRACTDT := V_EXTRACT_DATE;
CMPDTLS.REGISTRATIONNO := field(1);
CMPDTLS.COMPANYENTITIES1 := TO_NUMBER(field(2));
CMPDTLS.ADDRESS1 := field(3);
CMPDTLS.ADDRESS2 := field(4);
CMPDTLS.ADDRESS3 := field(5);
CMPDTLS.ADDRESS4 := field(6);
CMPDTLS.BUSINESSTYPE := field(7);
CMPDTLS.STATUS := field(

;
CMPDTLS.STATUSEFFDT := field(9);
CMPDTLS.PAIDUPCAPITAL := TO_NUMBER(field(10));

CMPDTLS.PAIDCAPTIALCURRENY := field(11);
CMPDTLS.CHAIRMAN := field(12);
CMPDTLS.CHAIRMAN_ICNO := field(13);
CMPDTLS.CHAIRMAN_ADD1 := field(14);
CMPDTLS.CHAIRMAN_ADD2 := field(15);
CMPDTLS.CHAIRMAN_ADD3 := field(16);
CMPDTLS.CHAIRMAN_APPDT := field(17);
CMPDTLS.CHAIRMAN_POSITION := field(1

;
CMPDTLS.CHAIRMAN_WDRAWNDT := field(19);
CMPDTLS.DIRECTOR1 := field(20);

CMPDTLS.DIRECTOR1_ICNO := field(21);
CMPDTLS.DIRECTOR1_ADD1 := field(22);
CMPDTLS.DIRECTOR1_ADD2 := field(23);
CMPDTLS.DIRECTOR1_ADD3 := field(24);
CMPDTLS.DIRECTOR1_APPDT := field(25);
CMPDTLS.DIRECTOR1_POSITION := field(26);
CMPDTLS.DIRECTOR1_WDRAWNDT := field(27);
CMPDTLS.DIRECTOR2 := field(2

;
CMPDTLS.DIRECTOR2_ICNO := field(29);
CMPDTLS.DIRECTOR2_ADD1 := field(30);
CMPDTLS.DIRECTOR2_ADD2 := field(31);
CMPDTLS.DIRECTOR2_ADD3 := field(32);
CMPDTLS.DIRECTOR2_APPDT := field(33);
CMPDTLS.DIRECTOR2_POSITION := field(34);
CMPDTLS.DIRECTOR2_WDRAWNDT := field(35);
CMPDTLS.SECRETARY := field(36);
CMPDTLS.SECRETARY_ICNO := field(37);
CMPDTLS.SECRETARY_ADD1 := field(3

;
CMPDTLS.SECRETARY_ADD2 := field(39);
CMPDTLS.SECRETARY_ADD3 := field(40);
CMPDTLS.SECRETARY_APPDT := field(41);
CMPDTLS.SECRETARY_POSITION := field(42);
CMPDTLS.SECRETARY_WDRAWNDT := field(43);
CMPDTLS.LASTAGMDATE := field(44);
CMPDTLS.LASTREVIEWDATE := field(45);
CMPDTLS.LASTAGMACCTLAIDDT := field(46);

BEGIN
   UPDATE COMPANYDETAILS SET
   ROC_EXTRACTDT = CMPDTLS.ROC_EXTRACTDT,
   CUSTOMERS1 = V_TALLYMAN_CUS_NO ,
   COMPANYENTITIES1 = CMPDTLS.COMPANYENTITIES1,
   ADDRESS1 = CMPDTLS.ADDRESS1,
   ADDRESS2 = CMPDTLS.ADDRESS2,
   ADDRESS3 = CMPDTLS.ADDRESS3,
   ADDRESS4 = CMPDTLS.ADDRESS4,
   BUSINESSTYPE = CMPDTLS.BUSINESSTYPE,
   STATUS= CMPDTLS.STATUS,
   STATUSEFFDT = CMPDTLS.STATUSEFFDT,
   PAIDUPCAPITAL = CMPDTLS.PAIDUPCAPITAL,
   PAIDCAPTIALCURRENY = CMPDTLS.PAIDCAPTIALCURRENY,
   CHAIRMAN = CMPDTLS.CHAIRMAN,
   CHAIRMAN_ICNO = CMPDTLS.CHAIRMAN_ICNO,
   CHAIRMAN_ADD1 = CMPDTLS.CHAIRMAN_ADD1,
   CHAIRMAN_ADD2 = CMPDTLS.CHAIRMAN_ADD2,
   CHAIRMAN_ADD3 = CMPDTLS.CHAIRMAN_ADD3,
   CHAIRMAN_APPDT = CMPDTLS.CHAIRMAN_APPDT,
   CHAIRMAN_POSITION = CMPDTLS.CHAIRMAN_POSITION,
   CHAIRMAN_WDRAWNDT = CMPDTLS.CHAIRMAN_WDRAWNDT,
   DIRECTOR1 = CMPDTLS.DIRECTOR1,
   DIRECTOR1_ICNO = CMPDTLS.DIRECTOR1_ICNO,
   DIRECTOR1_ADD1 = CMPDTLS.DIRECTOR1_ADD1,
   DIRECTOR1_ADD2 = CMPDTLS.DIRECTOR1_ADD2,
   DIRECTOR1_ADD3 = CMPDTLS.DIRECTOR1_ADD3,
   DIRECTOR1_APPDT = CMPDTLS.DIRECTOR1_APPDT,
   DIRECTOR1_POSITION = CMPDTLS.DIRECTOR1_POSITION,
   DIRECTOR1_WDRAWNDT = CMPDTLS.DIRECTOR1_WDRAWNDT,
   DIRECTOR2 = CMPDTLS.DIRECTOR2,
   DIRECTOR2_ICNO = CMPDTLS.DIRECTOR2_ICNO,
   DIRECTOR2_ADD1 = CMPDTLS.DIRECTOR2_ADD1,
   DIRECTOR2_ADD2 = CMPDTLS.DIRECTOR2_ADD2,
   DIRECTOR2_ADD3 = CMPDTLS.DIRECTOR2_ADD3,
   DIRECTOR2_APPDT = CMPDTLS.DIRECTOR2_APPDT,
   DIRECTOR2_POSITION = CMPDTLS.DIRECTOR2_POSITION,
   DIRECTOR2_WDRAWNDT = CMPDTLS.DIRECTOR2_WDRAWNDT,
   SECRETARY = CMPDTLS.SECRETARY,
   SECRETARY_ICNO = CMPDTLS.SECRETARY_ICNO,
   SECRETARY_ADD1 = CMPDTLS.SECRETARY_ADD1,
   SECRETARY_ADD2 = CMPDTLS.SECRETARY_ADD2,
   SECRETARY_ADD3 = CMPDTLS.SECRETARY_ADD3,
   SECRETARY_APPDT = CMPDTLS.SECRETARY_APPDT,
   SECRETARY_POSITION = CMPDTLS.SECRETARY_POSITION,
   SECRETARY_WDRAWNDT = CMPDTLS.SECRETARY_WDRAWNDT,
   LASTAGMDATE = CMPDTLS.LASTAGMDATE,
   LASTREVIEWDATE = CMPDTLS.LASTREVIEWDATE,
   LASTAGMACCTLAIDDT = CMPDTLS.LASTAGMACCTLAIDDT
   WHERE REGISTRATIONNO = CMPDTLS.REGISTRATIONNO ;
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE, SQL%ROWCOUNT || ' Update Failed from COMPANYDETAILS for REGISTRATION NO: '|| CMPDTLS.REGISTRATIONNO);

UTL_FILE.PUT_LINE(V_RETURN_HANDLE, '-4');
END;

      V_UPDATE_COUNTER := V_UPDATE_COUNTER + 1;

BEGIN
   INSERT INTO ACCOUNTACTIVITIES
   (ID, ACCOUNTID, DATETIME, TRANSACTIONDATE, CUSTOMERID, INCOMING, OUTGOING,
   PARENTID, CHILDID, USERID, SUMMARY, APPLIED, COST, CHARGE, DEBIT, SPECIAL)
   VALUES
   (accountactivitiesseq.NEXTVAL,V_TALLYMAN_ACCT_NO,sysdate,sysdate,
   V_TALLYMAN_CUS_NO,'N','N',19,20,0,'Updated Customer Registration of
   Company Details','N',0,0,0,'N');

EXCEPTION
WHEN OTHERS THEN
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE, SQL%ROWCOUNT || ' Insert Failed from ACCOUNTACTIVITIES for ACCOUNTID: '||V_TALLYMAN_ACCT_NO);
V_RETURN_CODE :='-4';
UTL_FILE.PUT_LINE(V_RETURN_HANDLE, V_RETURN_CODE);
END;

      END LOOP;
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'end_loop');

IF ( (V_TALNUM_ACCT <>; ( V_UPDATE_COUNTER + V_NOUPDATE_COUNTER )) OR V_TALNUM_ACCT = 0 ) THEN
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE, 'Total number of records with update performed plus
Total number of records with no update performed not equal
Total Requested records' );
V_RETURN_CODE := '-4';
UTL_FILE.PUT_LINE(V_RETURN_HANDLE, V_RETURN_CODE);

ROLLBACK;

ELSE
UTL_FILE.PUT_LINE(V_CNTLOG_HANDLE,'');
UTL_FILE.PUT_LINE(V_CNTLOG_HANDLE,'Total Requested records:' || V_TALNUM_ACCT);
UTL_FILE.PUT_LINE(V_CNTLOG_HANDLE,'Total number of records with update performed' || V_UPDATE_COUNTER);
UTL_FILE.PUT_LINE(V_CNTLOG_HANDLE,'Total number of records with no update performed' || V_NOUPDATE_COUNTER);
END IF;
COMMIT;
      UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'------Process End------ ');
      UTL_FILE.FCLOSE(V_INPUT_HANDLE);
      UTL_FILE.FCLOSE(V_ACTLOG_HANDLE);
UTL_FILE.FCLOSE(V_CNTLOG_HANDLE);
UTL_FILE.FCLOSE(V_ERROR_HANDLE);
UTL_FILE.FCLOSE(V_RETURN_HANDLE);
/* -- Exception Handle
EXCEPTION

WHEN UTL_FILE.INVALID_PATH OR
UTL_FILE.INVALID_MODE OR
UTL_FILE.INVALID_FILEHANDLE OR
UTL_FILE.INVALID_OPERATION OR
UTL_FILE.INTERNAL_ERROR OR
UTL_FILE.WRITE_ERROR THEN

UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE, 'Program Error! File Handler Error!');
UTL_FILE.PUT_LINE(V_CNTLOG_HANDLE, 'Program Error! File Handler Error!');
UTL_FILE.PUT_LINE(V_RETURN_HANDLE, '-4');

UTL_FILE.FCLOSE(V_INPUT_HANDLE);
      UTL_FILE.FCLOSE(V_ACTLOG_HANDLE);
UTL_FILE.FCLOSE(V_CNTLOG_HANDLE);
UTL_FILE.FCLOSE(V_ERROR_HANDLE);
UTL_FILE.FCLOSE(V_RETURN_HANDLE);

WHEN NO_DATA_FOUND THEN

      V_IGNORE := 1;
      V_NOUPDATE_COUNTER := V_NOUPDATE_COUNTER + 1;
      UTL_FILE.PUT_LINE(V_ERROR_HANDLE, V_LINE);

WHEN OTHERS THEN

      SQL_CD := SQLCODE;
      SQL_MSG := SUBSTR(SQLERRM,1,500);

      UTL_FILE.PUT_LINE (V_ACTLOG_HANDLE, 'SQLCODE:'|| SQL_CD ||', SQLERRM:'|| SQL_MSG);
UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE, 'Program Error! Oracle exception raised !');
UTL_FILE.PUT_LINE(V_CNTLOG_HANDLE, 'Program Error! Oracle exception raised !');
UTL_FILE.PUT_LINE(V_RETURN_HANDLE, '-4');

      UTL_FILE.FCLOSE(V_INPUT_HANDLE);
      UTL_FILE.FCLOSE(V_ACTLOG_HANDLE);
UTL_FILE.FCLOSE(V_CNTLOG_HANDLE);
UTL_FILE.FCLOSE(V_ERROR_HANDLE);
UTL_FILE.FCLOSE(V_RETURN_HANDLE);
*/
END;
/

谢谢

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

sky2004gy

白手起家

论坛徽章:: 0

4楼 [报告]

发表于 2005-08-31 13:26 |只看该作者

关于NO_DATA_FOUND出现异常的询问

如果我把第一个loop ... end loop 拿掉的话（就是只取detail line 的第一行）
程序就正常了，不知道为何如此，是不是循环嵌套所造成的问题呢，我在分解detail line 的时候会用个循环去取字段；
附detail line format

26-Aug-05,52898709K,2,74 SUNGEI KADUT LOOP,,,729513,RENOVATION CONTRACTORS,TERMINATED,1-Aug-04,4,,TAN CHIANG BENG,S1571926E,535 SERANGOON NORTH AVENUE 4,#10-185,550535,31-Jul-99,OWNER,,TAN CHIANG BENG,S1571926E,535 SERANGOON NORTH AVENUE 4,#10-185,550535,26-Nov-99,MANAGER,,THIA YOK ENG,S2164719E,535 SERANGOON NORTH AVENUE 4,#10-185,550535,29-Nov-99,OWNER,,,,,,,,,,,,

谢谢

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

sky2004gy

白手起家

论坛徽章:: 0

5楼 [报告]

发表于 2005-08-31 13:30 |只看该作者

关于NO_DATA_FOUND出现异常的询问

精灵使你在那里，等你啊。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

sky2004gy

白手起家

论坛徽章:: 0

6楼 [报告]

发表于 2005-08-31 13:37 |只看该作者

关于NO_DATA_FOUND出现异常的询问

好像是在循环取detail line时出的错
UTL_FILE.GET_LINE(V_INPUT_HANDLE,V_LINE);

到底是那里冒犯了UTL_FILE这个包呢。

谁给我答案要交活啊。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

sky2004gy

白手起家

论坛徽章:: 0

7楼 [报告]

发表于 2005-08-31 13:48 |只看该作者

关于NO_DATA_FOUND出现异常的询问

刚刚看到资料说，当指定文件读到最后一行的时候 NO_DATA_FOUND将会引发，那如何避免呢。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

sky2004gy

白手起家

论坛徽章:: 0

8楼 [报告]

发表于 2005-09-02 17:22 |只看该作者

关于NO_DATA_FOUND出现异常的询问

问题已解决
当读到input file 的最后一行时会触发NO_DATA_FOUND异常
这时要做一个异常处理，用GOTO 语句跳出循环。
LOOP
BEGIN
            UTL_FILE.GET_LINE(V_INPUT_HANDLE,V_LINE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
            UTL_FILE.PUT_LINE(V_ACTLOG_HANDLE,'the end of file ');
            UTL_FILE.FCLOSE(V_INPUT_HANDLE);
GOTO END_OF_LOOP;
END;

...
...
END LOOP
<<END_OF_LOOP>;>;
...

谢谢

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

返回列表

Chinaunix › 论坛 › 数据库技术 › Oracle › 关于NO_DATA_FOUND出现异常的询问

关于NO_DATA_FOUND出现异常的询问 [复制链接]

关于NO_DATA_FOUND出现异常的询问

关于NO_DATA_FOUND出现异常的询问

关于NO_DATA_FOUND出现异常的询问

关于NO_DATA_FOUND出现异常的询问

关于NO_DATA_FOUND出现异常的询问

关于NO_DATA_FOUND出现异常的询问

关于NO_DATA_FOUND出现异常的询问