Home » SQL & PL/SQL » SQL & PL/SQL » Acess a remote database without database link please help
Acess a remote database without database link please help [message #297827] Sun, 03 February 2008 23:00 Go to next message
someswar1
Messages: 53
Registered: January 2008
Member
Hi,
Can anyone tell me how i access two database without using database link in oracle 9i. If One database in my machine is someswar and in other database name is remote database. so how i access remote database's particular users schema from someswar database. please help.

Regards:

Someswar Bhattacharjee

someswar11@gmail.com
Re: Acess a remote database without database link please help [message #297857 is a reply to message #297827] Mon, 04 February 2008 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why don't you want to use a dblink? It is exactly the tool for the job.
Re: Acess a remote database without database link please help [message #297874 is a reply to message #297857] Mon, 04 February 2008 01:57 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
Actually the client requirements . Is it possible to access a two database without using database link. If it is possible please help. Its really urgent.

Regards:
Someswar Bhattacharjee

someswar11@gmail.com
Re: Acess a remote database without database link please help [message #297878 is a reply to message #297874] Mon, 04 February 2008 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, use 2 connections, one for each databases.

Regards
Michel
Re: Acess a remote database without database link please help [message #297882 is a reply to message #297878] Mon, 04 February 2008 02:33 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
How i make a connection between two database.
If give me the code it will better for me.
One database name is = someswar

and another database name is = remote

Re: Acess a remote database without database link please help [message #297885 is a reply to message #297882] Mon, 04 February 2008 02:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If we give you the code it will be worse for you, because then you will give a solution to a client you don't understand.

But here is some code to connect to different databases :

sqlplus user/pass@someswar

sqlplus user/pass@remote
Re: Acess a remote database without database link please help [message #297888 is a reply to message #297827] Mon, 04 February 2008 03:12 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
sqlplus user/pass@someswar

sqlplus user/pass@remote



To run,it is also required to add the remote database in tnsname.
and if the remote database is not in your local network then you have to route the ip of the remote databese also.
Re: Acess a remote database without database link please help [message #297907 is a reply to message #297885] Mon, 04 February 2008 03:56 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
I want just procedure code that i pass
Re: Acess a remote database without database link please help [message #297910 is a reply to message #297907] Mon, 04 February 2008 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want just procedure code that i pass

In which language and what is pass from what to what?

Regards
Michel
Re: Acess a remote database without database link please help [message #297911 is a reply to message #297907] Mon, 04 February 2008 03:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is no "just procedure code that i pass" for your requirements.
Re: Acess a remote database without database link please help [message #297915 is a reply to message #297827] Mon, 04 February 2008 04:03 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
first of all write your procedure and run it,
then post the procedure and errors you face.

regards,
Re: Acess a remote database without database link please help [message #297919 is a reply to message #297915] Mon, 04 February 2008 04:12 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
I know this connection string. But How i access remote database scott users emp table from someswar database test user. what is theprocedure and the code.

Re: Acess a remote database without database link please help [message #297923 is a reply to message #297827] Mon, 04 February 2008 04:18 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
we can't provide anything until you not provide us for what you have asked earlier.

regards,
Re: Acess a remote database without database link please help [message #297925 is a reply to message #297919] Mon, 04 February 2008 04:18 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

But How i access remote database scott users emp table from someswar database test user



From inside the local database You CAN'T without a dblink.

You can just connect to the remote database or the local database, not BOTH at once without a dblink.
Re: Acess a remote database without database link please help [message #297929 is a reply to message #297874] Mon, 04 February 2008 05:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
someswar1 wrote on Mon, 04 February 2008 08:57
Actually the client requirements .

Ask the client why it is so.
Ask the client why he even bothers.
Re: Acess a remote database without database link please help [message #297935 is a reply to message #297929] Mon, 04 February 2008 05:11 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
Please tell me how i acess scott user emp table from hr user within a same database.
Re: Acess a remote database without database link please help [message #297936 is a reply to message #297919] Mon, 04 February 2008 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what is theprocedure and the code.

Michel Cadot wrote on Mon, 04 February 2008 10:58
Quote:
I want just procedure code that i pass

In which language and what is pass from what to what?

Regards
Michel


Re: Acess a remote database without database link please help [message #297938 is a reply to message #297936] Mon, 04 February 2008 05:18 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
in Oracle 9i
If the database name is someswar

one user name is scott
password is tiger

and same someswar database another

user name is hr
password is hr

in scott user the table is emp


i want to acces the scott user's emp table to access from hr user
Re: Acess a remote database without database link please help [message #297939 is a reply to message #297938] Mon, 04 February 2008 05:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then just prefix the objects with the other shema, e.g.
hr.tablename

[Updated on: Mon, 04 February 2008 05:22]

Report message to a moderator

Re: Acess a remote database without database link please help [message #297940 is a reply to message #297938] Mon, 04 February 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in ThomasG's answer:
Quote:
From inside the local database You CAN'T without a dblink.


And why don't you want to answer the questions we post you?

Regards
Michel
Re: Acess a remote database without database link please help [message #297941 is a reply to message #297939] Mon, 04 February 2008 05:23 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
But i heard without dblink it is possible in local database
Re: Acess a remote database without database link please help [message #297942 is a reply to message #297941] Mon, 04 February 2008 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

YOU CAN'T FROM INSIDE DATABASE WITHOUT A DB LINK.

Is this clear now?

Regards
Michel
Re: Acess a remote database without database link please help [message #297944 is a reply to message #297827] Mon, 04 February 2008 05:27 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
It seems that you are not careful to us.you are not answering for what you have asked.you have changed the question also.simply you are violeting the forum's rule.

regards,
Re: Acess a remote database without database link please help [message #297952 is a reply to message #297944] Mon, 04 February 2008 05:40 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
Hi i am not breaking any forum rule. what is the exact ques you are asking.
Re: Acess a remote database without database link please help [message #297953 is a reply to message #297952] Mon, 04 February 2008 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 04 February 2008 12:12
Quote:
what is theprocedure and the code.

Michel Cadot wrote on Mon, 04 February 2008 10:58
Quote:
I want just procedure code that i pass

In which language and what is pass from what to what?

Regards
Michel




Re: Acess a remote database without database link please help [message #297956 is a reply to message #297953] Mon, 04 February 2008 05:43 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
I am not written any procedure . I want to know just how this is possible without dblink
Re: Acess a remote database without database link please help [message #297957 is a reply to message #297956] Mon, 04 February 2008 05:46 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
LOL, this reminds me of Abott and Costello
Who's on First
Re: Acess a remote database without database link please help [message #297958 is a reply to message #297827] Mon, 04 February 2008 05:47 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
I am not written any procedure . I want to know just how this is possible without dblink

if this is your question then we have replied you several times.
please read earlier posts.

regards,
Re: Acess a remote database without database link please help [message #297960 is a reply to message #297956] Mon, 04 February 2008 05:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I am not written any procedure . I want to know just how this is possible without dblink



No problem. To not write a procedure without dblink, you can for instance just drink a cup of coffee. Then you haven't written a procedure and you haven't used a dblink. Laughing
Re: Acess a remote database without database link please help [message #297983 is a reply to message #297935] Mon, 04 February 2008 07:05 Go to previous message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
someswar1 wrote on Mon, 04 February 2008 12:11
Please tell me how i acess scott user emp table from hr user within a same database.

Well, that is something completely different - now you are talking about the SAME database.

In order to do that, user 'scott' will have to GRANT required privileges to user 'hr'. In your example, GRANT SELECT might be just enough. Here is an example:
SQL> connect scott/tiger@ora10
Connected.
SQL> grant select on emp to mike;

Grant succeeded.

SQL> connect mike/lion@ora10
Connected.
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

SQL>

Another option is a database link (of course; that's what you were talking about all the time) (still connected as 'mike'):
SQL> create database link dbl_scott
  2  connect to scott
  3  identified by tiger
  4  using 'ora10';

Database link created.

SQL> select count(*) from emp@dbl_scott;

  COUNT(*)
----------
        14

SQL>
It doesn't matter that we are talking about the same database - database link will still work. But, in your first message, you've asked about two different databases. In that case, you will have to create a database link.
Previous Topic: using external tables
Next Topic: what datatype is used to handle a very large string
Goto Forum:
  


Current Time: Mon Dec 05 07:07:39 CST 2016

Total time taken to generate the page: 0.11258 seconds