Strange Procedural record count over db_link

Hi,

I am currently working with an 11.1.0.6 64-bit Standard Database.

For dev/testing, I am connecting this into a 9.0.1 64-bit Enterprise Database.

In SQL*PLus, I can run some SQL that extracts a PK recordset from across a db_link, and this returns around 617,000 rows.

If I put together the same cursor within a procedure, this same SQL is only returning 590,000 or so records.

I am at a complete loss as to what is going on.

Has anyone experienced this kind of behaviour?

It's first thing in the morning for me here, but I've had coffee!!

I've defined the SQL dynamically using both a SYS_REFCURSOR, and also running it as an EXECUTE IMMEDIATE (eventually the SQL is going to be dynamic).

I've even hardcoded the exact SQL that should connect across this particular link, in case I was being an idiot and it was connecting somewhere else - which it really can't - but I'm pulling my hair out with it here!

Comments anyone?

T.

Here's a quick example of what I'm looking at:

Quote:

---

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 3 10:17:14 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production

SQL> set serverout on
SQL> declare
v_sql varchar2(32767);
type t_order_no is table of varchar2(10) index by simple_integer;
v_order_no t_order_no;

begin
execute immediate
'select order_no from cs3sys.opheadm@cliff where status in (''C'',''I'',''9'',''8'')'
bulk collect into v_order_no;
dbms_output.put_line(v_order_no.count);
end;
/

589823

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM CS3SYS.OPHEADM@CLIFF WHERE STATUS IN ('C','I','9','8');

COUNT(*)
----------
617929

SQL> declare
v_sql varchar2(32767);
type t_order_no is table of varchar2(10) index by simple_integer;
v_order_no t_order_no;
begin
execute immediate
'select order_no from cs3sys.opheadm@cliff where status in (''C'',''I'',''9'',''8'')'
bulk collect into v_order_no;
dbms_output.put_line(v_order_no.count);
end;
/

65535

PL/SQL procedure successfully completed.

SQL>

---

Couple of questions...

1- Is cs3sys.opheadm@cliff a static table?
2- What happens if you run those statements directly logged into cliff?

Hi,

It's not a static table, it holds all customer orders (open and closed), but I'm only looking at the closed ones (denoted by the status codes).

The correct return result is the 617,000+ that gets returned from the SQL syntax, and this value is the same - whether run on the remote DB or the host DB.

The return result is always less (this 589,000 or so) when returned within a PL/SQL block.

Within the same session, the first PL/SQL return is this 589,000 - and if run immediately again, it only returns 65535 - and forever thereafter.

In a fresh session, if I run the SQL code first and then a PL/SQL block, the PL/SQL block will only ever return the 65535 rowcount.

This is just weird. I am just not fathoming what could possibly be going on here.

There isn't anything weird and wonderful like RLS involved, just PL/SQL is doing something odd.

If I run a PL/SQL block that just does something like:

Quote:

---

declare
v_count number;
begin
select count(*)
into v_count
from cs3sys.opheadm@cliff where status in ('C','I','8','9');
dbms_output.put_line(v_count);
end;
/

---

This returns the correct 617K+ rowcount.

Just trying to return some of the data into collections is not working...

I was working on a different dataset yesterday (looking at order detail lines), and I was happily returning 6 million+ entries into a collection and doing some processing - and this was all working correctly.

:confused:

- Tony.