Strange Procedural record count over db_link
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Strange Procedural record count over db_link

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    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.

  2. #2
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Here's a quick example of what I'm looking at:

    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>
    Last edited by AJW_ID01; 09-03-2010 at 04:22 AM.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Couple of questions...

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

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    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:

    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.



    - Tony.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width