Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
How to execute Oracle stored procedure which has Input and Output parameter as CommandText in C#.net

Dear Team,
I have a oracle stored procedure as below. This Oracle stored procedure accepts Input parameter and output parameters.
create or replace NONEDITIONABLE PROCEDURE GetDetailsWithOutputParams_Multiple(id IN INTEGER,valuepath OUT VARCHAR2,valueNumeric OUT NUMBER)
AS
BEGIN
for c in (SELECT k.VALUEPATH as VALUEPATH,k.VALUENUMERIC as VALUENUMERIC INTO valuepath,valueNumeric FROM KPI k where k.ID=id)
loop
valuepath:=c.VALUEPATH;
valueNumeric:=c.VALUENUMERIC;
end loop;
END;
Now I am calling this procedure using C#.net as below:
using (OracleConnection conn = new OracleConnection(_connectionString))
{
await conn.OpenAsync();
string str = @"DECLARE ID NUMBER;VALUEPATH VARCHAR2(200);VALUENUMERIC NUMBER;BEGIN GETDETAILSWITHOUTPUTPARAMS_MULTIPLE(ID, VALUEPATH, VALUENUMERIC); :VALUEPATH := VALUEPATH; :VALUENUMERIC := VALUENUMERIC;END;";
OracleCommand objCmd = new OracleCommand(str, conn);
objCmd.CommandType = CommandType.Text;
OracleParameter parameter = null;
parameter = new OracleParameter("ID", OracleDbType.Decimal, ParameterDirection.Input);
parameter.Value = 2;
objCmd.Parameters.Add(parameter);
parameter = new OracleParameter("VALUEPATH", OracleDbType.Varchar2, ParameterDirection.Output);
parameter.Size = 200;
objCmd.Parameters.Add(parameter);
parameter = new OracleParameter("VALUENUMERIC", OracleDbType.Decimal, ParameterDirection.Output);
objCmd.Parameters.Add(parameter);
string str1, str2;
using (OracleDataReader objReader = objCmd.ExecuteReader())
{
str1 = objCmd.Parameters["VALUEPATH"].Value.ToString();
str2 = objCmd.Parameters["VALUENUMERIC"].Value.ToString();
objReader.Close();
}
}
Getting Error as below:
Please advise, how to execute oracle stored procedure as a command text .
Answers
-
Hi,
You should executenosql instead of reader, and you also don't need the entire 'DECLARE....' block.
As a side comment, if you plan to get only one row of data in the stored procedure you should use 'select ... into ...' instead of a loop. There are some large discussions about that, but it is assumed better practice :)
Here is a little example I've found for you.
using (OracleConnection connection = new OracleConnection("ConnectionString")) using (OracleCommand command = new OracleCommand("ProcName", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("ParameterName", OracleDbType.Varchar2).Value = "Your Data Here"; command.Parameters.Add("SomeOutVar", OracleDbType.Varchar2, 120); command.Parameters["return_out"].Direction = ParameterDirection.Output; command.Parameters.Add("SomeOutVar1", OracleDbType.Varchar2, 120); command.Parameters["return_out2"].Direction = ParameterDirection.Output; connection.Open(); command.ExecuteNonQuery(); string SomeOutVar = command.Parameters["SomeOutVar"].Value.ToString(); string SomeOutVar1 = command.Parameters["SomeOutVar1"].Value.ToString(); }
Regards,
Hadar
-
Hello @Hpaiss,
Your code is very much useful.
Just one point to check with you is that, in your code block last lines your reading output data through output parameters.So my question, if output parameter is "refcursor" type then how to read the output data?
I have tried below format. But looks this always returns the empty/null ouput.
Var SomeOut = command.Parameters["refcursorParam"].Value;
-
Hi,
Here is an example for handling refcursor:
using (OracleConnection cn = new OracleConnection(DatabaseHelper.GetConnectionString())) { OracleDataAdapter da = new OracleDataAdapter(); OracleCommand cmd = new OracleCommand(); cmd.Connection = cn; cmd.InitialLONGFetchSize = 1000; cmd.CommandText = DatabaseHelper.GetDBOwner() + "PKG_COLLECTION.CSP_COLLECTION_HDR_SELECT"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("PUNIT", OracleDbType.Char).Value = unit; cmd.Parameters.Add("POFFICE", OracleDbType.Char).Value = office; cmd.Parameters.Add("PRECEIPT_NBR", OracleDbType.Int32).Value = receiptno; cmd.Parameters.Add("T_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output; da.SelectCommand = cmd; DataTable dt = new DataTable(); da.Fill(dt); return dt; }
Regards,
Hadar