Hello,
I am using Oracle 10.2.0.1 and working on a web application using MS VS 2005. Here it is my oracle code :
CREATE OR REPLACE PACKAGE rollsearch AS
TYPE t_cursor IS REF CURSOR;
Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
io_cursor OUT t_cursor);
END rollsearch;
CREATE OR REPLACE PACKAGE BODY rollsearch AS
Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
io_cursor OUT t_cursor)
IS
v_cursor t_cursor;
v_sql varchar2(1000);
begin
...
...
end rol_cursor;
end rollsearch;
At oracle level, no error; it is working fine (in SQL Plus).
Here it is my button click code :
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Ds As New DataSet()
Dim Oraclecon As New OracleConnection("Data Source=orcl;User ID=exam2;Password=exam2")
Oraclecon.Open()
Dim myCMD As New OracleCommand()
myCMD.Connection = Oraclecon
myCMD.CommandText = "rollsearch.rol_cursor"
myCMD.CommandType = CommandType.StoredProcedure
myCMD.Parameters.Add(New OracleParameter("c_rol", OracleType.VarChar, 7)).Direction = ParameterDirection.Input
myCMD.Parameters.Add(New OracleParameter("c_cnm", OracleType.VarChar, 30)).Direction = ParameterDirection.Input
myCMD.Parameters.Add(New OracleParameter("c_fnm", OracleType.VarChar, 30)).Direction = ParameterDirection.Input
myCMD.Parameters.Add(New OracleParameter("c_mnm", OracleType.VarChar, 30)).Direction = ParameterDirection.Input
myCMD.Parameters.Add(New OracleParameter("d_dob", OracleType.DateTime)).Direction = ParameterDirection.Input
myCMD.Parameters.Add(New OracleParameter("n_year", OracleType.Number, 4)).Direction = ParameterDirection.Input
myCMD.Parameters.Add(New OracleParameter("c_exam", OracleType.VarChar, 1)).Direction = ParameterDirection.Input
myCMD.Parameters.Add(New OracleParameter("c_exam_supp", OracleType.VarChar, 1)).Direction = ParameterDirection.Input
myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
myCMD.Parameters.Item(0).Value = Trim(TextBox5.Text) 'Roll Number
myCMD.Parameters.Item(1).Value = Trim(TextBox1.Text) 'Candidate Name
myCMD.Parameters.Item(2).Value = Trim(TextBox2.Text) 'Father Name
myCMD.Parameters.Item(3).Value = Trim(TextBox3.Text) 'Mother Name
If TextBox4.Text <> "" Then
myCMD.Parameters.Item(4).Value = Convert.ToDateTime(Trim(TextBox4.Text)) 'Date of birth
End If
If Left(DropDownList1.Text, 1) = "S" Then
myCMD.Parameters.Item(5).Value = 0 'Exam
Else
myCMD.Parameters.Item(5).Value = Val(DropDownList2.Text) 'Year
End If
If Left(DropDownList1.Text, 1) = "S" Then
myCMD.Parameters.Item(6).Value = "" 'Exam
Else
myCMD.Parameters.Item(6).Value = Left(DropDownList1.Text, 1) 'Exam
End If
If Left(DropDownList1.Text, 1) = "S" Then
myCMD.Parameters.Item(7).Value = "" 'Main Supp
Else
myCMD.Parameters.Item(7).Value = Left(DropDownList3.Text, 1) 'Main Supp
End If
'MessageBox.Show(myCMD.Parameters.Item(0).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
'MessageBox.Show(myCMD.Parameters.Item(1).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
'MessageBox.Show(myCMD.Parameters.Item(2).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
'MessageBox.Show(myCMD.Parameters.Item(3).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
'MessageBox.Show(myCMD.Parameters.Item(4).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
'MessageBox.Show(myCMD.Parameters.Item(5).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
'MessageBox.Show(myCMD.Parameters.Item(6).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
'MessageBox.Show(myCMD.Parameters.Item(7).Value, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
'MessageBox.Show(myCMD.Parameters.Count, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
Dim MyDA As New OracleDataAdapter(myCMD)
Try
MyDA.Fill(Ds)
Catch Myex As Exception
MessageBox.Show(Myex.Message, "Xx", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
End Try
datagrid1.DataSource = Ds.Tables(0)
Oraclecon.Close()
End Sub
I am not understanding the cause of :
System.Data.OracleClient.OracleException was unhandled by user code
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ROL_CURSOR'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="System.Data.OracleClient"
StackTrace:
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at _Default.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Rollsearch\Default.aspx.vb:line 62
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Please let me know, where i am wrong. Kindly let me know, if i am unclear in question and/or neet more clarification.
Thanks & Regards
Girish Sharma
|