Issues while using sqlplus with ruby

Question

I want to use sqlplus within ruby. Dont want to use any gems[bec I cannot get it installed on our servers without much help from other teams ..etc] and want to keep it very minimal.

I am trying something as simple as this in my ruby script:

 `rlwrap sqlplus user/pswd@host << EOF`

     `set serveroutput on;`
    `commit;`    #ERROR1: sh: commit: not found
     sql = "insert /*+ APPEND*/ INTO table(col1, col2) values (#{data[0]},#{data[1]});"
     `#{sql}`    #ERROR2: sh: Syntax error: "(" unexpected

Can anyone help me with ERROR1 and ERROR2 above

Basically for "commit: not found" I think its getting executed on shell rather than in sqlplus. However seems like "set serveroutput on" seems to execute fine !

For ERROR2, I am clueless. I also tried using escape slash for the "/" in the sql.

Thanks

Answer 1

The reason you get the errors is that you are sending each line to the shell individually. If your entire statement was wrapped in a single pair of backticks, it might work.

But if you really are unable to install the proper gems, put the commands in a temporary file and tell sqlplus to execute that, eg:

require 'tempfile'
file = Tempfile.open(['test', '.sql'])
file.puts "set serveroutput on;"
file.puts "commit;"
file.puts "insert /*+ APPEND*/ INTO table(col1, col2) values (#{data[0]},#{data[1]});"
file.puts "exit;"  # needed or sqlplus will never return control to your script
file.close
output = `sqlplus user/pswd@host @#{file.path}`
file.unlink

You'll have to be very careful about:

• Quoting values (if using oci8/dbi you could use bind variables)
• Error handling. If using ruby libraries, errors would raise exceptions. Using sqlplus, you'll have to parse the output instead. Yuck!

So it can be done but I highly recommend you jump through whatever hoops are required to get oci8 (and maybe ruby-DBI) installed properly :)

ps are you sure you want to commit before the insert?

Answer 2

The answer is, don't use SQL*Plus. Don't call a command-line utility from inside your script; between the ruby-oci8 gem and the ruby-plsql gem, you can do anything you could accomplish from within SQL*Plus.