All-- Please help. I keep getting the RTE "ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call" when I try to use Oracle and stored procedure and a DataAdapter. The C# and the SQL code is below and it really seems to be pretty
much the same as the "standard" Microsoft example at... http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q322160&ID=KB;EN-US;Q322160 ...so, I really need some assistance. Any ideas? Please advise. Notes-- I must use a DataAdapter. The table does exist.
The connection is open. The connection string is valid. The database user has sufficient permissions. Here is the C# code...
public void LoadAllEx(OracleConnection p_OpenConnection)
{
DataTable myDataTable;
OracleDataAdapter myOracleDataAdapter;
System.Data.OracleClient.OracleCommand myOracleCommand;
myOracleCommand = new System.Data.OracleClient.OracleCommand();
myDataTable = new DataTable();
myOracleCommand.Connection = p_OpenConnection;
myOracleCommand.CommandText = "LOD_JUR_SELALL";
myOracleCommand.CommandType = CommandType.StoredProcedure;
myOracleCommand.Parameters.Add(new OracleParameter("LOD_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output;
myOracleDataAdapter = new OracleDataAdapter(myOracleCommand);
//Code breaks here with RTE="ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'LOD_JUR_SELALL'"
myOracleDataAdapter.Fill(dataTable);
}
Here is the SQL proc code....
CREATE OR REPLACE PROCEDURE LOD_JUR_SELALL
(
outCursor OUT LOD.LOD_CURSOR
)
IS
BEGIN
OPEN outCursor FOR
SELECT
JURISDICTIONCODE,
NAME,
EXTRACTDATE,
STATUS,
ALIAS,
CERT_CYCLE,
JUR_TYPE_ID,
LEA_CODE,
COUNTY_CODE,
KOC_ID
FROM
JURISDICTION
;
END LOD_JUR_SELALL;
/
Here is the cursor defintion code...
CREATE OR REPLACE PACKAGE "LOD" AS
TYPE LOD_CURSOR IS REF CURSOR;
END LOD;
/
You don't need to specify the out cursor as a parameter. Just remove this line: myOracleCommand.Parameters.Add(new OracleParameter("LOD_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output; HTHs, Brian
...where the name of the cursor in the parameter constructor is NOT the cursor type, it is the name of parameter in the SQL, namely "outCursor" in my example.
After I fixed that, it worked like a charm. As usual, it was something hard to see but simple to fix. (See the SQL above for details.) Anyway and regardless, thank you for replying. Regardless, I will try removing that line as you suggest just for kick, (but
I think I already did but I could be wrong because I tried so many permutations). Thanks again. --Mark
Brian-- Thank you for the response. Great. Now I know that there are at least 2 ways of doing this sort of thing. BTW, in case anyone missed it, Microsoft has a decent article about all of this this... Microsoft Knowledge Base Article - 322160 HOW TO: Return
an Oracle Ref Cursor to a .NET DataReader Object by Using the .NET Managed Provider for Oracle ...located here.... http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q322160&ID=KB;EN-US;Q322160 ...and part of the code sample contains the following...
Dim Oraclecon As New OracleConnection("Password=pwd;" & "User ID=uid;Data Source=MyOracle;")
Oraclecon.Open()
Dim myCMD As New OracleCommand()
myCMD.Connection = Oraclecon
myCMD.CommandText = "curspkg_join.open_join_cursor1"
myCMD.CommandType = CommandType.StoredProcedure
myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
myCMD.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 123
Dim myReader As OracleDataReader
Try
myCMD.ExecuteNonQuery()
Catch myex As Exception
MsgBox(myex.Message)
End Try
myReader = myCMD.Parameters("io_cursor").Value
Dim x, count As Integer
count = 0
Do While myReader.Read()
For x = 0 To myReader.FieldCount - 1
Console.Write(myReader(x) & " ")
Next
Console.WriteLine()
count += 1
Loop
MsgBox(count & " Rows Returned.")
myReader.Close()
Oraclecon.Close()
...I want a simple store procedure example(Select query) and populate datagrid example. Is it necessary to use cursor. Can't I do like sql server ? Whats the best practice in terms of performance ?
KumarHarsh --
I am sorry; but, I am no longer working with Oracle.
As such, I have no context to answer your question than to say that the code noted in my solution above worked when I wrote those posts.
mkamoski
Contributor
5694 Points
1565 Posts
wrong number or types of arguments in call
Jun 16, 2004 06:58 PM|LINK
public void LoadAllEx(OracleConnection p_OpenConnection) { DataTable myDataTable; OracleDataAdapter myOracleDataAdapter; System.Data.OracleClient.OracleCommand myOracleCommand; myOracleCommand = new System.Data.OracleClient.OracleCommand(); myDataTable = new DataTable(); myOracleCommand.Connection = p_OpenConnection; myOracleCommand.CommandText = "LOD_JUR_SELALL"; myOracleCommand.CommandType = CommandType.StoredProcedure; myOracleCommand.Parameters.Add(new OracleParameter("LOD_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output; myOracleDataAdapter = new OracleDataAdapter(myOracleCommand); //Code breaks here with RTE="ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'LOD_JUR_SELALL'" myOracleDataAdapter.Fill(dataTable); }Here is the SQL proc code....CREATE OR REPLACE PROCEDURE LOD_JUR_SELALL ( outCursor OUT LOD.LOD_CURSOR ) IS BEGIN OPEN outCursor FOR SELECT JURISDICTIONCODE, NAME, EXTRACTDATE, STATUS, ALIAS, CERT_CYCLE, JUR_TYPE_ID, LEA_CODE, COUNTY_CODE, KOC_ID FROM JURISDICTION ; END LOD_JUR_SELALL; /Here is the cursor defintion code...Brian Bilbro
Member
115 Points
23 Posts
ASPInsiders
Re: wrong number or types of arguments in call
Jun 16, 2004 11:16 PM|LINK
mkamoski
Contributor
5694 Points
1565 Posts
Re: wrong number or types of arguments in call
Jun 17, 2004 02:17 AM|LINK
myOracleCommand.Parameters.Add(new OracleParameter("LOD_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output;...I should have typed this...myOracleCommand.Parameters.Add(new OracleParameter("outCursor", OracleType.Cursor)).Direction = ParameterDirection.Output;...where the name of the cursor in the parameter constructor is NOT the cursor type, it is the name of parameter in the SQL, namely "outCursor" in my example. After I fixed that, it worked like a charm. As usual, it was something hard to see but simple to fix. (See the SQL above for details.) Anyway and regardless, thank you for replying. Regardless, I will try removing that line as you suggest just for kick, (but I think I already did but I could be wrong because I tried so many permutations). Thanks again. --MarkBrian Bilbro
Member
115 Points
23 Posts
ASPInsiders
Re: wrong number or types of arguments in call
Jun 17, 2004 05:11 PM|LINK
mkamoski
Contributor
5694 Points
1565 Posts
Re: wrong number or types of arguments in call
Jun 18, 2004 06:17 PM|LINK
Dim Oraclecon As New OracleConnection("Password=pwd;" & "User ID=uid;Data Source=MyOracle;") Oraclecon.Open() Dim myCMD As New OracleCommand() myCMD.Connection = Oraclecon myCMD.CommandText = "curspkg_join.open_join_cursor1" myCMD.CommandType = CommandType.StoredProcedure myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output myCMD.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 123 Dim myReader As OracleDataReader Try myCMD.ExecuteNonQuery() Catch myex As Exception MsgBox(myex.Message) End Try myReader = myCMD.Parameters("io_cursor").Value Dim x, count As Integer count = 0 Do While myReader.Read() For x = 0 To myReader.FieldCount - 1 Console.Write(myReader(x) & " ") Next Console.WriteLine() count += 1 Loop MsgBox(count & " Rows Returned.") myReader.Close() Oraclecon.Close()...so, on with the show. Thank you. --MarkKumarHarsh
All-Star
15133 Points
3647 Posts
Re: wrong number or types of arguments in call
Aug 20, 2009 10:57 AM|LINK
Hi,
I am newbie in oracle.
I want a simple store procedure example(Select query)
and populate datagrid example.
Is it necessary to use cursor.
Can't I do like sql server ?
Whats the best practice in terms of performance ?
Thanks
Kumar Harsh
mkamoski
Contributor
5694 Points
1565 Posts
Re: wrong number or types of arguments in call
Aug 31, 2009 03:44 PM|LINK
KumarHarsh --
I am sorry; but, I am no longer working with Oracle.
As such, I have no context to answer your question than to say that the code noted in my solution above worked when I wrote those posts.
HTH.
Thank you.
-- Mark Kamoski