Driving Oracle sqlplus with open3

Discussion in 'Ruby' started by Daniel Berger, Jul 26, 2006.

  1. 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, Jul 26, 2006
    #1
    1. Advertising

  2. Daniel Berger wrote:
    > 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.
    Daniel Berger, Jul 26, 2006
    #2
    1. Advertising

  3. Daniel Berger

    Guest

    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
    --
    suffering increases your inner strength. also, the wishing for suffering
    makes the suffering disappear.
    - h.h. the 14th dali lama
    , Jul 26, 2006
    #3
  4. Question about Session [was: Driving Oracle sqlplus with open3]

    wrote:

    <snip>

    > 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.
    Daniel Berger, Jul 26, 2006
    #4
  5. Daniel Berger

    Guest

    Re: Question about Session [was: Driving Oracle sqlplus with open3]

    On Thu, 27 Jul 2006, Daniel Berger wrote:

    > 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



    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
    --
    suffering increases your inner strength. also, the wishing for suffering
    makes the suffering disappear.
    - h.h. the 14th dali lama
    , Jul 26, 2006
    #5
  6. wrote:
    > 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.execute:)stdin=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.
    Daniel Berger, Jul 26, 2006
    #6
  7. Daniel Berger

    Guest

    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

    > 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
    --
    suffering increases your inner strength. also, the wishing for suffering
    makes the suffering disappear.
    - h.h. the 14th dali lama
    , Jul 26, 2006
    #7
  8. wrote:
    > On Thu, 27 Jul 2006, Daniel Berger wrote:
    >=20
    >> 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.execute:)stdin=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
    >>

    >=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.
    Daniel Berger, Jul 26, 2006
    #8
  9. Daniel Berger

    Guest

    On Thu, 27 Jul 2006, Daniel Berger wrote:

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


    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::eek: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
    --
    suffering increases your inner strength. also, the wishing for suffering
    makes the suffering disappear.
    - h.h. the 14th dali lama
    , Jul 26, 2006
    #9
  10. Daniel Berger

    Pit Capitain Guest

    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
    Pit Capitain, Jul 27, 2006
    #10
  11. Daniel Berger

    Pra Bhandar Guest

    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-forum.com/.
    Pra Bhandar, Mar 3, 2009
    #11
  12. Daniel Berger

    Pra Bhandar Guest

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

    >
    > <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
    --
    Posted via http://www.ruby-forum.com/.
    Pra Bhandar, Mar 3, 2009
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gianni

    Sqlplus - S stops ...sigh !

    Gianni, Jun 17, 2004, in forum: Perl
    Replies:
    4
    Views:
    6,392
    Karel Kubat
    Jul 1, 2004
  2. Gianni

    Sqlplus -S interrupt !!!!

    Gianni, Jun 17, 2004, in forum: Perl
    Replies:
    0
    Views:
    2,934
    Gianni
    Jun 17, 2004
  3. Replies:
    1
    Views:
    757
    Martin v. Löwis
    Jan 24, 2008
  4. Feyruz
    Replies:
    4
    Views:
    2,116
    Sherm Pendley
    Oct 14, 2005
  5. The alMIGHTY N

    SQLPlus with Perl

    The alMIGHTY N, Nov 2, 2006, in forum: Perl Misc
    Replies:
    7
    Views:
    1,572
    Peter Scott
    Nov 3, 2006
Loading...

Share This Page