Home » SQL & PL/SQL » SQL & PL/SQL » Invalid Record Count in Oracle query while using dblink
Invalid Record Count in Oracle query while using dblink [message #209272] Thu, 14 December 2006 00:43 Go to next message
johnh_epa
Messages: 1
Registered: December 2006
Junior Member
while executing the following query i get invalied record count from oracle

insert into table1(field1,feeld2) values (select field1,databasefunction@dblink(param1,param2) from table2@dblink)

while executing the above query oracle says 1500 records inserted while actually it has inserted only 5 records.

If i remove the databasefunction from the select query then i get the correct count from oracle. The database function has a select query inside. Does oracle take the number of records fetched by the select query with the database function?? Is there a solution to it???
Re: Invalid Record Count in Oracle query while using dblink [message #209274 is a reply to message #209272] Thu, 14 December 2006 00:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
reported by johnh_epa
Reason This is a very critical issue and not many developers will have a solution to it. Hence the moderator can decide to escalate this problem if required.


MHE

[Updated on: Thu, 14 December 2006 00:47]

Report message to a moderator

Re: Invalid Record Count in Oracle query while using dblink [message #209291 is a reply to message #209274] Thu, 14 December 2006 02:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are there any triggers on the table?
How many rows does the select return?
Re: Invalid Record Count in Oracle query while using dblink [message #209324 is a reply to message #209274] Thu, 14 December 2006 05:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If it's a very urgent and critical problem, then you want to be talking to Metalink. They employ people to fix problems - we just do it as a hobby.
Re: Invalid Record Count in Oracle query while using dblink [message #209328 is a reply to message #209324] Thu, 14 December 2006 05:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't get the problem you report. Admittedly, your OP was a little shy of details, but what I've done sounds like what you did:
SQL> create database link loopback connect to john_test identified by john_test using 'fps_oratest';

Database link created.

SQL> create table dblink_driving_table (col_1  number, col_2 number);

Table created.

SQL> create table local_result_table   (col_1  number, col_2 number);

Table created.

SQL> insert into dblink_driving_table select mod(level,3),level from dual connect by level <= 200;

200 rows created.

SQL> CREATE OR REPLACE FUNCTION FUNCTION1 (p_col_1 in number)
  2  RETURN number AS
  3    v_return  number;
  4  BEGIN
  5    select max(col_2)
  6    into   v_return
  7    from   dblink_driving_Table 
  8    where  col_1 = p_col_1;
  9    
 10    return v_return;
 11  END;
 12  /

Function created.

SQL> insert into local_result_table (col_1,col_2) select col_1,function1@loopback(col_1) from dblink_driving_table@loopback;

200 rows created.

SQL> select count(*) from local_result_table;

  COUNT(*)
----------
       200
Previous Topic: How to find Oracle path
Next Topic: Question for PL/SQL Package dbms_repair
Goto Forum:
  


Current Time: Thu Dec 08 22:16:15 CST 2016

Total time taken to generate the page: 0.05351 seconds