join two commands 1

[watergrinder]

Could somebody tell how to execute the following at once from a script?

ssh userid@serverb
sqlplus system/dem@servb

If I use them as shown above, script logs in the remote system and remains idle with prompt. I want to login to remote system and immediately give sqlplus command.
Also, please give an example of a simple select statement along with the sqlplus command. Thanks in advance.

 

[PHV]

Something like this ?
echo "SELECT * FROM myTable" |
ssh userid@serverb sqlplus system/dem@servb

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

 

[watergrinder]

Thanks a lot for the response.

Any idea about this:

Authentication successful.
/usr/bin/sh: sqlplus: not found.

 

[PHV]

And what about this ?
echo "SELECT * FROM myTable" |
ssh userid@serverb '. /path/to/oracleprofile; sqlplus system/dem@servb'
The basic idea is to build a working environment for sqlplus.

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

 

[watergrinder]

The working environment seems to be set with this:

echo "SELECT count(*) FROM PLAN_TABLE" | ssh userid@serverb '. /home/oracle/.profile;sqlplus system/dem@servb'

It's connecting and not executing query

Authentication successful.
Not a terminal
stty: : Not a typewriter
stty: : Not a typewriter
                                                       
SQL*Plus: Release 8.1.7.0.0 on Mon Jun 7 18:02:01 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 
JServer Release 8.1.7.0.0 
 
SQL>   2  Disconnected from Oracle8i Enterprise Edition

 

[watergrinder]

count(*) is 400.

 

[PHV]

Have you tried this ?
echo "SELECT count(*) FROM PLAN_TABLE[highlight];[/highlight]" | ssh userid@serverb '. /home/oracle/.profile;sqlplus [highlight]-s [/highlight]system/dem@servb'

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

 

[watergrinder]

Thanks. Can we spool the output here?

 

[watergrinder]

I tried using

echo "SELECT count(*) FROM PLAN_TABLE;" | ssh userid@serverb '. /home/oracle/.profile;sqlplus -s system/dem@servb' > outputfile. I am getting a lot of extra information before the output line.

I made it like this:

exec 1> ${SQLTEMP}
exec 2>/dev/null

ssh userid@servera'. /home/oracle/.profile;sqlplus system/dem@serva' << EOF
@test4.sql
exit
EOF

I put the sql query in a .sql file at remote user account. executed it.

Format:

sqlquery
exit


It doesn't print output but gives this:

    ^[2 ^[2     ^[2     ^[2     ^[2^M                                                                        ^M
SQL*Plus: Release 8.1.7.0.

(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0
JServer Release 8.1.7.0.0 
 
SQL>   3    4  Input truncated to 6 characters
  5  Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 
JServer Release 8.1.7.0.0

 

[PHV]

Sorry I'm absolutely not an oracle guy.
the only things I know is the -s option to get rid of the sqlplus banner.

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

 

[clayj]

PH,
I'm interested in how the command string you provided works.

echo "SELECT count(*) FROM PLAN_TABLE;" | ssh userid@serverb '. /home/oracle/.profile;sqlplus -s system/dem@servb'

I understand how to execute a cmd/script on a remote host with ssh (the string in single quotes being that which is executed). What I don't understand is the first half of this statement with the echo'd select being piped to the remote host cmd. Does this echo'd string in effect become input to the remote cmd?

Thanks in advance. I'm a unix newbie, and just trying to understand.

 

[PHV]

In your shell's man page take a look at I/O redirection and pipeline.
In the above command line, the command echo is piped to the command ssh, ie the standard output of echo becomes the standard input of ssh (and therefore the stty's errors...).

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