-
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
-
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;
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|