SQL Exception while connecting to oracle 11g from web application using jdbc
I am getting SQL Exception.
The following is my code,
public DBConnect()
{
try {
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// connect through driver
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","system123");
// conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE","system","system123");
// create a statement object
CallableStatement cstmt = null;
String auth;
try {
cstmt = conn.prepareCall("{? = call login(?,?)}");
cstmt.registerOutParameter(1, java.sql.Types.BOOLEAN);
cstmt.setString(2, "aniket");
cstmt.setString(3, "aniket");
cstmt.execute();
auth = cstmt.getString(1);
cstmt.close();
System.out.println(auth);
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException ex) {
Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
}
catch(Exception e)
{
//system.out.println(e.printStackTrace());
}
}
And the following is the stacktrace -
java.sql.SQLException: ORA-01403: no data found
ORA-06512: at "SYSTEM.LOGIN", line 10 ORA-06512: at line 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:218)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:971)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1192)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3521)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4612)
at com.me.db.DBConnect.<init>(DBConnect.java:57)
at com.me.controller.PreLoginController.handleRequestInternal(PreLoginController.java:25)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:203)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:558)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:259)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:281)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
The following is my PL/SQL function login -
CREATE OR REPLACE FUNCTION login(p_uname IN varchar2, p_pword
IN varchar2) RETURN String AS
v_uname varchar2(30); v_pword varchar2(30);
BEGIN
SELECT uname, pword INTO v_uname, v_pword
FROM user_account
WHERE uname = p_uname AND pword = p_pword;
IF (v_uname IS NULL OR v_pword IS NULL) THEN
return 'Failed';
ELSE
return 'Success';
END IF;
END login; /
You have a mis-match between the parameter types as declared by the login PL/SQL function, and the types you're setting in the Java. The boolean seems like the most likely candidate. Make sure the return type of login is really compatible with java booleans.
As mentioned by skaffman, the Oracle JDBC driver doesn't support PL/SQL boolean type. So I changed the return type to varchar. One more critical thing missing in my PL/SQL code was the exception handling in case of 0 rows returned ie NO_DATA_FOUND
Added that exception in the PL/SQL function login and changed the location of Returning 'False' to exception section as IF NO_DATA_FOUND THEN Return 'False';
链接地址: http://www.djcxy.com/p/36970.html上一篇: 标准与JPQL或HQL