[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Driving Oracle sqlplus with open3

Berger, Daniel

7/26/2006 1:28:00 PM

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.
11 Answers

Berger, Daniel

7/26/2006 6:18:00 PM

0

Daniel Berger wrote:
> 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?

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.

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.

Ara.T.Howard

7/26/2006 6:23:00 PM

0

Berger, Daniel

7/26/2006 6:38:00 PM

0

ara.t.howard@noaa.gov wrote:

<snip>

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

Ara.T.Howard

7/26/2006 7:19:00 PM

0

Berger, Daniel

7/26/2006 7:41:00 PM

0

ara.t.howard@noaa.gov wrote:
> On Thu, 27 Jul 2006, Daniel Berger wrote:
>
>> 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

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 (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
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.

Ara.T.Howard

7/26/2006 8:00:00 PM

0

Berger, Daniel

7/26/2006 8:35:00 PM

0

ara.t.howard@noaa.gov wrote:
> On Thu, 27 Jul 2006, Daniel Berger wrote:
>
>> 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
>>
>
> try something like
>
> bash.execute cmd, 0 => sql+quit, 1 => STDOUT, 2 => STDERR

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

Ara.T.Howard

7/26/2006 8:54:00 PM

0

Pit Capitain

7/27/2006 9:12:00 AM

0

Daniel Berger 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

Pra Bhandar

3/3/2009 12:40:00 AM

0

Pit Capitain wrote:
> Daniel Berger 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
--
Posted via http://www.ruby-....