reference cursor odp .net

See more:

C#

SQL

.NET

Visual-Studio

ADO.NET

, +

hi i am trying to use reference cursor to retrieve data from two tables in to cursors. i will send a varchar2 field as input and get to ref cursor as output when i tried passing a input parameter its throwing error.when i try without any input parameter to return a whole table it works. Help me solve this is issue please. find my code below



Pl SQL code:

package CRM1 is
type refcursor is ref cursor;
procedure sp_customerretrive(compname in varchar2,
rcmasterdata out refcursor,
rcseconddata out refcursor);
end CRM1;

package body CRM1 is
procedure sp_customerretrive(compname in varchar2,
rcmasterdata out refcursor,
rcseconddata out refcursor) is
REFID NUMBER(6,0);

begin

SELECT COMPREFID into REFID from custrprof where COMPNAME = compname;

open rcmasterdata for
SELECT * from custrprof where COMPNAME = compname;

open rcseconddata for
SELECT * from secadd where REFID =REFID;


end sp_customerretrive;

end CRM1;



C# code:
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "CRM1.sp_customerretrive";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("companyname", OracleDbType.Varchar2, ParameterDirection.Input).Value = txtcompanyname.Text;
cmd.Parameters.Add("rcmasdata", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add("rcsecata", OracleDbType.RefCursor, ParameterDirection.Output);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);




The Error thrown is below
Server Error in '/' Application.

ORA-01036: illegal variable name/number

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Oracle.DataAccess.Client.OracleException: ORA-01036: illegal variable name/number

Source Error:


Line 168: OracleDataAdapter da = new OracleDataAdapter(cmd);
Line 169: DataSet ds = new DataSet();
Line 170: da.Fill(ds);
Line 171: ds.Tables[0].TableName = "master";
Line 172: ds.Tables[1].TableName = "secadd";

Source File: c:\Users\mohamed.faizul\Desktop\CRMWebsite latest\CRMWebsite\customerprofile.aspx.cs Line: 170

Stack Trace:


[OracleException (0x80004005): ORA-01036: illegal variable name/number]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable) +1510
Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) +52
Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) +4783
Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +298
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +88
CRMWebsite.customerprofile.Retrievecustomer() in c:\Users\mohamed.faizul\Desktop\CRMWebsite latest\CRMWebsite\customerprofile.aspx.cs:170
CRMWebsite.customerprofile.btncompanysearch_Click(Object sender, EventArgs e) in c:\Users\mohamed.faizul\Desktop\CRMWebsite latest\CRMWebsite\customerprofile.aspx.cs:123
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9750842
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +196
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18408