ssh and sql commands on another host help. 1

[bgreen]

Hi,

I am trying to run a sql command on another host which has multiple environments. I must run a script on the remote host to set the environment variables so I am running the sql command against the proper database. I am having some issues with getting output. any suggestions out there?

example:

ssh p690lp4 /u/applmgr/chg_mgmt_env INTFAPPS ; sqlplus apps/<password> select name from v$database

 

[PHV]

you may try this:
ssh p690lp4 "[!]. [/!]/u/applmgr/chg_mgmt_env INTFAPPS ; sqlplus apps/<password> select name from v$database"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bgreen]

I get this error message:

Usage: SQLPLUS [<option>] [<user>[/<password>] [@<host>]]
[@<startfile> [<parm1>] [<parm2>] ...]
where <option> ::= { -s | -? }
-s for silent mode and -? to obtain version number

 

[elgrandeperro]

You need to supress the $ sign in $database, with the " enclosed it is going to expand locally. Try it with a \ before the $.

gene

 

[bgreen]

that did't work

 

[PHV]

And this ?[tt]
ssh p690lp4 '. /u/applmgr/chg_mgmt_env INTFAPPS; echo "select name from v$database" | sqlplus apps/<password>' [/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[elgrandeperro]

Oh, is $database a variable or environment variable or is it the literal string '$database'? If it is a literal string, then :

ssh p690lp4 '. /u/applmgr/chg_mgmt_env INTFAPPS ; sqlplus apps/<password> select name from v\$database'

The single quotes sends the string, and the \$ suppresses the expansion of $database on the remote end.

gene

 

[bgreen]

didn't work either

 

[elgrandeperro]

I would simplify it:

First, if $database is a variable that is to be set by your call to /u/applmgr/chg_mgmt_env INTFAPPS, just execute:

ssh p690lp4 '/u/applmgr/chg_mgmt_env INTFAPPS ; echo $database'

and see that it returns a value.

Next, I would simplify the sql line to something short that doesn't need argument passing to see the sql gets called correctly.


gene

 

[p5wizard]

Gene,

That 'v$database" is a string literal: tha name of a system (pseudo)table in Oracle which holds info about the database you have logged on to. So sorry, you're barking up the wrong tree...


Bgreen,

I suggest PHV's solution, only some slight alterations:

ssh p690lp4 '. /u/applmgr/chg_mgmt_env INTFAPPS; echo "select name from v\$database;" | sqlplus apps/<password>'

This should run one shell at the remote end, first 'source'ing the env script with the '.' command, so that environment of than one shell is altered (very important) and then provides stdin for the sqlplus SQL interpreter by way of echo "sql-statement"|sqlplus username/password. sqlplus, once logged in to a database, reads SQL statements from stdin and provides the SQL output to stdout.

My alterations:
1) one has to 'hide' special characters from the shell in order to use them literally ($ for instance).
2) in ORACLE, an SQL statement needs to be terminated by a semicolon (.

Also, make sure that the login shell for the user is a korn or bourne for this syntax to work...


HTH,

p5wizard

 

[bgreen]

Thanks p5wizard. I did get some good data returned from your code suggestion. I will work with that. Thanks!

 

[bgreen]

Is there a way to run set commands?? set pagesize etc....

 

[p5wizard]

Have you tried:

ssh p690lp4 '. /u/applmgr/chg_mgmt_env INTFAPPS; echo "set pagesize 100\nselect name from v\$database;" | sqlplus apps/<password>'

I'm not sure, but I believe set commands do not require the ';' terminator. If yes, then try

ssh p690lp4 '. /u/applmgr/chg_mgmt_env INTFAPPS; echo "set pagesize 100;\nselect name from v\$database;" | sqlplus apps/<password>'

If you want to run more set commands:

ssh p690lp4 '. /u/applmgr/chg_mgmt_env INTFAPPS; echo "set pagesize 100\nset linesize 0\nselect name from v\$database;" | sqlplus apps/<password>'

Just put a '\n' or ';\n' after every command you want to run ('\n' is shell lingo for a newline character).

echo "set pagesize 100\nset linesize 0\nselect name from v\$database;" produces the text

----------------
set pagesize 100
set linesize 0
select name from v$database;
----------------

which you are pushing into standard input of the sqlplus command.

HTH,

p5wizard

 

[bgreen]

That works! You are gold p5wiz!