AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  ora-00911 message for callable statement          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message cormac_f@hotmail.com Guest Posted: Mon Mar 07, 2005 4:46 pm    Post subject: ora-00911 message for callable statement Hi, It would be great if someone could help me with a problem I am having. I am trying to call an Oracle stored procedure using a callable statement in JDBC. However my code fails on the following line and the ORA-00911 error message below is thrown. callableStatement = connection.prepareCall("{call ? := TM_CustomerCareLoginVerify(?,?)}"); Error message: java.sql.SQLException TM_CustomerCareLoginVerify(?,?) [Macromedia][Oracle JDBC Driver][Oracle]ORA-00911: invalid character I cannot see why I am getting an ORA-00911 message on this line. The function, TM_CustomerCareLoginVerify, is in the database I am connecting to. Any ideas why I would be getting this error on the prepareCall line? Thanks, Cormac Back to top Holger Samoht Guest Posted: Mon Mar 07, 2005 5:01 pm    Post subject: Re: ora-00911 message for callable statement Maybe you should try it without these characters "{}"?! callableStatement = connection.prepareCall("call ? := TM_CustomerCareLoginVerify(?,?)"); <cormac_f (AT) hotmail (DOT) com> schrieb im Newsbeitrag news:1110213971.680542.174990 (AT) o13g2000cwo (DOT) googlegroups.com... Quote: Hi, It would be great if someone could help me with a problem I am having. I am trying to call an Oracle stored procedure using a callable statement in JDBC. However my code fails on the following line and the ORA-00911 error message below is thrown. callableStatement = connection.prepareCall("{call ? := TM_CustomerCareLoginVerify(?,?)}"); Error message: java.sql.SQLException TM_CustomerCareLoginVerify(?,?) [Macromedia][Oracle JDBC Driver][Oracle]ORA-00911: invalid character I cannot see why I am getting an ORA-00911 message on this line. The function, TM_CustomerCareLoginVerify, is in the database I am connecting to. Any ideas why I would be getting this error on the prepareCall line? Thanks, Cormac Back to top Holger Samoht Guest Posted: Mon Mar 07, 2005 5:03 pm    Post subject: Re: ora-00911 message for callable statement Maybe this will also help. http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/oracle10g/namedparam/UsingNamedParameters.java.html <cormac_f (AT) hotmail (DOT) com> schrieb im Newsbeitrag news:1110213971.680542.174990 (AT) o13g2000cwo (DOT) googlegroups.com... Quote: Hi, It would be great if someone could help me with a problem I am having. I am trying to call an Oracle stored procedure using a callable statement in JDBC. However my code fails on the following line and the ORA-00911 error message below is thrown. callableStatement = connection.prepareCall("{call ? := TM_CustomerCareLoginVerify(?,?)}"); Error message: java.sql.SQLException TM_CustomerCareLoginVerify(?,?) [Macromedia][Oracle JDBC Driver][Oracle]ORA-00911: invalid character I cannot see why I am getting an ORA-00911 message on this line. The function, TM_CustomerCareLoginVerify, is in the database I am connecting to. Any ideas why I would be getting this error on the prepareCall line? Thanks, Cormac Back to top Lee Fesperman Guest Posted: Mon Mar 07, 2005 8:14 pm    Post subject: Re: ora-00911 message for callable statement [email]cormac_f (AT) hotmail (DOT) com[/email] wrote: Quote: Hi, It would be great if someone could help me with a problem I am having. I am trying to call an Oracle stored procedure using a callable statement in JDBC. However my code fails on the following line and the ORA-00911 error message below is thrown. callableStatement = connection.prepareCall("{call ? := TM_CustomerCareLoginVerify(?,?)}"); Error message: java.sql.SQLException TM_CustomerCareLoginVerify(?,?) [Macromedia][Oracle JDBC Driver][Oracle]ORA-00911: invalid character I cannot see why I am getting an ORA-00911 message on this line. The function, TM_CustomerCareLoginVerify, is in the database I am connecting to. Any ideas why I would be getting this error on the prepareCall line? Drop the colon ( before the equals sign. -- Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) Back to top Lee Fesperman Guest Posted: Tue Mar 08, 2005 9:32 am    Post subject: Re: ora-00911 message for callable statement Lee Fesperman wrote: Quote: [email]cormac_f (AT) hotmail (DOT) com[/email] wrote: Hi, It would be great if someone could help me with a problem I am having. I am trying to call an Oracle stored procedure using a callable statement in JDBC. However my code fails on the following line and the ORA-00911 error message below is thrown. callableStatement = connection.prepareCall("{call ? := TM_CustomerCareLoginVerify(?,?)}"); Error message: java.sql.SQLException TM_CustomerCareLoginVerify(?,?) [Macromedia][Oracle JDBC Driver][Oracle]ORA-00911: invalid character I cannot see why I am getting an ORA-00911 message on this line. The function, TM_CustomerCareLoginVerify, is in the database I am connecting to. Any ideas why I would be getting this error on the prepareCall line? Drop the colon ( before the equals sign. My apologies! The correct syntax for the call is: {? = call TM_CustomerCareLoginVerify(?,?)} Still no colon, though ;^) -- Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) Back to top cormac_f@hotmail.com Guest Posted: Tue Mar 08, 2005 9:42 am    Post subject: Re: ora-00911 message for callable statement Thanks guys, I got past the point I was stuck at by changing the sql to "? = call TM_CustomerCareLoginVerify(?,?­)". This resolves the exception and it runs. The way it was should work tho...it works on the production server - maybe there are differences in the JDBC drivers on my machine. Anyway now I have another problem with another query (aint easy is it!!): callableStatement = connection.prepareCall("{? = call TM_GetGroupSizes(?)}"); callableStatement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); This query throws the following error: : TM_GetGroupSizes(?) [Macromedia][Oracle JDBC Driver]The requested output parameter data is not available. The code for stored procedure is: CREATE OR REPLACE FUNCTION TM_GetGroupSizes( p_userid IN NUMBER DEFAULT NULL) RETURN TYPES.ref_cursor AS RC1 TYPES.ref_cursor; BEGIN OPEN RC1 FOR SELECT groupid, ( SELECT COUNT(groupid) FROM groupcontacts WHERE groupid = g.groupid ) "size" FROM TM_GroupsIncSharedView g WHERE userid = p_userid; RETURN RC1; END TM_GetGroupSizes; / Back to top Chuck Simpson Guest Posted: Tue Mar 08, 2005 4:23 pm    Post subject: Re: ora-00911 message for callable statement On Tue, 08 Mar 2005 01:42:42 -0800, cormac_f wrote: Quote: Thanks guys, I got past the point I was stuck at by changing the sql to "? = call TM_CustomerCareLoginVerify(?,?­)". This resolves the exception and it runs. The way it was should work tho...it works on the production server - maybe there are differences in the JDBC drivers on my machine. Anyway now I have another problem with another query (aint easy is it!!): callableStatement = connection.prepareCall("{? = call TM_GetGroupSizes(?)}"); callableStatement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); This query throws the following error: : TM_GetGroupSizes(?) [Macromedia][Oracle JDBC Driver]The requested output parameter data is not available. The code for stored procedure is: CREATE OR REPLACE FUNCTION TM_GetGroupSizes( p_userid IN NUMBER DEFAULT NULL) RETURN TYPES.ref_cursor AS RC1 TYPES.ref_cursor; BEGIN OPEN RC1 FOR SELECT groupid, ( SELECT COUNT(groupid) FROM groupcontacts WHERE groupid = g.groupid ) "size" FROM TM_GroupsIncSharedView g WHERE userid = p_userid; RETURN RC1; END TM_GetGroupSizes; / Try registering the return value as an OBJECT and cast it to an Oracle CURSOR object when you read it. e.g. callableStatement.registerOutParameter(1, Types.OBJECT); .... CURSOR cursor = (CURSOR) callableStatement.readObject(1); Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.