如何在Ruby中运行Oracle存储过程 [英] How do I run an Oracle stored procedure in Ruby

问题描述

已授予我对Oracle数据库的读取权限，以获取我自己的数据库的数据. DBA给了我一个存储过程，他向我保证了我所需要的一切，但是我还无法从Ruby运行它.

I have been given read access to an Oracle database to get data for my own database. The DBA has given me a stored procedure that he assures me is all I need but I have been unable to run it from Ruby as yet.

我已经安装了ruby-oci8 gem和oracle Instant Client.

I have the ruby-oci8 gem and the oracle instant client installed.

这是我到目前为止所管理的.

Here's what I have managed so far.

require 'oci8'
conn = OCI8.new('user','pass','//remoteora1:1521/xxxx')
=> #<OCI8::RWHRUBY>
cursor = conn.parse("call REPOSITORY.GET_PMI_ADT( '722833', 'W', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)")
=> #<OCI8::Cursor:0x56f4d50>

这就是我卡住的地方.我有这个OCI8 :: Cursor对象，但是我不知道该怎么做.它应该返回一堆结果(查询中为空值)，但我什么也没得到.在有或没有参数的情况下运行cursor.exec(我不确定我需要什么参数)都会给我带来错误.

And this is where I get stuck. I have this OCI8::Cursor object but I don't know what to do with it. It should be returning me a whole bunch of results (where the null values are in the query) but I get nothing. Running cursor.exec with and without parameters (I'm not sure what parameters I need though) gives me errors.

cursor.exec给出

cursor.exec gives

OCIError: ORA-06553: PLS-:
ORA-06553: PLS-:
ORA-06553: PLS-:
ORA-06553: PLS-306: wrong number or typ
ORA-06553: PLS-306: wrong number or type of arguments in call to 'GET_PMI_ADT'
ORA-06553: PLS-306: wrong number or type of arguments in call to 'GET_PMI_ADT'
ORA-06553: PLS-306: wrong number or type of arguments in call to 'GET_PMI_ADT'
ORA-06553: PLS-306: wrong number or type of arguments in call to 'GET_PMI_ADT'
ORA-06553: PLS-306: wrong number or type of arguments in call to 'GET_PMI_ADT'
ORA-06553: PLS-306: wrong number or type of arguments in call to 'GET_PMI_ADT'
ORA-06553: PLS-306: wrong number or type of arguments in call to 'GET_PMI_ADT'

等...

cursor.fetch给出

cursor.fetch gives

OCIError: ORA-24338: statement handle not executed

有人在这里有什么想法吗?我的心情好极了.

Does anyone have any ideas here? I'm in well over my head.

这里的任何仍在观看的人的更新.如果我将存储过程更改为

An update here for anyone who's still watching. If I change the stored procedure to

BEGIN REPOSITORY.GET_PMI_ADT( '722833', 'W', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); END;

我现在得到了错误；

OCIERROR: ORA-06550: line 1, column 45:
PLS-00363: expression ' NULL' cannot be used as an assignment target

这是否确认存储过程不正确?有明显的纠正措施吗?

Does this confirm that the stored procedure is incorrect? Is there anything obvious to do to correct it?

推荐答案

这应该有所帮助:

http://rubyforge.org/forum/forum.php? thread_id = 11295& forum_id = 1078

示例如下:

msi处于IN变量状态，其余的credit是OUT变量

msi is in the IN variable status and remaining_credit are the OUT variables

cursor = conn.parse ('begin ROAMFLEX.GETMSISDNSTATUS(:msi, :status, :remaining_credit); end;')
cursor.bind_param(':msi', '250979923')
cursor.bind_param(':status', nil, String, 20)
cursor.bind_param(':remaining_credit', nil, String, 50)
cursor.exec()
puts cursor[':status']
puts cursor[':remaining_credit_amount']

这篇关于如何在Ruby中运行Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！