Problem with IN parameters to an Oracle stored procedure

Posted: 11/6/2007 10:43:00 PM

java-programmer, Problem with IN parameters to an Oracle stored procedure Hello all

I had earlier posted the same problem I am having on
comp.java.lang.programmer earlier and someone suggested this post
would probably fall better under this group. Here is the link to the
original post with the original post copy pasted below.

http://groups.google.co.in/group/comp.lang.java.programmer/browse_thread/thread/b0b845981c3e403a/#

Since then I have changed the IN parameter to my stored procedure to
read as

clID IN TABLEA.CLIENTID%TYPE

instead of clientID, based on a suggestion to the earlier post and
have still had no luck.

Any suggestions are welcome.

Thanks
Swetha

Posted: 11/7/2007 10:04:00 PM

java-programmer >> Problem with IN parameters to an Oracle stored procedure On Nov 6, 6:43 am, swetha <email***@***.com> wrote:
> Hello all
>
> I had earlier posted the same problem I am having on
> comp.java.lang.programmer earlier and someone suggested this post
> would probably fall better under this group. Here is the link to the
> original post with the original post copy pasted below.
>
> http://groups.google.co.in/group/comp.lang.java.programmer/browse_thr...
>
> Since then I have changed the IN parameter to my stored procedure to
> read as
>
> clID IN TABLEA.CLIENTID%TYPE
>
> instead of clientID, based on a suggestion to the earlier post and
> have still had no luck.
>
> Any suggestions are welcome.
>
> Thanks
> Swetha

what happens if you change the procedure definition to have all
the parameters defined as NUMBER, or NUMBER (20,0)?
Joe Weinstein at BEA Systems

Posted: 11/8/2007 4:35:00 PM

java-programmer >> Problem with IN parameters to an Oracle stored procedure On Nov 7, 4:03 pm, "email***@***.com" <email***@***.com>
wrote:

> what happens if you change the procedure definition to have all
> the parameters defined as NUMBER

Tried that now. No luck. Still the same.

> , or NUMBER (20,0)?

What do you mean by that?
clientID IN NUMBER (20,0)
gives me a compile error.

Posted: 11/9/2007 12:09:00 AM

java-programmer >> Problem with IN parameters to an Oracle stored procedure On Nov 8, 12:34 am, swetha <email***@***.com> wrote:
> On Nov 7, 4:03 pm, "email***@***.com" <email***@***.com>
> wrote:
>
> > what happens if you change the procedure definition to have all
> > the parameters defined as NUMBER
>
> Tried that now. No luck. Still the same.
>
> > , or NUMBER (20,0)?
>
> What do you mean by that?
> clientID IN NUMBER (20,0)
> gives me a compile error.

nevermind. So if you do this:

CallableStatement cs = conn.prepareCall("{call InsertValue(?, 1, 1,
235234)}");
cs.registerOutParameter(1, Types.INTEGER);
boolean result = cs.execute();
int ID = cs.getInt(1);

does it work?

I would make a simple standalone program that creates it's own
table, inserts a row or two, and creates a procedure with the
same signature as yours, and then duplicates the problem. That
would be perfect for Oracle support.
Joe

Posted: 11/10/2007 6:35:00 PM

java-programmer >> Problem with IN parameters to an Oracle stored procedure On Nov 8, 6:08 pm, "email***@***.com" <email***@***.com>
wrote:
> So if you do this:
>
> CallableStatement cs = conn.prepareCall("{call InsertValue(?, 1, 1,
> 235234)}");
> cs.registerOutParameter(1, Types.INTEGER);
> boolean result = cs.execute();
> int ID = cs.getInt(1);
>
> does it work?

Yes this works and I get a valid ID returned.

> I would make a simple standalone program that creates it's own
> table, inserts a row or two, and creates a procedure with the
> same signature as yours, and then duplicates the problem. That
> would be perfect for Oracle support.

I'll try doing that and send it to Oracle. Thanks.

Swetha