Calling Oracle Stored Functions
-
Does somebody knows how to call Oracle Stored Functions in VB Net, I am very very tired trying one time and once again, and so on. Please is urgent!! Thanks in advanced.:confused:
-
Does somebody knows how to call Oracle Stored Functions in VB Net, I am very very tired trying one time and once again, and so on. Please is urgent!! Thanks in advanced.:confused:
Use the OleSqlwhateveritis generic class provided by ADO.NET for all sources except SqlServer. You aren't saying what you've tried, or what is happening, but my memory of Oracle ( which I am trying to erase, Oracle is crap ), was that the issue was finding a driver that worked properly. May you have driver issues ? Christian Graus - Microsoft MVP - C++
-
Use the OleSqlwhateveritis generic class provided by ADO.NET for all sources except SqlServer. You aren't saying what you've tried, or what is happening, but my memory of Oracle ( which I am trying to erase, Oracle is crap ), was that the issue was finding a driver that worked properly. May you have driver issues ? Christian Graus - Microsoft MVP - C++
Hi Christian, I think is not an issue with drivers becouse I can call Stored Procedures fine, however I can't call Functions which are stored in Oracle Packages. I am using the next code in VB Net: Public Sub MY_PKG_ADD_NEW_ITEM(ByVal ConnectionString As String _ , ByRef N_ITEM_ID_O As Integer _ , ByVal V_ITEM As String) Dim oConn As New OracleConnection(ConnectionString) Dim oraCmd As New OracleCommand("", oConn) oraCmd.Connection = oConn oConn.Open() oraCmd.CommandText = "MY_PKG.ADD_NEW_ITEM" oraCmd.CommandType = CommandType.StoredProcedure 'oraCmd.Parameters.Add(New OracleParameter("N_ITEM_ID_O", OracleType.Number, 3)).Direction = ParameterDirection.Output 'oraCmd.Parameters.Add(New OracleParameter("V_ITEM", OracleType.VarChar, 2000)).Value = V_ITEM 'oraCmd.Parameters(1).Direction = ParameterDirection.Input Try oraCmd.ExecuteNonQuery() N_ITEM_ID_O = oraCmd.Parameters("N_ITEM_ID_O").Value Catch 'MsgBox(Err.Description) Finally oraCmd.Dispose() oConn.Close() End Try End Sub The error what I am receiving is: ORA-06550: line 1, column 7: PLS-00221: 'ADD_NEW_ITEM' is not a procedure or is not defined ORA-06550: line 1, column 7: PL/SQL: Statement ignored The software I am using is: VB Net 2002 version Oracle driver is System.Data.OracleClient (.NET Framework Data Provider for Oracle) I am getting conected to an Oracle 9i data source. I hope this info complete my scenario so to get more help from you. Thanks in advanced !!:omg:
-
Hi Christian, I think is not an issue with drivers becouse I can call Stored Procedures fine, however I can't call Functions which are stored in Oracle Packages. I am using the next code in VB Net: Public Sub MY_PKG_ADD_NEW_ITEM(ByVal ConnectionString As String _ , ByRef N_ITEM_ID_O As Integer _ , ByVal V_ITEM As String) Dim oConn As New OracleConnection(ConnectionString) Dim oraCmd As New OracleCommand("", oConn) oraCmd.Connection = oConn oConn.Open() oraCmd.CommandText = "MY_PKG.ADD_NEW_ITEM" oraCmd.CommandType = CommandType.StoredProcedure 'oraCmd.Parameters.Add(New OracleParameter("N_ITEM_ID_O", OracleType.Number, 3)).Direction = ParameterDirection.Output 'oraCmd.Parameters.Add(New OracleParameter("V_ITEM", OracleType.VarChar, 2000)).Value = V_ITEM 'oraCmd.Parameters(1).Direction = ParameterDirection.Input Try oraCmd.ExecuteNonQuery() N_ITEM_ID_O = oraCmd.Parameters("N_ITEM_ID_O").Value Catch 'MsgBox(Err.Description) Finally oraCmd.Dispose() oConn.Close() End Try End Sub The error what I am receiving is: ORA-06550: line 1, column 7: PLS-00221: 'ADD_NEW_ITEM' is not a procedure or is not defined ORA-06550: line 1, column 7: PL/SQL: Statement ignored The software I am using is: VB Net 2002 version Oracle driver is System.Data.OracleClient (.NET Framework Data Provider for Oracle) I am getting conected to an Oracle 9i data source. I hope this info complete my scenario so to get more help from you. Thanks in advanced !!:omg:
you need to set a returnValue parameter (where the function result will be stored) and execute as "ExecuteNonQuery" Ishak
-
you need to set a returnValue parameter (where the function result will be stored) and execute as "ExecuteNonQuery" Ishak
Here is a differente way to prepare the calling to the stored function: Public Sub ADD_NEW_ITEM(ByVal ConnectionString As String _ , ByRef ITEM_ID_O As Integer _ , ByVal V_ITEM As String) Dim oConn As New OracleClient.OracleConnection(ConnectionString) Dim rtnVal As New OracleClient.OracleParameter("", Conn) Dim inpVal As New OracleClient.OracleParameter("", Conn) Dim outVal As New OracleClient.OracleParameter("", Conn) Dim oraCmd As New OracleClient.OracleCommand("MY_PCKG.ADD_NEW_ITEM", oConn) oraCmd.CommandType = CommandType.StoredProcedure outVal = oraCmd.CreateParameter outVal.ParameterName = "ITEM_ID_O" outVal.OracleType = OracleType.Number outVal.Direction = ParameterDirection.Output outVal.Value = DBNull.Value oraCmd.Parameters.Add(outVal) inpVal = oraCmd.CreateParameter inpVal.ParameterName = "V_ITEM" inpVal.OracleType = OracleType.VarChar inpVal.Size = 2000 inpVal.Direction = ParameterDirection.Input inpVal.Value = V_ITEM oraCmd.Parameters.Add(inpVal) Try oConn.Open() rtnVal.Direction = ParameterDirection.ReturnValue rtnVal.OracleType = OracleType.Number rtnVal.ParameterName = "ITEM_ID" rtnVal.Value = oraCmd.ExecuteNonQuery oraCmd.ExecuteNonQuery() Catch MsgBox(Err.Description) Finally oraCmd.Dispose() oConn.Close() End Try End Sub So I get the same results. Thanks for your answer, do you have some other idea:omg:?