OraFAQ Forum: SQL & PL/SQL » sequel help to reference a different database on the same server

Home » SQL & PL/SQL » SQL & PL/SQL » sequel help to reference a different database on the same server

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sequel help to reference a different database on the same server [message #603879]

Fri, 20 December 2013 02:55

rkaria
Messages: 3
Registered: December 2013

Junior Member

Hi

I am trying to pull data through an ODBC connection and I need help in writing the correct sequel.

My situation is there are 2 oracle databases on the server one called LIVE and called TEST. Im connected to the live database and running a query but I want one of the functions that Im using in the query to run against the test databse.

Hope that makes more sense.

Can anyone advise the sequel to do this I have tried the below, but I just get 'error invalid identifier'.

1.testdb.Rfunctioname (which is the database and the function name.)
2.[testdb].Rfunctioname
3.datasource.testdb.Rfunctionname (which is the datasource.database.function)
4.datasource.Rfunctioname(which is the datasource and function name)
5.testdb.dbo.Rfunctioname

THanks
R

Report message to a moderator

Re: sequel help to reference a different database on the same server [message #603883 is a reply to message #603879]

Fri, 20 December 2013 03:23

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you, instead of using ODBC, create a database link between these two databases?

Report message to a moderator

Re: sequel help to reference a different database on the same server [message #603885 is a reply to message #603883]

Fri, 20 December 2013 03:26

rkaria
Messages: 3
Registered: December 2013

Junior Member

Im not sure how to do that?

Report message to a moderator

Re: sequel help to reference a different database on the same server [message #603886 is a reply to message #603885]

Fri, 20 December 2013 03:28

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

CREATE DATABASE LINK

Even if you use ODBC, what are the steps you are following? For two databases on two servers you would need two ODBC connections. Or, instead of two connections, you could use the connect property and pass the different connection atrributes as paramters.

[Updated on: Sat, 08 March 2014 13:30] by Moderator

Report message to a moderator

Re: sequel help to reference a different database on the same server [message #603891 is a reply to message #603886]

Fri, 20 December 2013 03:38

rkaria
Messages: 3
Registered: December 2013

Junior Member

Im creating the query within SSRS(reporting Services)for a report, but the connection is against ODBC.

I have 2 datasources within the report, one for live database and one for test database. Both database are on the same server.

The report looks at live database, but I want one of the functions in the query to look at the test database, I thought it would be as simple as referencing the datasource.database.NameofFunction since the test datasource is set up.

Im just not sure of the syntax in oracle

Report message to a moderator

Re: sequel help to reference a different database on the same server [message #603949 is a reply to message #603891]

Fri, 20 December 2013 10:57

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Access to database through ODBC does not depend on the rdbms. If you know how to do it for MS/Access (for instance) you know how to do it for Oracle.

If the function call is INSIDE a query (SELECT statement) then the solution is inside the database (and had nothing to do with ODBC or the way you access the database), and the solution is there, as already pointed, a database link.

Report message to a moderator

Previous Topic:	count according to headlines
Next Topic:	Help required to understand sys refcursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 01:41:54 CDT 2024