File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes sql question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql question" Watch "sql question" New topic
Author

sql question

jim li
Ranch Hand

Joined: May 20, 2008
Posts: 180
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

By calling "exec [your procedure name]"


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
jim li
Ranch Hand

Joined: May 20, 2008
Posts: 180
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
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

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(?, ?, ?)}");


OCUP UML fundamental and ITIL foundation
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql question