与ruby一起使用sqlplus时出现的问题

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.

我想在ruby中使用sqlplus。不想使用任何宝石[因为我不能在没有其他团队帮助的情况下将它安装在我们的服务器上..等等]并且希望保持它非常小。

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

我在我的ruby脚本中尝试一些简单的事情：

 `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

任何人都可以帮助我上面的ERROR1和ERROR2

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 !

基本上对于“commit：not found”我认为它是在shell而不是在sqlplus中执行的。然而，似乎“设置serveroutput on”似乎执行正常！

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

对于ERROR2，我很无能为力。我也尝试使用转义斜杠为sql中的“/”。

Thanks

谢谢

2 个解决方案

#1

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.

答案是，不要使用SQL * Plus。不要在脚本中调用命令行实用程序;在ruby-oci8 gem和ruby-plsql gem之间，你可以做任何你可以在SQL * Plus中完成的事情。

#2

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.

您收到错误的原因是您要将每一行单独发送到shell。如果整个语句都包含在一对反引号中，那么它可能会起作用。

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:

但是如果你真的无法安装正确的宝石，请将命令放在一个临时文件中并告诉sqlplus执行它，例如：

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)
引用值（如果使用oci8 / dbi，则可以使用绑定变量）
Error handling. If using ruby libraries, errors would raise exceptions. Using sqlplus, you'll have to parse the output instead. Yuck!
错误处理。如果使用ruby库，错误会引发异常。使用sqlplus，您将不得不解析输出。呸！

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

所以它可以完成，但我强烈建议你跳过所需的箍，以便正确安装oci8（也许ruby-DBI）:)

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

ps你确定要在插入之前提交吗？

#1