From: Daniel Berger <Daniel.Berger@...>
Date: 2006-07-27T04:41:24+09:00
Subject: Re: Driving Oracle sqlplus with open3

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.