Error when calling a Oracle Stored Proc

Discussion in 'Ruby' started by sam944, Jun 22, 2006.

  1. sam944

    sam944 Guest

    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
     
    sam944, Jun 22, 2006
    #1
    1. Advertising

  2. sam944 wrote:
    > 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
     
    Robert Klemme, Jun 22, 2006
    #2
    1. Advertising

  3. sam944

    sam944 Guest

    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

    Robert Klemme wrote:
    > sam944 wrote:
    > > 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
     
    sam944, Jun 22, 2006
    #3
  4. sam944

    ChrisH Guest

    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)
     
    ChrisH, Jun 22, 2006
    #4
  5. sam944

    sam944 Guest

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


    ChrisH wrote:
    > 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, Jun 22, 2006
    #5
  6. sam944

    sam944 Guest

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

    sam944 wrote:
    > I removed the extra quote characted between the procname and open
    > bracket. I still get the same error..
    >
    >
    > ChrisH wrote:
    > > 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, Jun 22, 2006
    #6
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?RXJpYyBMYW5k?=
    Replies:
    1
    Views:
    751
    Chandra Sekhar
    Aug 7, 2004
  2. Jack Black
    Replies:
    3
    Views:
    2,793
    =?Utf-8?B?UGF1bA==?=
    Apr 20, 2005
  3. David Lozzi
    Replies:
    3
    Views:
    1,941
    David Lozzi
    Jun 1, 2005
  4. king kikapu

    PyODBC Stored proc calling

    king kikapu, Jan 18, 2007, in forum: Python
    Replies:
    1
    Views:
    563
    king kikapu
    Jan 19, 2007
  5. Doug
    Replies:
    0
    Views:
    121
Loading...

Share This Page