JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » JDBC/SQL討論區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 請教使用CallableStatement call oracle store Function [精華]
lovex





發文: 153
積分: 0
於 2003-12-05 10:53 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
各位好:

小弟使用以下方法去call oracle 的 store function

1
2
3
4
5
6
7
8
private CallableStatement SPCall=null;
private Connection DB_Conn = null;
private strFlowCode="test";
 
SPCall = DB_Conn.prepareCall("?={call KM_getTypePath(?)}");
SPCall.registerOutParameter(1,java.sql.Types.VARCHAR);
SPCall.setString(2, strFlowCode);
SPCall.executeUpdate();

但每次執行到 executeUpdate() method時就出現 nullpoint exception,而若把store function changed to store procedure,然後把 return value放在第二個引數,就可以成功return,修改後如下:

1
2
3
4
5
6
7
8
9
10
private CallableStatement SPCall=null;
private Connection DB_Conn = null;
private strFlowCode="test";
 
SPCall = DB_Conn.prepareCall("{call sp_KM_getTypePath(?,?)}");
SPCall.setString(1, strFlowCode);
SPCall.registerOutParameter(2,java.sql.Types.VARCHAR);
SPCall.executeUpdate();
 
Strint strReturnValue=SPCall.getString(2);


請教各位,以oracle store function的方式回傳,為何總是會有Exception呢?

望各位能不吝指教!!謝謝!!


browser edited on 2003-12-05 13:06
reply to postreply to post
作者 Re:請教使用CallableStatement call oracle store Function [Re:lovex]
lovex





發文: 153
積分: 0
於 2003-12-05 13:47 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
補充一下....

oracle store function return value 是一個 cursor...

但試了幾個比較有可能的java.sql.types.XXXX都是不行~~

要嘛nullpointer or 型態衝突 or 型態錯誤....

ps.謝謝幫小弟程式作成區塊的先進啊!!(不好意思,不懂怎麼把程式block起來:p)


reply to postreply to post
作者 Re:請教使用CallableStatement call oracle store Function [Re:lovex]
lovex





發文: 153
積分: 0
於 2003-12-05 17:39 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
找到方法了....

搞了半天,原來JDBC沒有支援 cursor variant........

修改程式如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import oracle.jdbc.driver.*;
 
DB_Conn = (new DBBean()).getConnection();
//if haven’t result parameter can use this syntax:
//SPCall = DB_Conn.prepareCall("{call fc_getflownum( ? )}");
SPCall = DB_Conn.prepareCall("{?=call fc_getflownum( ? )}");
            SPCall.registerOutParameter(1,OracleTypes.CURSOR);
SPCall.setString(2, strFlowCode);
SPCall.execute();
 
rs=((OracleCallableStatement)SPCall).getCursor(1);
 
rs.next();
strResult = new String(rs.getString(1));


注意import and casting....


browser edited on 2003-12-05 19:03
reply to postreply to post
作者 Re:請教使用CallableStatement call oracle store Function [Re:lovex]
annyang0921





發文: 1
積分: 0
於 2005-12-06 16:30 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
請問JDBC沒有支援 cursor variant是什麼意思?
表示如果store procedure 中不管是輸出或是輸入變數都不可以是types.cursorType 是嗎?


reply to postreply to post
作者 Re:請教使用CallableStatement call oracle store Function [Re:annyang0921]
lovex





發文: 153
積分: 0
於 2005-12-18 11:39 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
annyang0921 wrote:
請問JDBC沒有支援 cursor variant是什麼意思?
表示如果store procedure 中不管是輸出或是輸入變數都不可以是types.cursorType 是嗎?


是的!!除非改用Oracle 自己提供的JDBC variant type...


reply to postreply to post
» JWorld@TW »  JDBC/SQL討論區

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8