sql question

[jim li]

hi

i have a pl sql function which allows me to insert a new row to table. how can i call this pl sql function in the sql ?


thank you

[Paul Sturrock]

By calling "exec [your procedure name]"

[jim li]

PreparedStatement stmt = tr.createPreparedStatement(""DBTransaction.DEFAULT);
stmt.execute( "f_valid_agree_accept(1, \"856456\", '0')" );

i got the error: java.sql.SQLException: ORA-00900: invalid SQL statement

09/08/05 09:08:30 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
09/08/05 09:08:30 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
09/08/05 09:08:30 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
09/08/05 09:08:30 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
09/08/05 09:08:30 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
09/08/05 09:08:30 at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
09/08/05 09:08:30 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1161)
09/08/05 09:08:30 at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1654)
09/08/05 09:08:30 at oracle.oc4j.sql.proxy.StatementBCELProxy.execute(StatementBCELProxy.java:301)
09/08/05 09:08:30 at ca.bluecross.ab.tao.controller.loginfilter.TermsOfUseFilter.updateTOU(TermsOfUseFilter.java:120)
09/08/05 09:08:30 at ca.bluecross.ab.tao.controller.loginfilter.TermsOfUseFilter.doFilter(TermsOfUseFilter.java:74)
09/08/05 09:08:30 at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
09/08/05 09:08:30 at oracle.adf.model.servlet.ADFBindingFilter.doFilter(ADFBindingFilter.java:162)
09/08/05 09:08:30 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:620)
09/08/05 09:08:30 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:369)
09/08/05 09:08:30 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:865)
09/08/05 09:08:30 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:447)
09/08/05 09:08:30 at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:215)
09/08/05 09:08:30 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:117)
09/08/05 09:08:30 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:110)
09/08/05 09:08:30 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
09/08/05 09:08:30 at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
09/08/05 09:08:30 at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
09/08/05 09:08:30 at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
09/08/05 09:08:30 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:298)
09/08/05 09:08:30 at java.lang.Thread.run(Thread.java:595)

[Jan Cumps]

here is an example.
Use a java.sql.CallableStatement, and use the proper sql syntax

in your case:
CallableStatement cstmt = conn.prepareCall("{call f_valid_agree_accept(?, ?, ?)}");