FAQ
Hello
I am using the zxJDBC package with jython (similar to python), and I
am having "python error PLS-00306: wrong number or types of arguments"
error when using the "callproc()" method to execute a stored
procedure.

The Oracle stored procedure takes a single OUT varchar2 parameter. My
code is as follows:

p = [None]
c.callproc('pkg1_returns', p);

...
What I am doing corresponds to the examples..but I can seem to know
why it is not working. Help.


"Adeoluwa"

Search Discussions

  • John Gordon at Jul 13, 2011 at 5:40 pm

    In <01efb6ac-deaa-4bdb-8b2d-b603bdddec57 at n5g2000yqh.googlegroups.com> Adeoluwa Odein <stratfordtenants at gmail.com> writes:

    Hello
    I am using the zxJDBC package with jython (similar to python), and I
    am having "python error PLS-00306: wrong number or types of arguments"
    error when using the "callproc()" method to execute a stored
    procedure.
    The Oracle stored procedure takes a single OUT varchar2 parameter. My
    code is as follows:
    p = [None]
    c.callproc('pkg1_returns', p);
    If the procedure takes a varchar2 parameter, why are you passing [None]?

    It might help if you posted the method signature of the Oracle stored
    procedure you're trying to call.

    --
    John Gordon A is for Amy, who fell down the stairs
    gordon at panix.com B is for Basil, assaulted by bears
    -- Edward Gorey, "The Gashlycrumb Tinies"
  • Adeoluwa Odein at Jul 13, 2011 at 5:58 pm
    Thanks, your assistance will be greatly appreciated on the right way
    forward. See the Stored Procedure Below -very simple:



    create or replace package c2_pkg
    as
    procedure openc;
    procedure closec;
    procedure RS22(v out varchar);
    end;
    /

    create or replace package body c2_pkg
    as
    v_first_time boolean := TRUE;
    v_cursor number;
    cursor srvr_cur
    is
    select distinct b.mid from SVR a,VAR b where a.mid = b.mid;

    procedure openc
    as
    begin
    if not srvr_cur%ISOPEN
    then
    open srvr_cur;
    end if;

    end openc;

    procedure closec
    as
    begin
    close srvr_cur;
    end closec;


    procedure RS22(v out varchar2)
    as
    -- Server varchar2(64);

    begin
    Server := NULL;
    fetch srvr_cur into Server;
    v := Server;

    end RS22;

    end;
    /



















    On Jul 13, 1:40?pm, John Gordon wrote:
    In <01efb6ac-deaa-4bdb-8b2d-b603bddde... at n5g2000yqh.googlegroups.com> Adeoluwa Odein <stratfordtena... at gmail.com> writes:
    Hello
    I am using the zxJDBC package with jython (similar to python), and I
    am having "python error PLS-00306: wrong number or types of arguments"
    error when using the "callproc()" method to execute a stored
    procedure.
    The Oracle stored procedure takes a single OUT varchar2 parameter. ?My
    code is as follows:
    p = [None]
    c.callproc('pkg1_returns', p);
    If the procedure takes a varchar2 parameter, why are you passing [None]?

    It might help if you posted the method signature of the Oracle stored
    procedure you're trying to call.

    --
    John Gordon ? ? ? ? ? ? ? ? ? A is for Amy, who fell down the stairs
    gor... at panix.com ? ? ? ? ? ? ?B is for Basil, assaulted by bears
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- Edward Gorey, "The Gashlycrumb Tinies"
  • John Gordon at Jul 13, 2011 at 6:10 pm

    In <9e937261-d05d-477a-90d2-a690e85e124d at h17g2000yqn.googlegroups.com> Adeoluwa Odein <stratfordtenants at gmail.com> writes:

    Thanks, your assistance will be greatly appreciated on the right way
    forward. See the Stored Procedure Below -very simple:
    I don't see a procedure named "pkg1_returns", which is the prodecure
    called by your code. Where is this procedure?

    --
    John Gordon A is for Amy, who fell down the stairs
    gordon at panix.com B is for Basil, assaulted by bears
    -- Edward Gorey, "The Gashlycrumb Tinies"
  • Adeoluwa Odein at Jul 13, 2011 at 6:18 pm
    The actual jython/python call is:

    p = [None]
    c.callproc('c2_pkg.RS22', p);

    I used a placeholder initially; now that you have the SQL code, there
    it is. It essentially invokes the stored procedure, and it should
    return the OUT variable p, with some value. It doesn't have to be a
    cursor fetch; even a minor text assignment.





    On Jul 13, 2:10?pm, John Gordon wrote:
    In <9e937261-d05d-477a-90d2-a690e85e1... at h17g2000yqn.googlegroups.com> Adeoluwa Odein <stratfordtena... at gmail.com> writes:
    Thanks, your assistance will be greatly appreciated on the right way
    forward. ?See the Stored Procedure Below -very simple:
    I don't see a procedure named "pkg1_returns", which is the prodecure
    called by your code. ?Where is this procedure?

    --
    John Gordon ? ? ? ? ? ? ? ? ? A is for Amy, who fell down the stairs
    gor... at panix.com ? ? ? ? ? ? ?B is for Basil, assaulted by bears
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- Edward Gorey, "The Gashlycrumb Tinies"
  • John Gordon at Jul 13, 2011 at 6:26 pm

    In <d45161dc-648c-4a44-a563-317b5f5e5699 at h14g2000yqd.googlegroups.com> Adeoluwa Odein <stratfordtenants at gmail.com> writes:

    The actual jython/python call is:
    p = [None]
    c.callproc('c2_pkg.RS22', p);
    I used a placeholder initially; now that you have the SQL code, there
    it is. It essentially invokes the stored procedure, and it should
    return the OUT variable p, with some value. It doesn't have to be a
    cursor fetch; even a minor text assignment.
    That procedure is defined as taking one parameter, but you're passing
    an empty parameter list. Why?

    --
    John Gordon A is for Amy, who fell down the stairs
    gordon at panix.com B is for Basil, assaulted by bears
    -- Edward Gorey, "The Gashlycrumb Tinies"
  • Adeoluwa Odein at Jul 13, 2011 at 6:32 pm

    On Jul 13, 2:26?pm, John Gordon wrote:
    In <d45161dc-648c-4a44-a563-317b5f5e5... at h14g2000yqd.googlegroups.com> Adeoluwa Odein <stratfordtena... at gmail.com> writes:
    The actual jython/python call is:
    It's taking an OUT parameter.. I'm just following the examples as
    documented by zxJDBC. How can I fix it?



    p = [None]
    c.callproc('c2_pkg.RS22', p);
    I used a placeholder initially; now that you have the SQL code, there
    it is. ?It essentially invokes the stored procedure, and it should
    return the OUT variable p, with some value. ?It doesn't have to be a
    cursor fetch; even a minor text assignment.
    That procedure is defined as taking one parameter, but you're passing
    an empty parameter list. ?Why?

    --
    John Gordon ? ? ? ? ? ? ? ? ? A is for Amy, who fell down the stairs
    gor... at panix.com ? ? ? ? ? ? ?B is for Basil, assaulted by bears
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- Edward Gorey, "The Gashlycrumb Tinies"
  • John Gordon at Jul 13, 2011 at 8:09 pm

    It's taking an OUT parameter.. I'm just following the examples as
    documented by zxJDBC. How can I fix it?
    I suspect the example you're looking at was for a procedure which has no
    arguments, so in that case it would make sense to pass an empty parameter
    list.

    I haven't worked with OUT parameters so I don't know if this will work,
    but try it and see what happens:

    my_string = ""
    p = [my_string]
    c.callproc('c2_pkg.RS22', p);
    print p

    --
    John Gordon A is for Amy, who fell down the stairs
    gordon at panix.com B is for Basil, assaulted by bears
    -- Edward Gorey, "The Gashlycrumb Tinies"
  • Adeoluwa Odein at Jul 13, 2011 at 8:33 pm

    On Jul 13, 4:09?pm, John Gordon wrote:
    It's taking an OUT parameter.. I'm just following the examples as
    documented by zxJDBC. ?How can I fix it?
    I suspect the example you're looking at was for a procedure which has no
    arguments, so in that case it would make sense to pass an empty parameter
    list.

    I haven't worked with OUT parameters so I don't know if this will work,
    but try it and see what happens:

    ? my_string = ""
    ? p = [my_string]
    ? c.callproc('c2_pkg.RS22', p);
    ? print p

    --
    John Gordon ? ? ? ? ? ? ? ? ? A is for Amy, who fell down the stairs
    gor... at panix.com ? ? ? ? ? ? ?B is for Basil, assaulted by bears
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- Edward Gorey, "The Gashlycrumb Tinies"

    The same error. The sample were found on the following site --I copied
    exactly what is written there:
    1. http://www.jython.org/archive/21/docs/zxjdbc.html
  • John Gordon at Jul 13, 2011 at 9:02 pm

    In <86b9e6f2-e18e-41b9-92a2-86ea8d7b4592 at f35g2000vbr.googlegroups.com> Adeoluwa Odein <stratfordtenants at gmail.com> writes:

    The same error. The sample were found on the following site --I copied
    exactly what is written there:
    1. http://www.jython.org/archive/21/docs/zxjdbc.html
    Ah, I see. You're supposed to call c.fetchall() afterwards to retrieve
    the OUT parameter.

    Also, the example page defines the called object as a function, not a
    procedure. Maybe that's the problem? Try defining RS22 as a function
    and see if that helps.

    You might also try defining it outside of a package, as that is how the
    example code does it.

    --
    John Gordon A is for Amy, who fell down the stairs
    gordon at panix.com B is for Basil, assaulted by bears
    -- Edward Gorey, "The Gashlycrumb Tinies"
  • Adeoluwa Odein at Jul 13, 2011 at 9:06 pm

    On Jul 13, 5:02?pm, John Gordon wrote:
    In <86b9e6f2-e18e-41b9-92a2-86ea8d7b4... at f35g2000vbr.googlegroups.com> Adeoluwa Odein <stratfordtena... at gmail.com> writes:
    The same error. The sample were found on the following site --I copied
    exactly what is written there:
    1.http://www.jython.org/archive/21/docs/zxjdbc.html
    if you define the function in the execute() method, it works (as seen
    on the page). But this is a stored procedure already residing on the
    DB. A function/procedure outside of a package, actually works, but
    then you lose access to private data; which is while I used a package.


    Ah, I see. ?You're supposed to call c.fetchall() afterwards to retrieve
    the OUT parameter.

    Also, the example page defines the called object as a function, not a
    procedure. ?Maybe that's the problem? ?Try defining RS22 as a function
    and see if that helps.

    You might also try defining it outside of a package, as that is how the
    example code does it.

    --
    John Gordon ? ? ? ? ? ? ? ? ? A is for Amy, who fell down the stairs
    gor... at panix.com ? ? ? ? ? ? ?B is for Basil, assaulted by bears
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- Edward Gorey, "The Gashlycrumb Tinies"
  • John Gordon at Jul 13, 2011 at 9:19 pm

    In <0730c5fb-3b65-45ce-9cc5-69d639f48281 at g2g2000vbl.googlegroups.com> Adeoluwa Odein <stratfordtenants at gmail.com> writes:

    if you define the function in the execute() method, it works (as seen
    on the page). But this is a stored procedure already residing on the
    DB. A function/procedure outside of a package, actually works, but
    then you lose access to private data; which is while I used a package.
    Did you try changing RS22 from a procedure to a function inside the
    package?

    --
    John Gordon A is for Amy, who fell down the stairs
    gordon at panix.com B is for Basil, assaulted by bears
    -- Edward Gorey, "The Gashlycrumb Tinies"
  • Adeoluwa Odein at Jul 13, 2011 at 9:28 pm

    On Jul 13, 5:19?pm, John Gordon wrote:
    In <0730c5fb-3b65-45ce-9cc5-69d639f48... at g2g2000vbl.googlegroups.com> Adeoluwa Odein <stratfordtena... at gmail.com> writes:
    if you define the function in the execute() method, it works (as seen
    on the page). ?But this is a stored procedure already residing on the
    DB. ?A function/procedure outside of a package, actually works, but
    then you lose access to private data; which is while I used a package.
    Did you try changing RS22 from a procedure to a function inside the
    package?

    --
    John Gordon ? ? ? ? ? ? ? ? ? A is for Amy, who fell down the stairs
    gor... at panix.com ? ? ? ? ? ? ?B is for Basil, assaulted by bears
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- Edward Gorey, "The Gashlycrumb Tinies"
    Correction, the previous actually works, and still gives me access to
    private data. So I will most likely use it.
    Basically, just call a function, outside a package. It resolves this
    entire dilemma.
    Implementing similar program in Perl DBI, works without any problem.
    Python/Jython seem quite difficult to work with Store Procedures, in
    my opinion. Thanks a lot.
  • Adeoluwa Odein at Jul 13, 2011 at 9:28 pm

    On Jul 13, 5:19?pm, John Gordon wrote:
    In <0730c5fb-3b65-45ce-9cc5-69d639f48... at g2g2000vbl.googlegroups.com> Adeoluwa Odein <stratfordtena... at gmail.com> writes:
    if you define the function in the execute() method, it works (as seen
    on the page). ?But this is a stored procedure already residing on the
    DB. ?A function/procedure outside of a package, actually works, but
    then you lose access to private data; which is while I used a package.
    Did you try changing RS22 from a procedure to a function inside the
    package?

    --
    John Gordon ? ? ? ? ? ? ? ? ? A is for Amy, who fell down the stairs
    gor... at panix.com ? ? ? ? ? ? ?B is for Basil, assaulted by bears
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- Edward Gorey, "The Gashlycrumb Tinies"
    The same problem, if done inside a package. I just left it outside a
    package, and it works.
  • Terry Reedy at Jul 13, 2011 at 9:18 pm

    On 7/13/2011 4:33 PM, Adeoluwa Odein wrote:

    The same error. The sample were found on the following site --I copied
    exactly what is written there:
    1. http://www.jython.org/archive/21/docs/zxjdbc.html
    The jython-users mailing list might be a better place to ask your
    question. Most people here are CPython users.

    http://sourceforge.net/mail/?group_id867

    --
    Terry Jan Reedy
  • Adeoluwa Odein at Jul 13, 2011 at 6:33 pm

    On Jul 13, 2:26?pm, John Gordon wrote:
    In <d45161dc-648c-4a44-a563-317b5f5e5... at h14g2000yqd.googlegroups.com> Adeoluwa Odein <stratfordtena... at gmail.com> writes:
    The actual jython/python call is:
    p = [None]
    c.callproc('c2_pkg.RS22', p);
    I used a placeholder initially; now that you have the SQL code, there
    it is. ?It essentially invokes the stored procedure, and it should
    return the OUT variable p, with some value. ?It doesn't have to be a
    cursor fetch; even a minor text assignment.
    That procedure is defined as taking one parameter, but you're passing
    an empty parameter list. ?Why?

    --
    John Gordon ? ? ? ? ? ? ? ? ? A is for Amy, who fell down the stairs
    gor... at panix.com ? ? ? ? ? ? ?B is for Basil, assaulted by bears
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- Edward Gorey, "The Gashlycrumb Tinies"
    I'm new to jython...
  • Related Discussions

    Discussion Navigation
    viewthread | post
    Discussion Overview
    grouppython-list @
    categoriespython
    postedJul 13, '11 at 5:11p
    activeJul 13, '11 at 9:28p
    posts16
    users3
    websitepython.org

    People

    Translate

    site design / logo © 2022 Grokbase