Driving Oracle sqlplus with open3

[Daniel Berger]

Hi all,

Is it possible to drive Oracle's sqlplus (command line utility) with =
open3?=20
Don't ask me why, just nod your head and accept it please.

I seem to be able to connect and send sql, but I'm having trouble =
grabbing output:

# Attempt to interact with the sql shell
require 'open3'

cmd =3D 'user@database'
pass =3D 'xxxx'
sql =3D 'select sysdate from dual;'

Open3.popen3(cmd) do |stdin, stdout, stderr|
puts "Sending password..."
stdin.puts(pass)

puts "Sending sql..."
stdin.puts(sql)

# Hangs here
puts "Getting results"
uresults =3D stdout.read
puts "Results: #{results}"

puts "Quitting..."
stdin.puts('quit')
end

I tried wrapping the read method in its own Thread, but I couldn't make =
it=20
work. Tinkering with various sync options didn't help, though perhaps I =
set=20
them incorrectly.

Any ideas?

Thanks,

Dan


This communication is the property of Qwest and may contain confidential =
or
privileged information. Unauthorized use of this communication is =
strictly=20
prohibited and may be unlawful. If you have received this communication =

in error, please immediately notify the sender by reply e-mail and =
destroy=20
all copies of the communication and any attachments.

 

[Daniel Berger]

Hi all,
=20
Is it possible to drive Oracle's sqlplus (command line utility) with=20
open3? Don't ask me why, just nod your head and accept it please.
=20
I seem to be able to connect and send sql, but I'm having trouble=20
grabbing output:
=20
# Attempt to interact with the sql shell
require 'open3'
=20
cmd =3D 'user@database'
pass =3D 'xxxx'
sql =3D 'select sysdate from dual;'
=20
Open3.popen3(cmd) do |stdin, stdout, stderr|
puts "Sending password..."
stdin.puts(pass)
=20
puts "Sending sql..."
stdin.puts(sql)
=20
# Hangs here
puts "Getting results"
uresults =3D stdout.read
puts "Results: #{results}"
=20
puts "Quitting..."
stdin.puts('quit')
end
=20
I tried wrapping the read method in its own Thread, but I couldn't = make=20
it work. Tinkering with various sync options didn't help, though=20
perhaps I set them incorrectly.
=20
Any ideas?

Some more digging reveals that I'm actually getting this back from the=20
stdin.puts(sql) call:

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::=3D <username>[/<password>][@<connect_string>] | /

Google indicates that this is some kind of shell issue where my =
environment=20
variables aren't being picked up.

I'll post a solution if/when I find one.

Dan




This communication is the property of Qwest and may contain confidential =
or
privileged information. Unauthorized use of this communication is =
strictly=20
prohibited and may be unlawful. If you have received this communication =

in error, please immediately notify the sender by reply e-mail and =
destroy=20
all copies of the communication and any attachments.

 

[ara.t.howard]

Some more digging reveals that I'm actually getting this back from the
stdin.puts(sql) call:

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /

Google indicates that this is some kind of shell issue where my environment
variables aren't being picked up.

I'll post a solution if/when I find one.

drive it via session or under sh - that way your environment will be picked
up.

-a

 

[Daniel Berger]

(e-mail address removed) wrote:

drive it via session or under sh - that way your environment will be = picked
up.
=20
-a

Session looks interesting Ara, but how do I pass input to, say, an =
interactive=20
script? The README shows only output and error handles.

Thanks,

Dan


This communication is the property of Qwest and may contain confidential =
or
privileged information. Unauthorized use of this communication is =
strictly=20
prohibited and may be unlawful. If you have received this communication =

in error, please immediately notify the sender by reply e-mail and =
destroy=20
all copies of the communication and any attachments.

 

[ara.t.howard]

(e-mail address removed) wrote:



Session looks interesting Ara, but how do I pass input to, say, an
interactive script? The README shows only output and error handles.

Thanks,

Dan

harp:~ > cat a.rb
# system 'gem install session'
require 'rubygems' rescue nil
require 'session'

#
# initialize with /bin/sh, there are other shells supported too
#
sh = Session::Sh.new
#
# with redirects
#
stdin, stdout, stderr = '42', '', ''
sh.execute 'cat', 'stdin' => stdin, 'stdout' => stdout, 'stderr' => stderr
puts stdout
#
# same thing
#
stdin, stdout, stderr = '42', '', ''
sh.execute 'cat', :stdin => stdin, :stdout => stdout, :stderr => stderr
puts stdout
#
# same thing, for weak wrists
#
stdin, stdout, stderr = '42', '', ''
sh.execute 'cat', 0 => stdin, 1 => stdout, 2 => stderr
puts stdout
#
# note that this is __one__ instance of /bin/sh - so variables/state persist
# between calls
#
sh.execute 'var=42'
sh.execute 'echo "var : $var"', 1=>STDOUT


harp:~ > ruby a.rb
42
42
42
var : 42


-a

 

[Daniel Berger]

On Thu, 27 Jul 2006, Daniel Berger wrote:
=20

Some more digging reveals that I'm actually getting this back from = the=20
stdin.puts(sql) call:

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::=3D <username>[/<password>][@<connect_string>] | /

Google indicates that this is some kind of shell issue where my=20
environment variables aren't being picked up.

I'll post a solution if/when I find one.

=20
drive it via session or under sh - that way your environment will be = picked
up.
=20
-a

I tried session like so:

# sessiontest.rb
require 'rubygems'
require 'session'

cmd =3D 'sqlplus foo/xxx@mydb'
sql =3D 'select sysdate from dual;'
quit =3D 'quit'

bash =3D Session::Bash.new
bash.execute(cmd) do |output, error|
if error
puts "ERROR: #{error}"
exit
end
puts output if output
end

# Hangs here
bash.executestdin=3D>sql) do |output, error|
if error
puts "ERROR: #{error}"
exit
end
puts output if output
end

bash.execute(quit) do |output, error|
if error
puts "ERROR: #{error}"
exit
end
puts output if output
end

ruby sessiontest.rb

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 26 13:33:53 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

SQL> SP2-0734: unknown command beginning "export __e..." - rest of line =
ignored.
SQL> SP2-0734: unknown command beginning "echo '__CM..." - rest of line =
ignored.
SQL> SP2-0734: unknown command beginning "echo '__CM..." - rest of line =
ignored.

Then it hangs.

Am I doing something wrong? Maybe it just isn't possible to interact =
with the=20
sqlplus shell through the standard IO handles. :/

Regards,

Dan


This communication is the property of Qwest and may contain confidential =
or
privileged information. Unauthorized use of this communication is =
strictly=20
prohibited and may be unlawful. If you have received this communication =

in error, please immediately notify the sender by reply e-mail and =
destroy=20
all copies of the communication and any attachments.

 

[ara.t.howard]

I tried session like so:

# sessiontest.rb
require 'rubygems'
require 'session'

cmd = 'sqlplus foo/xxx@mydb'
sql = 'select sysdate from dual;'
quit = 'quit'

bash = Session::Bash.new
bash.execute(cmd) do |output, error|
if error
puts "ERROR: #{error}"
exit
end
puts output if output
end

# Hangs here
bash.executestdin=>sql) do |output, error|
if error
puts "ERROR: #{error}"
exit
end
puts output if output
end

bash.execute(quit) do |output, error|
if error
puts "ERROR: #{error}"
exit
end
puts output if output
end

try something like

bash.execute cmd, 0 => sql+quit, 1 => STDOUT, 2 => STDERR

Then it hangs.

Am I doing something wrong? Maybe it just isn't possible to interact with
the sqlplus shell through the standard IO handles. :/

possible. you may have to use pty.

-a

 

[Daniel Berger]

On Thu, 27 Jul 2006, Daniel Berger wrote:
=20
=20
try something like
=20
bash.execute cmd, 0 =3D> sql+quit, 1 =3D> STDOUT, 2 =3D> STDERR

<snip>

Excellent! That worked, thanks. The only think I noticed was that I =
had to=20
add an explicit newline to the sql statement to get it to work (but =
remember to=20
keep the semicolon).

For future Googlers, it's probably easier to parse the output if you =
connect=20
with sqlplus -S (silent mode).

Many thanks,

Dan


This communication is the property of Qwest and may contain confidential =
or
privileged information. Unauthorized use of this communication is =
strictly=20
prohibited and may be unlawful. If you have received this communication =

in error, please immediately notify the sender by reply e-mail and =
destroy=20
all copies of the communication and any attachments.

 

[ara.t.howard]

<snip>

Excellent! That worked, thanks. The only think I noticed was that I had to
add an explicit newline to the sql statement to get it to work (but remember
to keep the semicolon).

For future Googlers, it's probably easier to parse the output if you connect
with sqlplus -S (silent mode).

Many thanks,

Dan

ok. if that worked, and it's all you really need to do, something like this
may be simpler and not require session

require 'tempfile'
require 'open3'

tmp = Tempfile.new rand.to_s
tmp.write "your sql commands;\nquit"
tmp.close

Open3:pen3('sh') do |i,o,e|
i.puts "your sql shell command < #{ tmp.path }"
i.close

ot = Thread.new{ o.each{|line| STDOUT.puts line} }
et = Thread.new{ e.each{|line| STDERR.puts line} }

ot.join
et.join
end

food for thought.

-a

 

[Pit Capitain]

Is it possible to drive Oracle's sqlplus (command line utility) with
open3?

Dan, I've seen you already solved your problem. I'm driving SQL*Plus via
IO.popen all the time, so feel free to ask again if you have any more
questions.

Regards,
Pit

 

[Pra Bhandar]

Dan, I've seen you already solved your problem. I'm driving SQL*Plus via
IO.popen all the time, so feel free to ask again if you have any more
questions.

Regards,
Pit

hi,
I would be very interested to see what you did with IO.popen. Thanks
much in advance.

Prakash

 

[Pra Bhandar]

<snip>

Excellent! That worked, thanks. The only think I noticed was that I
had to
add an explicit newline to the sql statement to get it to work (but
remember to
keep the semicolon).

For future Googlers, it's probably easier to parse the output if you
connect
with sqlplus -S (silent mode).

Many thanks,

Dan


This communication is the property of Qwest and may contain confidential
or
privileged information. Unauthorized use of this communication is
strictly
prohibited and may be unlawful. If you have received this communication
in error, please immediately notify the sender by reply e-mail and
destroy
all copies of the communication and any attachments.

Hi Dan,
Could you please post the snippet of the script after you made the
changes? I am missing something i think in my similar script.

Thanks much.

Prakash