| exception thrown when executing cmd.ExecuteNonQuery(); [message #579744] |
Thu, 14 March 2013 15:46  |
 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
