Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to execute Stored Procedures with OraOLEDB.Oracle Provider and C#

855947Dec 28 2011 — edited Dec 29 2011
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 26 2012
Added on Dec 28 2011
2 comments
2,056 views