In December, when taking a procedure which I had developed as a stand-alone procedure and adding it to an existing package, I noticed that it initially ran correctly but after some time it failed - in that the cursor selecting data to process retrieved no rows.
So I created a new package to hold just this and other newly-developed procedures and they worked there for a while, but now at least one again fails to select data.
I have created a test procedure P in the original package and a stand-alone procedure P (WITH ABSOLUTELY IDENTICAL CODE). Code listing is at the end of this post.
When run in the package:
NOWASTE> exec bins_util.p
count(*)=0
PL/SQL procedure successfully completed.
When run standalone:
NOWASTE> exec p
count(*)=16
PL/SQL procedure successfully completed.
All code is owned and executed by the same user. The package and procedure are run with authid definer but as no other user is involved that seems irrelevant.
This is Oracle 11.2 (on Solaris I think)
NOWASTE> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
5 rows selected.
I don't know where to start searching for the reason for this behaviour and welcome suggestions.
Code listing:
create or replace
PROCEDURE P authid definer as
c pls_integer;
begin
select count(*)
into c
from
bins_request_sf sf,
cgd.block bact,
cgdmga.block bmga,
cgdmga.street_address sa
where sf.request_type = 'New Greenwaste – House'
and sf.block_key = bact.block_key
and sf.block_key = bmga.block_key
and sa.block_id = bmga.block_id
/*
and
(not exists
(select null from bin_service_location bsl
where bsl.submission_id = sf.submission_number
and bsl.bin_type_id = 'V')
and not exists
(select null
from bins_request t
where sf.submission_number = t.submission_number
and sf.request_type = t.request_type))
*/
order by sf.submission_number;
cgd_util.do_out('count(*)='||c);
end p;
/