Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: ORA-01403: no data found

    Hi,
    I created this anonymous block to write in the file:

    DECLARE
    NUM_RECORD_WRITE NUMBER:= 0;
    NUM_RECORD_NO_WRITE NUMBER:= 0;
    v_file UTL_FILE.FILE_TYPE;
    v_file2 UTL_FILE.FILE_TYPE;
    v_dir VARCHAR2(200);
    FILE_NAME varchar2(80) := 'TEST';
    FILE_NAME2 varchar2(80) := 'TEST2';
    V_OUT varchar2(3000);
    V_OUT1 varchar2(3000);
    V_OUT2 varchar2(3000);
    V_OUT3 varchar2(3000);
    V_OUT4 varchar2(3000);
    V_OUT5 varchar2(3000);
    err_num number;
    err_msg varchar2(500);
    CURSOR c_data IS
    select dv_id
    from dp;
    BEGIN
    v_dir := 'E:\risk_analysis';
    v_file := utl_file.fopen(v_dir,FILE_NAME||'.txt','w',3000);
    v_file2 := utl_file.fopen(v_dir, FILE_NAME2||'.txt', 'w', 3000);
    select dv_id
    INTO V_OUT1
    from dv
    where dv_id='AIM'
    and rownum=1;

    select dv_id
    INTO V_OUT2
    from dv
    where dv_id='AEC'
    and rownum=1;

    select dv_id
    INTO V_OUT3
    from dv
    where dv_id='BBC'
    and rownum=1;

    FOR cur_rec IN c_data
    LOOP
    V_OUT := cur_rec.DV_ID;
    IF V_OUT NOT IN(V_OUT1, V_OUT2,V_OUT3) THEN
    UTL_FILE.PUT_LINE(v_file,V_OUT);
    ELSE
    UTL_FILE.PUT_LINE(v_file2,V_OUT);
    NUM_RECORD_NO_WRITE := NUM_RECORD_NO_WRITE +1;
    UTL_FILE.PUT_LINE(v_file2,NUM_RECORD_NO_WRITE);
    END IF;
    NUM_RECORD_WRITE := NUM_RECORD_WRITE + 1;
    UTL_FILE.PUT_LINE(v_file,NUM_RECORD_WRITE);
    END LOOP;

    UTL_FILE.FCLOSE(V_FILE);
    UTL_FILE.FCLOSE(V_FILE2);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    err_msg:= SUBSTR(SQLERRM, 1, 500);
    err_num:= SQLCODE;
    DBMS_OUTPUT.put_line('Error:'||err_num||'Mess: '||err_msg);
    WHEN utl_file.invalid_path THEN
    dbms_output.put_line('Invalid Path <'||v_dir||'>');
    WHEN utl_file.invalid_mode THEN
    dbms_output.put_line('Invalid Mode ');
    WHEN utl_file.invalid_operation THEN
    dbms_output.put_line('Invalid Operation');
    WHEN utl_file.INVALID_MAXLINESIZE THEN
    dbms_output.put_line('INVALID MAXLINESIZE');
    END;

    Now my problem is this:

    I'd like to exclude any DV_ID that return from 3 queries and write it in the file TEST2.txt

    This block is correct if the 3 queries return one record, but in my case the query:
    select dv_id
    INTO V_OUT3
    from dv
    where dv_id='BBC'
    and rownum=1;
    RETURN NO RECORD and I get this error:

    ORA-01403: no data found

    and no write in the file.

    How Can I manage and avoid this error to get in v_file2 also null values?

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Encapsulate every SELECT into its own BEGIN-EXCEPTION-END PL/SQL block and, in every EXCEPTION section, write appropriate exception handler (in your example, it would be teh WHEN NO-DATA-FOUND THEN ...).

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    SELECT INTO is a fine thing, but if it's possible for it to find no rows and you want to handle that condition and continue, then you need to wrap the SELECT INTO statement in its own BEGIN...EXCEPTION...END block.

    btw I didn't really see what this achieved:

    WHEN NO_DATA_FOUND THEN
    err_msg:= SUBSTR(SQLERRM, 1, 500);
    err_num:= SQLCODE;
    DBMS_OUTPUT.put_line('Error:'||err_num||'Mess: '||err_msg);

    Why 500? Why SQLCODE? SQLERRM already contains the error code. This message is just a scrambled version of the one already provided, and replacing an exception with a debug message means the block always appears to complete successfully.

  4. #4
    Join Date
    Jul 2002
    Posts
    227
    select dv_id
    from dp;

    DV_ID
    -------
    ACEGA
    CCC
    AGESCI
    AAA
    BBB
    AGILENT
    AGRISIAN
    ALATEL
    ALBACOM
    ALLAXIA
    ALTRI
    AMC
    AMMINISTRAZIONI PUBBLICHE
    APLAT
    ASM


    select dv_id
    INTO V_OUT1
    from dv
    where dv_id='AIM'
    and rownum=1;
    DV_ID
    -------
    AIM


    select dv_id
    INTO V_OUT2
    from dv
    where dv_id='AEC'
    and rownum=1;
    DV_ID
    ------
    AEC

    select dv_id
    INTO V_OUT3
    from dv
    where dv_id='BBC'
    and rownum=1;

    no rows selected

    in TEST I'd like to get:
    ACEGA
    1
    CCC
    2
    AGESCI
    3
    AAA
    4
    BBB
    5
    AGILENT
    6
    AGRISIAN
    7
    ALATEL
    8
    ALBACOM
    9
    ALLAXIA
    10
    ALTRI
    11
    AMC
    12
    AMMINISTRAZIONI PUBBLICHE
    13
    APLAT
    14
    ASM
    15

    in TEST2 I'd like to get:
    AIM
    1
    AEC
    2

    Have you any idea?
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •