熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> Java核心技術 >> 正文

Enterprise Library-Data Block oracle

2013-11-23 18:40:49  來源: Java核心技術 

  昨天使用 Data Block 操作 oracle 返回 cursor 期間產生了一點問題很是郁悶找了一下午也沒有解決早上睡不著起來繼續找結果找到了解決的方法其實也是怪自己沒有很好的看文檔在此記錄一下以使別的同志再出現我的問題的時候很容易的找到解決的方法
  
  問題是這樣的
  
  我在oracle裡面有這樣一個過程
  
  PROCEDURE ListAllStatic_Users (cur_Static_User OUT T_CURSOR)
  IS
  BEGIN
  OPEN cur_Static_User FOR
  SELECT * FROM Static_User ;
  END ListAllStatic_Users;
  
  我在程序裡面如下調用
  
  Database db = DatabaseFactoryCreateDatabase(oraserver);
  string sqlCommand = Static_UserPackageListAllStatic_Users;
  DBCommandWrapper dbCommandWrapper =dbGetStoredProcCommandWrapper(sqlCommand);
  DataSet dsCustomers = dbExecuteDataSet(dbCommandWrapper);
  DataGridDataSource=dsCstomers;
  DataGridDataBind();
  
  結果出現如下問題
  
  ORA: 第 列: PLS: 調用 LISTALLSTATIC_USERS 時參數個數或類型錯誤 ORA: 第 列: PL/SQL: Statement ignored
  
  說明: 執行當前 Web 請求期間出現未處理的異常請檢查堆棧跟蹤信息以了解有關該錯誤以及代碼中導致錯誤的出處的詳細信息
  
  異常詳細信息: SystemDataOracleClientOracleException: ORA: 第 列: PLS: 調用 LISTALLSTATIC_USERS 時參數個數或類型錯誤 ORA: 第 列: PL/SQL: Statement ignored
  
  源錯誤:
  
  行
  行 DataSet dsCustomers = dbExecuteDataSet(dbCommandWrapper);行 DataGridDataSource=dsCustomers;
  行 DataGridDataBind();
  
  我以為是我的參數沒有弄對於是就加了一句
  
  dbCommandWrapperAddOutParameter(cur_Static_UserDbTypeObject);
  
  結果還是一樣的後來也試驗了
  
  OracleCommandWrapperAddParameter(stringDbTypeintParameterDirectionboolbytebytestringDataRowVersionobject);
  
  這個方法來添加也是不行
  
  後來就上網找了很長時間也沒有什麼進展今天早上起來還是一籌莫展偶爾的打開Enterprise Library安裝目錄的Enterprise Library Release Notesrtf文件發現裡面有這麼一段
  
   Data Access Application Block: Default Oracle cursor cur_OUT
  
  The managed provider for Oracle requires you to explicitly bind your reference cursor in your parameter collection This means you must explicitly create an output parameter for the cursor in your application code However that code will not be portable with database systems that do not require a parameter for the cursor The OracleDatabase allows you to create commands without specifying a cursor It will create a cursor named cur_OUT for commands that execute a stored procedure and do not include an output parameter for the cursor This means that you can name your reference cursor as cur_OUT and the Data Access Application Block will bind it for you; you do not need to explicitly create an output parameter for the cursor If your stored procedures use a cursor with a name other than cur_OUT you must explicitly add a parameter for each cursor to the command Similarly if your stored procedure contains multiple cursors you must explicitly add each cursor parameter to the command
  
  這下我就明白了在我的oracle函數中我的名字 cur_Static_User 和默認的名字cur_OUT不匹配
  
  於是我就改了我的存儲過程的參數名稱cur_Static_User改為 cur_OUT
  
  問題就解決了
  
  經過試驗也可以用如下方法用自己的參數名而不用默認的參數名也可以在一個PROCEDURE中返回多個 CURSOR
  
  我的存儲過程
  
  Procedure STATIC_USER_SelectAll
  ( cur_OUT_f OUT T_OUT cur_OUT_g OUT T_OUT)
  AS
  Begin
  OPEN cur_OUT_f FOR Select * from STATIC_USER;
  OPEN cur_OUT_g FOR Select * from STATIC_ROLE;
  End;
  
  Procedure STATIC_USER_SelectAll
  ( cur_OUT_f OUT T_OUT cur_OUT_g OUT T_OUT)
  AS
  Begin
  OPEN cur_OUT_f FOR Select * from STATIC_USER;
  OPEN cur_OUT_g FOR Select * from STATIC_ROLE;
  End;
  
  Procedure STATIC_USER_SelectAll
  ( cur_OUT_f OUT T_OUT cur_OUT_g OUT T_OUT)
  AS
  Begin
  OPEN cur_OUT_f FOR Select * from STATIC_USER;
  OPEN cur_OUT_g FOR Select * from STATIC_ROLE;
  End; 代碼如下
  
  Database db = DatabaseFactoryCreateDatabase(oraserver);
  string sqlCommand = Static_UserPackageSTATIC_USER_SelectAll;
  MicrosoftPracticesEnterpriseLibraryDataOracleOracleCommandWrapper dbCommandWrapper =(MicrosoftPracticesEnterpriseLibraryDataOracleOracleCommandWrapper)dbGetStoredProcCommandWrapper(sqlCommand);
  dbCommandWrapperAddParameter(cur_OUT_f OracleTypeCursor ParameterDirectionOutput true StringEmpty DataRowVersionDefault ConvertDBNull);
  dbCommandWrapperAddParameter(cur_OUT_g OracleTypeCursor ParameterDirectionOutput true StringEmpty DataRowVersionDefault ConvertDBNull);
  DataSet dsCustomers = dbExecuteDataSet(dbCommandWrapper);
  DataGridDataSource=dsCustomersTables[];
  DataGridDataBind();
  DataGridDataSource=dsCustomersTables[];
  DataGridDataBind();
From:http://tw.wingwit.com/Article/program/Java/hx/201311/25559.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.