DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: NO_DATA_FOUND exception

  1. #1
    Join Date
    Apr 2002
    Posts
    1
    I have a stored procedure which contains the following:

    SELECT toc.description
    INTO strComments2
    FROM toctask toc, tofcontract tc, toccontractstub tcs
    WHERE tc.c_occtrstb_contractstub = tcs.c
    AND tc.i_occtrstb_contractstub = tcs.i
    AND tcs.referencenumb IN (toc.subjectrefere, '100'||substr(toc.subjectrefere, 4, 11))
    AND tc.c = ROW.c_ofcontra_statuschanges
    AND tc.i = ROW.i_ofcontra_statuschanges
    AND toc.c_octaskty_tasks = TASK_TYPE_CLASS
    AND toc.i_octaskty_tasks = tasktype_id
    AND toc.creationdate = ( SELECT max (toc2.creationdate)
    FROM toctask toc2
    WHERE tcs.referencenumb in (toc2.subjectrefere, '100'||substr(toc2.subjectrefere, 4, 11))
    AND toc2.c_octaskty_tasks = TASK_TYPE_CLASS
    AND toc2.i_octaskty_tasks = tasktype_id );

    In some circumstances there is no data and so a NO_DATA_FOUND exception occurs, to resolve this I want to put:

    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN strComments2 := ' ';

    Please can someone let me know if this will work and pass back a blank string for the strComments2 field.

    thank you

  2. #2
    Join Date
    Apr 2002
    Posts
    4
    BEGIN
    SELECT toc.description
    INTO strComments2
    FROM toctask toc, tofcontract tc, toccontractstub tcs
    WHERE tc.c_occtrstb_contractstub = tcs.c
    AND tc.i_occtrstb_contractstub = tcs.i
    AND tcs.referencenumb IN (toc.subjectrefere, '100'||substr (toc.subjectrefere, 4, 11))
    AND tc.c = ROW.c_ofcontra_statuschanges
    AND tc.i = ROW.i_ofcontra_statuschanges
    AND toc.c_octaskty_tasks = TASK_TYPE_CLASS
    AND toc.i_octaskty_tasks = tasktype_id
    AND toc.creationdate = ( SELECT max (toc2.creationdate)
    FROM toctask toc2
    WHERE tcs.referencenumb in (toc2.subjectrefere, '100'||substr(toc2.subjectrefere, 4, 11))
    AND toc2.c_octaskty_tasks = TASK_TYPE_CLASS
    AND toc2.i_octaskty_tasks = tasktype_id );
    SELECT DECODE(m_Test,'Y','DISB','N','PDSB') INTO m_Status FROM Dual;
    EXCEPTION
    WHEN TOO_MANY_ROWS THEN m_Status:='PDSB';
    END;

  3. #3
    Join Date
    Feb 2001
    Posts
    20
    In case you can use a OUT variable which will have this value assinged to that varible and will be return . A stored provedure nevers returns a value unless ask to do so. Here u can also use a function instead of procedure.

  4. #4
    Join Date
    May 2002
    Posts
    1
    HI
    I think u can u se a explicit cursor and take the advantage of %found and %notfound

Posting Permissions

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


Click Here to Expand Forum to Full Width