How to execute Stored Procedures with OraOLEDB.Oracle Provider and C#
855947Dec 28 2011 — edited Dec 29 2011I want to get data with stored procedures from oracle database with OraOLEDB.Oracle Provider and fill to a DataTable.Below is SP and C# code.the field named "origseq" in database is number(10).
CREATE OR REPLACE PACKAGE msgs
AS
TYPE torigseq is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
PROCEDURE getmsg
(origseq OUT torigseq);
END msgs;
/
CREATE OR REPLACE PACKAGE BODY msgs
AS
PROCEDURE getmsg
(origseq OUT torigseq)
IS
CURSOR msg_cur IS
SELECT origseq
FROM testtable where origseq=10;
percount NUMBER DEFAULT 1;
BEGIN
FOR singlemsg IN msg_cur
LOOP
origseq(percount) := singlemsg.origseq;
percount := percount + 1;
END LOOP;
END;
END;
/
Execute SP with C#:
//In my project I use OraOLEDB.Oracle.1 privider
string strAccessConn = "Provider=OraOLEDB.Oracle.1;PLSQLRSet=true" +
";User ID=" + this.dicDBConfig["DBUserID"] +
";password=" + this.dicDBConfig["DBPassword"] +
";Data Source=" + this.dicDBConfig["DBDataSource"] +
";Persist Security Info=False";
try
{
OleDbConnection myAccessConn = new OleDbConnection(strAccessConn);
OleDbCommand myAccessCommand = new OleDbCommand("{call msgs.getmsg(?)}", myAccessConn);//call SP
OleDbParameter pout = new OleDbParameter("origseq", OleDbType.Numeric ,10);
pout.Direction = ParameterDirection.Output;
myAccessCommand.Parameters.Add(pout);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
myAccessConn.Open();
DataTable dt = new DataTable();
myDataAdapter.Fill(dt);//here catch a exception
myAccessConn.Close();
this.dataGrid1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(string.Format("{0}", ex.Message), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
myAccessConn.Close();
return false;
}
When execute "myDataAdapter.Fill(dt);", catch an execption as below:
ORA-06550:line 1,column 7:
PLS-00306:wrong number or types of arguments in call to "GETMSG"
ORA-06550:line 1,column 7:
PL/SQL:Statement ignored;
I think there have some mistake in my code, but I do not know how to fix it, Any body know how to fix my code to slove this problem? Thanks for you help.