Home » SQL & PL/SQL » SQL & PL/SQL » Query over dblink from orecle9 to oracle11 (Oracle9, Oracle 11)
Query over dblink from orecle9 to oracle11 [message #593798] Wed, 21 August 2013 03:17 Go to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Hi all, I have something strange :
I am executing a select on a single table using a dblink from oracle9 to oracle11 and i don't know why automatically add in the select the rowid at the end. The problem is that inside the query i have a group by so it crash.

Any suggestion?

Query sample :

original query from vb:
select a, b, c
from mytable
group by a, b, c

query intercepted on oracle11:
select a, b, c, rowid
from mytable
group by a, b, c


Thanks
Oliver
Re: Query over dblink from orecle9 to oracle11 [message #593800 is a reply to message #593798] Wed, 21 August 2013 03:24 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Welcome to the forum.

Please read and follow How to use [code] tags and make your code easier to read?

What error are you getting?
Why don't you use distinct instead?
Re: Query over dblink from orecle9 to oracle11 [message #593801 is a reply to message #593798] Wed, 21 August 2013 03:27 Go to previous messageGo to next message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
Your question is not clear. Neither of your queries (please format them with [code] tags next time) uses a database link, can you re-phrase the question?

--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Re: Query over dblink from orecle9 to oracle11 [message #593806 is a reply to message #593801] Wed, 21 August 2013 03:45 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Ok...we are migrating our DB from Oracle9 to Oracle11 so not to creating problem to the society we manage first we create a dblink on Oracle9 that read table on Oracle11.
I have a simple select like below that come from Visual Basic code and when I excute it I get the error :
ORA-00979: it isn't a GROUP BY expression

original query:
SELECT mycolumn
FROM mytale
GROUP BY my column


So if I try to find the query executed on Oracle11, I notice that it add the rowid as the last column and the group by crash naturally
query excuted on oracle11 with Session Browser tool:
SELECT mycolumn, rowid
FROM mytale
GROUP BY my column



I hope now is more clear

Thanks
Oliver
Re: Query over dblink from orecle9 to oracle11 [message #593808 is a reply to message #593806] Wed, 21 August 2013 03:49 Go to previous messageGo to next message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
"my column" should read "mycolumn". I still don 't see any database link. man!
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

Re: Query over dblink from orecle9 to oracle11 [message #593816 is a reply to message #593806] Wed, 21 August 2013 04:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
noflyingzone wrote on Wed, 21 August 2013 14:15
So if I try to find the query executed on Oracle11, I notice that it add the rowid as the last column and the group by crash naturally


Are you using any tool and using show query or something like that?

You say the select query is from visual basic code which runs fine in Oracle 9i, but using dblinks when executed in 11g it throws error. So how are these 3 things connected in your system?
Re: Query over dblink from orecle9 to oracle11 [message #593825 is a reply to message #593816] Wed, 21 August 2013 04:52 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Yes executing the query directly on Oracle9 works fine, not the same thing throws dblink....
About the dblink I don't know because it was created from another person and he is in holiday at the moment.

query with dblink:

SELECT mycolumn, rowid
FROM mytable@mydblink
GROUP BY mycolumn
Re: Query over dblink from orecle9 to oracle11 [message #593826 is a reply to message #593825] Wed, 21 August 2013 04:55 Go to previous messageGo to next message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
Look, you have now shown the query. That is a good start. You might want to show the results as well.
Re: Query over dblink from orecle9 to oracle11 [message #593828 is a reply to message #593825] Wed, 21 August 2013 05:03 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
noflyingzone wrote on Wed, 21 August 2013 10:52
Yes executing the query directly on Oracle9 works fine, not the same thing throws dblink....
About the dblink I don't know because it was created from another person and he is in holiday at the moment.

query with dblink:

SELECT mycolumn, rowid
FROM mytable@mydblink
GROUP BY mycolumn


So the query is coded with rowid in? because obviously that won't work.
Re: Query over dblink from orecle9 to oracle11 [message #593830 is a reply to message #593825] Wed, 21 August 2013 05:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
Still not clear, what do you want from us? Do you want to know from where has the ROW_ID come from in the query? Just remove the ROW_ID and run the same query in 11g too with the dblink at it's place.
Re: Query over dblink from orecle9 to oracle11 [message #593839 is a reply to message #593830] Wed, 21 August 2013 07:15 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Excuse me but I think the is a missunderstand.
I want to say that in the original query, that work fine in oracle9, I didn't put the rowid statement ....it seem that there is something like a translator that introduce the rowid as new column when the query pass throw the dblink to oracle11.

I hope its clear otherwise I don't now how to explain it.

Thanks Oliver
Re: Query over dblink from orecle9 to oracle11 [message #593840 is a reply to message #593839] Wed, 21 August 2013 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can we reproduce and see what you see?

Regards
Michel
Re: Query over dblink from orecle9 to oracle11 [message #593841 is a reply to message #593840] Wed, 21 August 2013 07:20 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
We aren't aware of any such translator and unless you provide precise details about how the query is run (is it in a db procedure? in vb code? where exactly is the db link referenced?) and how you are determining that the rowid is being added, we can't begin to even guess as to what is causing the problem.
Re: Query over dblink from orecle9 to oracle11 [message #593842 is a reply to message #593841] Wed, 21 August 2013 07:27 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
I notice that if the query result is more than 10 records it puts the rowid such as it would index record Sad( my head goes around
Re: Query over dblink from orecle9 to oracle11 [message #593843 is a reply to message #593839] Wed, 21 August 2013 07:29 Go to previous messageGo to next message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
noflyingzone wrote on Wed, 21 August 2013 13:15
Excuse me but I think the is a missunderstand.
I want to say that in the original query, that work fine in oracle9, I didn't put the rowid statement ....it seem that there is something like a translator that introduce the rowid as new column when the query pass throw the dblink to oracle11.

I hope its clear otherwise I don't now how to explain it.

Thanks Oliver
Oliver, can you not show what the problem is? Like this -
orclz>
orclz> create table mytable(mycolumn number);

Table created.

orclz> insert into mytable values(1);

1 row created.

orclz> create database link mydblink using 'orclz';

Database link created.

orclz> SELECT mycolumn
  2  FROM mytable@mydblink
  3  GROUP BY mycolumn;

  MYCOLUMN
----------
         1

orclz>
No problem.
Re: Query over dblink from orecle9 to oracle11 [message #593844 is a reply to message #593839] Wed, 21 August 2013 07:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
noflyingzone wrote on Wed, 21 August 2013 17:45
a translator that introduce the rowid as new column when the query pass throw the dblink to oracle11.


A query is passed through the dblink? How?
Re: Query over dblink from orecle9 to oracle11 [message #593846 is a reply to message #593844] Wed, 21 August 2013 08:12 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
This is the real case, the query above is the original that I execute correctly on Oracle9 before we create the dblink to Oracle11

  SELECT MCCCDSOC,
         MCCCDFOR,
         MCCDSFOR,
         MCCDSPRS,
         MCCDSIND,
         MCCDSLOC,
         MCCCDCAP,
         MCCCDPRV,
         MCCCDNAZ,
         MCCCDFIS,
         MCCFLINT
    FROM MCDGMFG.MCTTBFOR
    WHERE    MCCCDSOC = 'NR'
    GROUP BY MCCCDSOC, MCCCDFOR, MCCDSFOR, MCCDSPRS, MCCDSIND, MCCDSLOC,
         MCCCDCAP, MCCCDPRV, MCCCDNAZ, MCCCDFIS, MCCFLINT
		ORDER BY MCCCDSOC, MCCCDFOR



Instead this one is the query that I see with Session Browser tool throws dblink to Oracle11

  SELECT "A1"."MCCCDSOC",
         "A1"."MCCCDFOR",
         "A1"."MCCDSFOR",
         "A1"."MCCDSPRS",
         "A1"."MCCDSIND",
         "A1"."MCCDSLOC",
         "A1"."MCCCDCAP",
         "A1"."MCCCDPRV",
         "A1"."MCCCDNAZ",
         "A1"."MCCCDFIS",
         "A1"."MCCFLINT",
         "A1".ROWID
    FROM "MCDGMFG"."MCTTBFOR" "A1"
    WHERE    "A1"."MCCCDSOC" = 'NR'
		ORDER BY "A1"."MCCCDSOC", "A1"."MCCCDFOR"



Strange things :
- I don't put the alias on table "A1"
- I don't put the last column "A1".ROWID

Is there any parameter or particular setting to force the creation of the dblink ?????
Re: Query over dblink from orecle9 to oracle11 [message #593847 is a reply to message #593846] Wed, 21 August 2013 08:14 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Another thing:
for each table there is a synonym that uses the dblink to point the relative table on Oracle11
Re: Query over dblink from orecle9 to oracle11 [message #593848 is a reply to message #593847] Wed, 21 August 2013 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again:
Quote:
How can we reproduce and see what you see?


Which tool do you use to query?
What is the version (with 4 decimals) of each database?
What is the version of the listener that serves the dblink?

Post a complete test case we can execute in our environment.

Regards
Michel
Re: Query over dblink from orecle9 to oracle11 [message #593850 is a reply to message #593846] Wed, 21 August 2013 08:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
noflyingzone wrote on Wed, 21 August 2013 18:42

Instead this one is the query that I see with Session Browser tool throws dblink to Oracle11


Which tool?
Re: Query over dblink from orecle9 to oracle11 [message #593851 is a reply to message #593848] Wed, 21 August 2013 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also give the sql used to create the synonyms and the db link.

And in your last post the transformed query is missing the group by, is that correct?
Re: Query over dblink from orecle9 to oracle11 [message #593853 is a reply to message #593850] Wed, 21 August 2013 08:29 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Toad for Oracle Version 11.6.0.43
Re: Query over dblink from orecle9 to oracle11 [message #593855 is a reply to message #593851] Wed, 21 August 2013 08:31 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Yes in all
Re: Query over dblink from orecle9 to oracle11 [message #593856 is a reply to message #593851] Wed, 21 August 2013 08:32 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
cookiemonster wrote on Wed, 21 August 2013 15:21
Also give the sql used to create the synonyms and the db link.

And in your last post the transformed query is missing the group by, is that correct?



Yes correct
Re: Query over dblink from orecle9 to oracle11 [message #593857 is a reply to message #593855] Wed, 21 August 2013 08:33 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
noflyingzone wrote on Wed, 21 August 2013 14:31
Yes in all

Yes in all what?
Re: Query over dblink from orecle9 to oracle11 [message #593858 is a reply to message #593857] Wed, 21 August 2013 08:34 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
No I see ...excuse me Yes in only the last

[Updated on: Wed, 21 August 2013 08:35]

Report message to a moderator

Re: Query over dblink from orecle9 to oracle11 [message #593860 is a reply to message #593851] Wed, 21 August 2013 08:37 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The "A1" alias is indeed added by the remote execution, like mentioned here at the bottom of the page. I see that alias quite often when looking at queries running over a dblink.

I have never seen the rowid added, but I could imagine the optimizer pulls the remote rowid under some circumstances where it thinks that it has to access the rows again later. Perhaps it tries to first get all the rows from the remote source instance, and then do the group by in it's own local instance?

[Updated on: Wed, 21 August 2013 08:38]

Report message to a moderator

Re: Query over dblink from orecle9 to oracle11 [message #593861 is a reply to message #593851] Wed, 21 August 2013 08:37 Go to previous messageGo to next message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
That is how oracle rewrites the linked query inside the other database. As for the rowid, if you are using something like toad it always adds the rowid so it has a handle on the row. If you run your query using the database link in SQL*PLUS does it fail? unless you use a driving site hint, it will bring over the selected rows and do the grouping on the first machine.

[Updated on: Wed, 21 August 2013 08:38]

Report message to a moderator

Re: Query over dblink from orecle9 to oracle11 [message #593862 is a reply to message #593856] Wed, 21 August 2013 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
noflyingzone wrote on Wed, 21 August 2013 14:32
cookiemonster wrote on Wed, 21 August 2013 15:21
Also give the sql used to create the synonyms and the db link.

And in your last post the transformed query is missing the group by, is that correct?



Yes correct


You said originally said the transformation was adding rowid, not removing group by. Indeed the error message suggests group by remains.
Start from the beginning and give us the following:
1) sql for db link
2) sql to create the table
3) sql to create the synonym
4) the exact 9i query along with the exact details of where it is run from (db proceudre, sqlplus, vb, something else?)
5) the exact error message
6) The exact version of both DBs
Re: Query over dblink from orecle9 to oracle11 [message #593863 is a reply to message #593858] Wed, 21 August 2013 08:40 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
noflyingzone wrote on Wed, 21 August 2013 14:34
No I see ...excuse me Yes in only the last


Only the last what?
You don't get charged by the word to post here you know.
Re: Query over dblink from orecle9 to oracle11 [message #593864 is a reply to message #593861] Wed, 21 August 2013 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Bill B wrote on Wed, 21 August 2013 14:37
As for the rowid, if you are using something like toad it always adds the rowid so it has a handle on the row.

If you tell it to get all the data in a table, sure. If you're running a hand writen query in the sql window and it does it that would be a bug.
Re: Query over dblink from orecle9 to oracle11 [message #593866 is a reply to message #593864] Wed, 21 August 2013 08:59 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Excuse me all I thing to have made some confusion whit all the post


Re: Query over dblink from orecle9 to oracle11 [message #593867 is a reply to message #593853] Wed, 21 August 2013 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
noflyingzone wrote on Wed, 21 August 2013 15:29
Toad for Oracle Version 11.6.0.43


Through away TOAD, it is b...
Start to work with SQL*Plus.

And you didn't answer to my other questions.

Anyway, in the end, it is just a TOAD problem, you should post your question in a Quest forum.

Regards
Michel

Re: Query over dblink from orecle9 to oracle11 [message #593869 is a reply to message #593867] Wed, 21 August 2013 09:06 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Michel Cadot wrote on Wed, 21 August 2013 16:01
noflyingzone wrote on Wed, 21 August 2013 15:29
Toad for Oracle Version 11.6.0.43


And you didn't answer to my other questions.

Regards
Michel



Now I organize all the answers and then i post it correctly

Thanks
Oliver
Re: Query over dblink from orecle9 to oracle11 [message #593870 is a reply to message #593862] Wed, 21 August 2013 09:08 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
[quote title=cookiemonster wrote on Wed, 21 August 2013 15:38][quote title=noflyingzone wrote on Wed, 21 August 2013 14:32]cookiemonster wrote on Wed, 21 August 2013 15:21
Also give the sql used to create the synonyms and the db link.

Start from the beginning and give us the following:
1) sql for db link
2) sql to create the table
3) sql to create the synonym
4) the exact 9i query along with the exact details of where it is run from (db proceudre, sqlplus, vb, something else?)
5) the exact error message
6) The exact version of both DBs


I find out all the correct answer .....
Thanks Oliver
Re: Query over dblink from orecle9 to oracle11 [message #593871 is a reply to message #593867] Wed, 21 August 2013 09:16 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Wed, 21 August 2013 15:01

Anyway, in the end, it is just a TOAD problem, you should post your question in a Quest forum.


I'm not all convinced it is. Just because the OP is using toad session browser to see the query on 11g it doesn't follow that toad is the cause. It hasn't even been confirmed that the problem query is being run from toad.
Re: Query over dblink from orecle9 to oracle11 [message #593872 is a reply to message #593871] Wed, 21 August 2013 09:30 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
cookiemonster wrote on Wed, 21 August 2013 16:16
Michel Cadot wrote on Wed, 21 August 2013 15:01

Anyway, in the end, it is just a TOAD problem, you should post your question in a Quest forum.


I'm not all convinced it is. Just because the OP is using toad session browser to see the query on 11g it doesn't follow that toad is the cause. It hasn't even been confirmed that the problem query is being run from toad.


Also for me, I am not sure and I'm not an Oracle expert but I think there is something to specificate in the creation of the dblink or any instruction to put in the query....I hope it isn't the second supposition otherwise I have to chech all the code of the programs whit the same cases of error.

[Updated on: Wed, 21 August 2013 09:31]

Report message to a moderator

Re: Query over dblink from orecle9 to oracle11 [message #593873 is a reply to message #593872] Wed, 21 August 2013 09:32 Go to previous messageGo to next message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
Noflyingzone,

What program are you using to run your query??? Please be specific!!
Re: Query over dblink from orecle9 to oracle11 [message #593876 is a reply to message #593873] Wed, 21 August 2013 09:45 Go to previous messageGo to next message
noflyingzone
Messages: 30
Registered: August 2013
Member
Bill B wrote on Wed, 21 August 2013 16:32
Noflyingzone,

What program are you using to run your query??? Please be specific!!



If you mean the language used for develop my program is Visual basic
Re: Query over dblink from orecle9 to oracle11 [message #593878 is a reply to message #593876] Wed, 21 August 2013 09:52 Go to previous messageGo to previous message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
Oliver, this is dragging on a bit. You have shown the query that runs, correctly, against 9i. You have not shown that query that uses a database link. Can you please just paste it here and run it? From SQL*Plus?
You saw the way I did it as a demonstration, that is all that is needed.
Previous Topic: dynamic sql
Next Topic: Indexes & Views
Goto Forum:
  


Current Time: Sat Sep 20 15:50:57 CDT 2014

Total time taken to generate the page: 0.07856 seconds