Error when calling a Oracle Stored Proc

S

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::DatabaseError)
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.func:)bind_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
 
R

Robert Klemme

sam944 said:
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::DatabaseError)
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.func:)bind_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
 
S

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

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

sam944

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

sam944

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,780
Messages
2,569,611
Members
45,286
Latest member
ChristieSo

Latest Threads

Top