Home » Infrastructure » Windows » exception thrown when executing cmd.ExecuteNonQuery(); (PL/sql dEVOPER VER 9, oACLE 11G, c3 2010)
exception thrown when executing cmd.ExecuteNonQuery(); [message #579744] Thu, 14 March 2013 15:46 Go to next message
stevenandler
Messages: 3
Registered: March 2013
Location: New York
Junior Member
When My C# program executes the cmd.ExecuteNonQuery(), the following exception is thrown:

[System.Data.OracleClient.OracleException] = {"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'VALIDATE_PATIENT_NEW'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"}


Here is my Stored Procedure:

CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW
(
P_LAST_NAME IN ogen.gen_m_patient_mast.last_name%TYPE,
P_FIRST_NAME IN ogen.gen_m_patient_mast.first_name%TYPE,
P_DOB IN OGEN.GEN_M_PATIENT_MAST.BIRTH_DATE%TYPE, -- timestamp DEFAULT NULL,
c_dbuser OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN c_dbuser FOR
SELECT PAT_NUMBER,FACILITY_KEY
FROM OGEN.GEN_M_PATIENT_MAST
WHERE LAST_NAME = P_LAST_NAME AND FIRST_NAME = P_FIRST_NAME AND BIRTH_DATE = P_DOB;
END;

Here is my C# code:
OracleConnection conn = new OracleConnection("Data Source=SRVORDERS;User ID=OGEN;Password=xxxxxx;Unicode=True;");
try
{
OracleCommand cmd = new OracleCommand("OGEN.VALIDATE_PATIENT_NEW", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("LAST_NAME", LAST_NAME);
cmd.Parameters.AddWithValue("FIRST_NAME", FIRST_NAME);
cmd.Parameters.AddWithValue("DOB", DOB);
cmd.Parameters.Add("c_dbuser", OracleType.Cursor);
cmd.Parameters["c_dbuser"].Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery(); //throws exception here
patnumber = Convert.ToInt32(cmd.Parameters["c_dbuser"].Value);
}
catch (Exception e)
{
conn.Close();
using (StreamWriter sw = File.AppendText(log))
{
sw.WriteLine(System.DateTime.Now + " OGEN.VALIDATE_PATIENT_NEW " + Success + " PATNUM= " + patnumber + " Order No " + orderno);
}
}
}


I suspect the error is caused by the Cursor parameter. So any sameple showing how to read the Cursor data will
be very helpful.


Re: exception thrown when executing cmd.ExecuteNonQuery(); [message #579749 is a reply to message #579744] Thu, 14 March 2013 16:38 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I suspect the error is caused by the Cursor parameter. So any sameple showing how to read the Cursor data will
Then why not modify things to eliminate the cursor; just to validate that other 3 parameters work as expected?

http://www.lmgtfy.com/?q=oracle+C#+sysrefcursor
Previous Topic: DBCA hung at 2%..
Next Topic: Oracle 10g Installation
Goto Forum:
  


Current Time: Thu Mar 28 07:38:01 CDT 2024