Driving Oracle sqlplus with open3

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. :slight_smile:

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 = ‘[email protected]
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. :confused:

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/[email protected]
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. :confused:

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs