| Exception NO_DATA_FOUND [message #624357] |
Mon, 22 September 2014 06:13  |
 |
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
|
Member |
|
|
Hi all,
I have to display Cast_prize and Roll_prize.
I wrote below code to fetch respective prize.
I am getting problem when my SELECT query return no data then, cursor is pointing at exception.
Instead of this I want to check next record up to :system.last_record='TRUE'.
Please help me..
BEGIN
FIRST_RECORD;
LOOP
IF :BLOCK_STEEL_GRADE_MST.CAST_PRIZE IS NULL AND :BLOCK_STEEL_GRADE_MST.ROLL_PRIZE IS NULL THEN
SELECT T.UNIT_RATE INTO :BLOCK_STEEL_GRADE_MST.CAST_PRIZE
FROM TRANSACTIONS_MST T
WHERE T.EFF_DT = :BLOCK_PARAM.DOC_DT
AND T.GRADE_CD = :BLOCK_STEEL_GRADE_MST.GRADE_CD
AND T.CR_TYPE = 'C'
AND T.UNIT_RATE >0;
SELECT T.UNIT_RATE INTO :BLOCK_STEEL_GRADE_MST.ROLL_PRIZE
FROM TRANSACTIONS_MST T
WHERE T.EFF_DT = :BLOCK_PARAM.DOC_DT
AND T.GRADE_CD = :BLOCK_STEEL_GRADE_MST.GRADE_CD
AND T.CR_TYPE = 'R'
AND T.UNIT_RATE >0;
END IF;
NEXT_RECORD;
EXIT WHEN :SYSTEM.LAST_RECORD='TRUE';
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:BLOCK_STEEL_GRADE_MST.CAST_PRIZE := NULL;
:BLOCK_STEEL_GRADE_MST.ROLL_PRIZE := NULL;
NEXT_RECORD;
WHEN OTHERS THEN
NULL;
END;
|
|
|
|
|
|
| Re: Exception NO_DATA_FOUND [message #624368 is a reply to message #624357] |
Mon, 22 September 2014 06:33  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Try this...
And if want to write
DECLARE
CURSOR c1( pass parametere's')
IS
SELECT t.unit_rate
FROM transactions_mst t
WHERE t.eff_dt = :block_param.doc_dt
AND t.grade_cd = :block_steel_grade_mst.grade_cd
AND t.cr_type = 'C'
AND t.unit_rate > 0;
---Write here another cursor
BEGIN
BEGIN
FOR c_rec IN c1(pass parametere's')
LOOP
.....
..........
..........
END LOOP;
END;
Begin
SELECT count(1)
into v_count
FROM transactions_mst t
WHERE t.eff_dt = :block_param.doc_dt
AND t.grade_cd = :block_steel_grade_mst.grade_cd
AND t.cr_type = 'C'
AND t.unit_rate > 0;
End;
if v_count=0 then
exception....
.....
end if;
END;
Or
BEGIN
first_record;
LOOP
Begin
IF :BLOCK_STEEL_GRADE_MST.cast_prize IS NULL
AND :BLOCK_STEEL_GRADE_MST.roll_prize IS NULL THEN
SELECT T.unit_rate
INTO :BLOCK_STEEL_GRADE_MST.cast_prize
FROM transactions_mst T
WHERE T.eff_dt = :BLOCK_PARAM.doc_dt
AND T.grade_cd = :BLOCK_STEEL_GRADE_MST.grade_cd
AND T.cr_type = 'C'
AND T.unit_rate > 0;
SELECT T.unit_rate
INTO :BLOCK_STEEL_GRADE_MST.roll_prize
FROM transactions_mst T
WHERE T.eff_dt = :BLOCK_PARAM.doc_dt
AND T.grade_cd = :BLOCK_STEEL_GRADE_MST.grade_cd
AND T.cr_type = 'R'
AND T.unit_rate > 0;
Execption
WHEN NO_DATA_FOUND THEN
:BLOCK_STEEL_GRADE_MST.cast_prize:= NULL;
:BLOCK_STEEL_GRADE_MST.roll_prize := NULL;
End;
END IF;
next_record;
EXIT WHEN :SYSTEM.last_record = 'TRUE';
END LOOP;
next_record;
END;
[Updated on: Mon, 22 September 2014 06:35] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
