Hi all,
Is it possible to drive Oracle’s sqlplus (command line utility) with
open3?
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 = ‘user@database’
pass = ‘xxxx’
sql = ‘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 = 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
work. Tinkering with various sync options didn’t help, though perhaps I
set
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
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.
Daniel B. wrote:
it work. Tinkering with various sync options didn’t help, though
perhaps I set them incorrectly.
Any ideas?
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 ::= [/][@<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.
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.
[email protected] wrote:
drive it via session or under sh - that way your environment will be picked
up.
-a
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
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.
On Thu, 27 Jul 2006, Daniel B. wrote:
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
On Thu, 27 Jul 2006, Daniel B. wrote:
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
On Thu, 27 Jul 2006, Daniel B. wrote:
bash = Session::Bash.new
if error
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
[email protected] wrote:
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
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.execute(:stdin=>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 © 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
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
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.
[email protected] wrote:
quit = ‘quit’
Hangs here
puts "ERROR: #{error}"
exit
end
puts output if output
end
try something like
bash.execute cmd, 0 => sql+quit, 1 => STDOUT, 2 => STDERR
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.
On Thu, 27 Jul 2006, Daniel B. wrote:
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::open3(‘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
Daniel B. schrieb:
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
Daniel B. wrote:
[email protected] wrote:
quit = ‘quit’
Hangs here
puts "ERROR: #{error}"
exit
end
puts output if output
end
try something like
bash.execute cmd, 0 => sql+quit, 1 => STDOUT, 2 => STDERR
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
Pit C. wrote:
Daniel B. schrieb:
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
hi,
I would be very interested to see what you did with IO.popen. Thanks
much in advance.
Prakash
