Speaking to an Oracle base

[Paul Murray]

Dear Gurus,

I am trying to write a small script that will insert a row into an Oracle table on a central database server host.

Problem is, I want to execute this script on a number of different servers, some of which do not have Oracle installed.

Can you give me some pointers on how to speak to remote databases


Many thanks in advance,

Paul M.


PS -- Oracle v7.3.4.4, HPUXv11.

[John Palmer]

Hi,

You'll need to run the Oracle client 'sqlplus' somewhere.

This can either be:-
- locally on the server where the database is running
- on a remote server with Oracle client installed and with TNS configured so that Oracle's networking software can reach the database.

If you don't have Oracle client installed on a server then can you configure 'remsh' so that you can run a script on one that has?

Regards,
John

[Rhonda Thorne]

Paul,

Are you wanting to run this script at a UNIX level? IF so, hen you can do an if statement to check for pmon. If pmon is running, then oracle exists and is running on this server.

Hope this is helpful, but needed more information to offer more

Rhonda

[Carl Stehman]

If the scenario is that you want to connect to the
Oracle Host and execute a script that resides on the Oracle host to modify the database, you can do that a couple of ways. If you are running from a unix machine, you can use remsh to login and run a script that will connect to the instance and call sqlplus with the name of the script on the command line, or you can use the
ftp "quote SITE EXEC program" feature to invoke a script
that will call sqlplus with the name of the sql script on
the command line.

Hope this helps.

[Wodisch]

Hello Paul,

you have to install and configure "Oracle SQL*Net V2"
on the *other* machine. Installation is relatively simple
(except for mount the CD-Rom - check pfs_mount), and
the configuration on the client machine consists of just
one file: "$ORACLE_HOME/network/admin/tnsnames.ora"
or - if it exists there - "/etc/tnsnames.ora".

The content would be something like:
name = (DESCRIPTION=(
ADDRESS_LIST=(
ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)
)
)
(CONNECT_DATA=(SID=instance))
)

You havw to exchange the lower case words with your
own values: "name" is the name you will have to use as
"connect string" or "host string" in your command line
for "sqlplus" (see below), "hosts" is the name of your
Oracle-server machine, and "instance" must be the name
of the instance running on the server (usually in upper
case).

Be logged in as user "oracle" for the following commands.

Test it with "tnsping":
tnsping name
where "name" is the "name" of the paragraph in your
"tnsnames.ora". You must have started the "listener"
on the server machine before "pinging" it.

Use it with "sqplus":
sqplus user/password@name
where "name" is as before, "user" is the oracle-user
and "password" is his/her password. If you omit it,
you will be asked interactively. But you still need the
"@" character in front of "name" - and *NO* spaces
in between!

On your server machine you must have installed the
server part of "SQL*net V2" and it is only one config
file, again: "listener.ora" - same directories as for the
client.

The file would look like:

STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
USE_CKPFILE_LISTENER = false
USE_PLUG_AND_PLAY_LISTENER = true

LISTENER = (
ADDRESS_LIST =
(ADDRESS=(PROTOCOL=IPC)(KEY=instance))
(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))
)
SID_LIST_LISTENER= (
SID_LIST=
(SID_DESC=(SID_NAME=instance)(ORACLE_HOME=/opt/oracle))
)

Of course, you have to put in your own values...
And you have to start the listener, after that:
lsnrctl start

Check for the running "tnslsnr" process:
ps -ef | grep tns

HTH,
Wodisch