Hi,
I am getting an error while executing a sp from Oracle database. Can you let me know what could be the problem?
here is the code
using(System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
{
cn.ConnectionString="Provider=OraOLEDB.Oracle;Pass word=STORED_OBJ;User ID=STORED_OBJ;Data Source=PDCDGL1.NA.HPFS.COM;Extended Properties=;Persist Security Info=False;PLSQLRSet=1;";
cn.Open();
//String strProcName = "{CALL pkg_product.SearchProd(?, ?, ?, ?, ?, ?, {resultset 0, CDFCursor})}";
String strProcName = "{CALL pkg_product.SearchProd(?, ?, ?, ?, ?, ?}}";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = cn;
cmd.CommandText=strProcName;
cmd.CommandType=CommandType.Text;
cmd.Parameters.Add("mfrid",System.Data.OleDb.OleDb Type.Integer).Value=3;
cmd.Parameters.Add("prodtypeid",System.Data.OleDb. OleDbType.Integer).Value=1;
cmd.Parameters.Add("partnum",System.Data.OleDb.Ole DbType.VarWChar).Value=3;
cmd.Parameters.Add("descr",System.Data.OleDb.OleDb Type.VarWChar).Value="3";
cmd.Parameters.Add("active",System.Data.OleDb.OleD bType.VarWChar).Value="3";
cmd.Parameters.Add("langcode",System.Data.OleDb.Ol eDbType.VarWChar).Value="3";
//cmd.Parameters.Add("CDFCursor",System.Data.OleDb.O leDbType.VarWChar).Value="";
cmd.ExecuteReader();
cn.Close();
}
The stored procedure on the oracle database is as follows:
PROCEDURE SearchProd ( mfrid in number,prodtypeid in number,partnum in varchar2,descr in nvarchar2,
active in varchar2, langcode in varchar2,CDFCursor in out CDFCur)
Is
langcd varchar2(3);
begin
if langcode = ' ' then
langcd := 'eng';
else
langcd := langcode;
end if;
if langcd = 'eng' then
Open CDFCursor for
Select (to_char(pr.product_id) || '|' || langcd) primkey, mf.mfr_short_name mfr_short_name,
pr.part_number part_number, pr.short_desc short_descr,
langcd langcd, pt.product_type_value,
decode(ltrim(rtrim(pr.active_status_ind)),'Y',-1,0) active_status_ind
from product pr,manufacturer mf,product_type pt
where pr.mfr_id = mf.mfr_id and
pr.product_type_id = pt.product_type_id
and (pr.mfr_id = mfrid or mfrid = -1 )
and (pr.product_type_id = prodtypeid or prodtypeid = -1)
and (upper(pr.part_number) like upper(partnum) or partnum = ' ' or partnum is null )
and (upper(pr.short_desc) like upper(descr) or descr = ' ' or descr is null )
and (upper(ltrim(rtrim(pr.active_status_ind))) = upper(active) or active = ' ' or active is null );
else
Open CDFCursor for
Select distinct (to_char(pr.product_id) || '|' || langcd) primkey,ml.mfr_short_name mfr_short_name,
pl.part_number part_number, pl.short_descr short_descr,
pl.lang_cd lang_cd, pt.product_type_value,
decode(ltrim(rtrim(pr.active_status_ind)),'Y',-1,0) active_status_ind
from product pr,manufacturer mf,product_type pt,product_lang pl, mfr_lang ml
where pr.product_type_id = pt.product_type_id
and pr.product_id = pl.product_id
and pl.lang_cd = langcd
and pr.mfr_id = ml.mfr_id (+)
and ml.lang_cd (+) = langcd
and (pr.mfr_id = mfrid or mfrid = -1 )
and (pr.product_type_id = prodtypeid or prodtypeid = -1)
and (upper(pr.part_number) like upper(partnum) or partnum = ' ' or partnum is null )
and (upper(pl.short_descr) like upper(descr) or descr = ' ' or descr is null )
and (upper(ltrim(rtrim(pr.active_status_ind))) = upper(active) or active = ' ' or active is null );
end if;
end SearchProd ;
I get the following error "Unspecified error"
