Sql*plus On Your Pc Connecting to a remote database

[yordan 10]

1. INTRODUCTION.

 

Most of you are familiar using Oracle sql*plus, in order to perform very simple queries, or writing down complex sql scripts.

In order to do that, you usually simply rlogin or putty to the remote Unix/Linux machine and run your queries in a command-line prompt window.

When you want to create complicated (at least several-lines long) scripts, or test the reproducibility of a table/index creation script, you start it on the Linux remote system. If you want to modify it, you get it back on your PC, modify it with your favourite developer’s program (mine is Notepad++Portable), and put it back on the Linux server and fire it.

In the present tuto I will show you how to install sql*plus on your PC, and use it in order to access the Oracle database on the remote Unix/Linux server.

 

 

2. FIRST OF ALL, GET THE SOFTWARE.

 

You first need to obtain the Oracle thin client and the sql*plus binaries. After that we will put them somewhere in your system, configure the connector description file, and tell Windows how to manage this in a user configuration variable.

First of all, go to http://forums.xisto.com/no_longer_exists/ , sign up the disclosure agreement, and get the two zip files

http://download.oracle.com/errors/download-fail-1505220.html

and http://download.oracle.com/errors/download-fail-1505220.html

 

3. UNZIP THE SOFWARE IN A TEMPORARY PLACE.

 

Click on each .zip downloaded file, and uncompress it in a random temporary place, let’s say c:\tmp

 

4. CONFIGURE THE SOFTWARE.

 

Copy all the uncompressed files in a single folder, let’s say c:\oracle\client.

In this location, create a file named tnsnames.ora.

This file contains the lines concerning the network information allowing sql*plus to access to the remote database using the thin client software you just put in this place. The best way is to do “vi tnsnames.ora” in a putty window, copy the few lines concerning the database you will reach, and paste them in notepad on your PC file.

Here is my c:\oracle\client\tnsnames.ora file :

MYDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = mydb)

)

)

 

“mydb” is the name of the Oracle database on the remote Linux database as well as the local name I have chosen on my PC, “rac1” is the name of the remote host where my database is running, it has to be present in my c:\windows\system32\drivers\etc\host file, if not simply replace the words “rac1” by the IP address of your Linux machine.

 

5. TELL WINDOWS WHERE THE FILES ARE.

 

Now we finished configuring the software, we know where it is (namely it is in the c:\oracle\client folder) but Microsoft windows does not know where to find it.

Now, we have to tell windows where to find this software. We do not do this with registry values, we do this with user environment variables.

Environment variables are the values Windows currently know. If you open a “cmd” window on your PC and you type “set”, you will see all the environment variables currently known by the system, you will see a lot of lines showing each defined user variable and it’s current value.

We need to do two things : update the “path” variable and add a TNS_ADMIN variable.

You simply have to add c:\oracle\client at the end of the Path value, and add the following variable :

TNS_ADMIN= c:\oracle\client

If you don’t remember how to do that, please refer to my tutorial here :

http://forums.xisto.com/topic/96264-topic/?findpost=1064395315

 

6. THAT’S ALL, LET’S TEST.

 

That’s all, you finish installing everything. Let’s test it. Open a new “cmd” command line Window on your PC, and type sqlplus myuser/mypassword@mydb

(“sqlplus” will start the local sqlplus.exe on your PC, @mydb tells sqlplus to connect to the remote (@) database named mydb defined in the tnsnames.ora, with the remote user named “myuser” whose password is “mypassword”

(of course, if some curious people are around you, you don’t type your password and you will be invisible prompted for the password).

You will see

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Oct 29 16:52:57 2008

 

Copyright © 1982, 2007, Oracle. All rights reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>

This means that your local sqlplus.exe, whose version is 11.1.0.6.0, successfully connected to the remote database, which is a 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production (you noticed that my database server is a 64 bit machine, currently it’s an AIX machine but this method works on any Unix and any Linux machine).

That’s all, you were successful installing the downloaded software, configuring it, and you successfully tested it.

Now you are ready for using your local development tool in order to create sql scripts, and run them locally on your PC using your local sqlplus.exe, performing actions on the remote Oracle database.

And this installation is harmful for your PC, each file is in the same location, nothing is in Windows registry or somewhere else. If you want to remove this from your PC, you simply remove the c:\oracle\client folder.

[Quatrux 4]

I'm using SQL plus from Oracle, even though it's not the best thing I've ever used, but I needed to create a DB using Oracle for university, so I used it as most of other students, but I think I used version 9 and not 11, even though I don't really know what is the difference, I didn't really like it's interface, don't know about 11 version, but I have done my things using Notepad2 because it is easier to edit and write sql queries there and later I just copy pasted them to see if it works..The fun thing was to drop, update, delete data in the lectures, most of other students didn't know sql though..

[yordan 10]

I think I used version 9 and not 11

I showed my example with Oracle 11i because it's the last one. However, for basic commands, 9i works very well.Simply, some complicated things work well with the latest version, and some old bugs in the older versions are fixed in the new version.
However, what is new in this topic, is that you don't need to have the Oracle database engine installed on your PC, you only need sql*plus and the network dll's, the data are remote. You use the local tools on your PC in order to prepare your scripts, and you run them on your PC, dropping and creating the data on the remote database.

[Quatrux 4]

If I got this right, yeah we done the same in the university, I only had sql plus at my home computer installed and overwritten some files to connect to a remote server and I always connected to the uni server with a username, password and host string to use the queries from the book with the DB I connected, the funny thing, students had permissions to drop and delete data, update and insert, so you know ho usually it ended, but that wasn't a problem usually.. later I could login to a remote server in the uni from anywhere with my own username and password, that noone could do anything with my created DB which everyone needed to create..To connect to a remote server, all I needed is how you told, to edit the .ora file and I think I also overwritten something more in a forms90 directory, well it was some time ago, can't really remember.