Problem with IN parameters to an Oracle stored procedure

Thread view:

swetha - 06 Nov 2007 14:43 GMT

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

Reply to this Message

joeNOSPAM@BEA.com - 07 Nov 2007 14:03 GMT

> Hello all
>
[quoted text clipped - 17 lines]
> 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

Reply to this Message

swetha - 08 Nov 2007 08:34 GMT

On Nov 7, 4:03 pm, "joeNOS...@BEA.com" <joe.weinst...@gmail.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.

Reply to this Message

joeNOSPAM@BEA.com - 08 Nov 2007 16:08 GMT

> On Nov 7, 4:03 pm, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
> wrote:
[quoted text clipped - 9 lines]
> 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

Reply to this Message

swetha - 10 Nov 2007 10:35 GMT

On Nov 8, 6:08 pm, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
wrote:

> So if you do this:
>
[quoted text clipped - 5 lines]
>
> 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

Reply to this Message

Problem with IN parameters to an Oracle stored procedure

Free Magazines