Error when calling a Oracle Stored Proc

[sam944]

Hi,

Can some one tell me why I get this error message when I call my Oracle
Stored Procedure using the DBI Module ? ...

c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `execute': ORA-06550:
line 1, column 7: (DBI:atabaseError)
PLS-00201: identifier 'MY_TEST.Test_Stored_Proc' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored from
c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `exec'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:111:in `do_ocicall'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `exec'
from c:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8/OCI8.rb:162:in
`execute'
from c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:777:in `execute'
from C:/dev/ruby/progs/dbi/test_storeproc.rb:11
from c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:629:in `prepare'
from C:/dev/ruby/progs/dbi/test_storeproc.rb:7

My ruby code is as follows...

require "dbi"

dbh = DBI.connect('DBI:OCI8:SAMDEV.WORLD', 'username, 'password')

p1 = "Jtxy"

dbh.prepare("BEGIN MY_TEST.Test_Stored_Proc'(?,?); END;") do |sth|
sth.bind_param(1, p1)
po_resultString = ' ' * 256
sth.bind_param(2, resultStr)
sth.execute
while true
sth.execute
#puts sth.funcbind_value, 2)
end
end

Param #1 'p1' is a string IN param and Param2 ('resultStr') is a
VARCHAR2 OUT Param

It would be very helpful if someone could tell me what I am doing wrong
in my script

Many Thanks,

Sam

 

[Robert Klemme]

Hi,

Can some one tell me why I get this error message when I call my Oracle
Stored Procedure using the DBI Module ? ...

c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `execute': ORA-06550:
line 1, column 7: (DBI:atabaseError)
PLS-00201: identifier 'MY_TEST.Test_Stored_Proc' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored from

Are you sure this SP does exist? Did you maybe create the SP with
another user than the one you use from your script?

c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `exec'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:111:in `do_ocicall'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `exec'
from c:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8/OCI8.rb:162:in
`execute'
from c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:777:in `execute'
from C:/dev/ruby/progs/dbi/test_storeproc.rb:11
from c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:629:in `prepare'
from C:/dev/ruby/progs/dbi/test_storeproc.rb:7

My ruby code is as follows...

require "dbi"

dbh = DBI.connect('DBI:OCI8:SAMDEV.WORLD', 'username, 'password')

p1 = "Jtxy"

dbh.prepare("BEGIN MY_TEST.Test_Stored_Proc'(?,?); END;") do |sth|

There seems to be a single quote too much in this line.

sth.bind_param(1, p1)
po_resultString = ' ' * 256
sth.bind_param(2, resultStr)
sth.execute
while true
sth.execute
#puts sth.funcbind_value, 2)
end
end

Param #1 'p1' is a string IN param and Param2 ('resultStr') is a
VARCHAR2 OUT Param

It would be very helpful if someone could tell me what I am doing wrong
in my script

An alternative would be to use "CALL":

dbh.prepare("CALL MY_TEST.Test_Stored_Proc(?,?)") do |sth|

HTH

Kind regards

robert

 

[sam944]

Hi Robert

Thanks for your suggestion.

Unfortunately, It does not work for me eiather and I get this error
message

c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `execute': ORA-06576: not
a valid function or procedure name (DBI:atabaseError)
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `exec'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:111:in `do_ocicall'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:571:in `exec'
from c:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8/OCI8.rb:162:in
`execute'
from c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:777:in `execute'
from C:/dev/ruby/progs/dbi/test_storeproc.rb:12
from c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:629:in `prepare'
from C:/dev/ruby/progs/dbi/test_storeproc.rb:8

Sam

 

[ChrisH]

sam944 wrote:
....

dbh.prepare("BEGIN MY_TEST.Test_Stored_Proc'(?,?); END;") do |sth|

maybe this quote char is the issue?----------------------^ (between the
proc name and the open bracket)

 

[sam944]

I removed the extra quote characted between the procname and open
bracket. I still get the same error..

 

[sam944]

Does anyone have a sample Oracle Stroed Proc they are calling from Ruby
using DBI/OCI8 ?