473,231 Members | 1,745 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

HOWTO : call stored procedure with result function and input/ouput parameters and Oracle database

Here a sample to call a stored procedure with an Oracle Database.

odc.Connection = m_cDb ' use an open connection to your database
odc.CommandType = CommandType.StoredProcedure
odc.CommandText = "pkg_func.GetReqStatus" ' just set the name of the
function, don't used syntax like {? = call
pkg_func.GetReqStatus(?,?,?)}

' the return parameter
odp = New OleDbParameter("result", OleDbType.VarChar)
odp.Direction = ParameterDirection.ReturnValue
odp.Size = 255 ' don't forget the size or you will got an error
odc.Parameters.Add(odp)

' the input parameter
odp = New OleDbParameter("req_id", OleDbType.Integer)
odp.Direction = ParameterDirection.Input
odp.Value = 1024
odp.Size = 10
odc.Parameters.Add(odp)

' the first output parameter
odp = New OleDbParameter("status", OleDbType.VarChar)
odp.Direction = ParameterDirection.Output
odp.Size = 255
odc.Parameters.Add(odp)

' the second output parameter
odp = New OleDbParameter("data", OleDbType.VarChar)
odp.Direction = ParameterDirection.Output
odp.Size = 1024
odc.Parameters.Add(odp)

odc.ExecuteNonQuery()

' now get return values
Dim sResult As String = CType(odc.Parameters("result").Value(),
String)
Dim sStatus As String = CType(odc.Parameters("status").Value(),
String)
Dim sData As String = CType(odc.Parameters("data").Value(), String)

I use the Oracle Client v10.2 and the 'Oracle Provider for OLE DB'
installed with the client.

May 24 '07 #1
0 3778

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: William Buchanan | last post by:
Hi I have the following stored proc in interbase (which might contain errors - i'm doing it off the top of my head), which I would like to convert into oracle. Can you help? What I want back is...
6
by: Eugene A | last post by:
Hello. In this call from a Java prog to a Oracle SP I am trying to pass along some varchars and a cursor, but apperently the syntax of the call to the stor proc is incorrect. What am I doing...
0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
1
by: John Sidney-Woollett | last post by:
Is it possible to call a Postgres stored function (as if it were a procedure), using a PreparedStatement which will receive no return value, instead of having to use a CallableStatement and ignore...
12
by: Newbie | last post by:
how can i call an oracle function to get data without using a select statement or stored procedures? given a project_no, i need to call the function: ops$sqltime.pa_new_job_no_fn which will...
6
by: Ian Boyd | last post by:
Every time during development we had to make table changes, we use Control Center. Most of the time, Control Center fails. If you try to "undo all", it doesn't, and you end up losing your identity...
14
by: jehugaleahsa | last post by:
Hello: I am working with Oracle .NET Stored Procedures. I would like to know how to return the results of a SELECT statement. I have tried returning a OracleRefCursor and a DataTable, but...
2
by: E11esar | last post by:
Hello there. I am going in bit of a circle with this matter; First some background: I am trying to upload the details of a CSV file into an Oracle table. I am using a StreamReader to copy a line...
5
by: kaushal30 | last post by:
I am getting this error when I call a stored procedure from my C# code. It is a simple stored procedure with six params that inserts data : PROCEDURE LHD_SUR_ADMNEXP_HDR_INS ( ...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.