| cursor error [message #376407] |
Wed, 17 December 2008 04:17  |
laith
Messages: 41
Registered: December 2008
Location: U.A.E
|
Member |
|
|
i have form using 2 non database block and i want just to use the cursor to show data, and this is the cursor that i use:
PROCEDURE SHOW_ROWS IS
BEGIN
DECLARE
CURSOR PTI_CURSOR IS
SELECT PYPRCSD_ZONE_CODE , pubmast_type , COUNT(*)
from PYPRCSD, pubmast x
where PYPRCSD_ACADEMIC_YEAR = :acy_year
and PYPRCSD_ACTIVE_IND = 'Y'
AND PYPRCSD_sticker_no IS NOT NULL
and pubmast_pidm = pyprcsd_pidm
and pubmast_seq_no = pyprcsd_bene_seq_no
and nvl(pubmast_sys_update_date,'01-jan-1900') = (select max(nvl(pubmast_sys_update_date,'01-jan-1900'))
from pubmast
where pubmast_pidm = x.pubmast_pidm
and pubmast_seq_no = x.pubmast_seq_no)
and PYPRCSD_ZONE_CODE != 'EXPIRED'
and trunc(PYPRCSD_RENEWAL_DATE) >= (:renewal_date)
group by PYPRCSD_ZONE_CODE,pubmast_type
order by PYPRCSD_ZONE_CODE,pubmast_type;
BEGIN
OPEN PTI_CURSOR ;
loop
FETCH PTI_CURSOR INTO :PYPRCSD_ZONE_CODE , :ZONE_TYPE, :ZONE_COUNT;
exit when PTI_CURSOR%notfound;
next_record;
end loop;
first_record;
close PTI_CURSOR;
END;
END;
the problem is the pointer going to the last row (after last record ) and give me error message :
FRM-40102: Record must be entered or deleted first , so please can you tell me how can i solve this problem. thank you
|
|
|
|
| Re: cursor error [message #376704 is a reply to message #376407] |
Thu, 18 December 2008 05:45  |
 |
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
|
Senior Member |
|
|
Hello,
I would suggest you to use rather a FROM_CLAUSE_QUERY block to which you would pass the SQL condition via a set_block_property call and invoking the query_data_source_name
a bit like this :
vsqlstmt := '(select <my_stuff...> where <my_conditions> )';
go_block('my_block');
clear_block(no_commit);
set_block_property('my_block',query_data_source_name,vsqlstmt);
you can even pass on a subsequent order_by too afterwards.
Try considering this instead of the cursor populating the block, okay ?
Cheers,
Didz
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
