Answer
Oct 20, 2010 - 02:40 AM
Do you have a solution for your problem?
I am facing just the same problem like you.
Please let me know. thx
I am facing just the same problem like you.
Please let me know. thx
I need to pass USER defined type (Type table based on a record Type) to Oracle API using .Net
Hi,
I have a requirement where I need to pass values to a Oracle user defined type TABLE from .NET.
Here is what I have ....
Oracle type
--type describes adress object
TYPE t_address IS RECORD(
addressline1 company.address_line_1%TYPE,
addressline2 company.address_line_2%TYPE,
addressline3 company.address_line_3%TYPE,
addressline4 company.address_line_4%TYPE,
postalcode company.postcode%TYPE,
countrycode company.country_code%TYPE);
--table of adresses
TYPE t_addresses IS TABLE OF t_address;
Here is the signature of Oracle API (Part of Oracle Package)
PROCEDURE create_distributor(
i_broadcaster_ref_usage IN broadcaster_reference.reference_usage%TYPE DEFAULT g_broadcaster_ref_usage,
i_company_key IN broadcaster_reference.broadcaster_reference%TYPE,
i_name IN company.company_name%TYPE,
i_addresses IN t_addresses, -- This is where i pass the Oracle Type
i_contact_first_name IN contact.contact_first_name%TYPE,
i_contact_last_name IN contact.contact_last_name%TYPE,
i_company_type in company.company_type%type default 'D',
i_station_id in station.station_id%type default 0,
o_company_key out company.company_id%type,
o_response_cursor OUT SYS_REFCURSOR) IS
In .Net I tried it this way
Int32 companykey = 0;
t_response.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
t_address.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
string spName = "ACQUISITIONS_API.CREATE_DISTRIBUTOR";
DbCommand cmd;
OracleParameter addresses = new OracleParameter();
addresses.OracleDbType = OracleDbType.Varchar2;
// set the collection type for each parameter
addresses.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
addresses.Value = new string[6] { dist.address.addressLine1,
dist.address.addressLine2,
dist.address.addressLine3,
dist.address.addressLine4,
dist.address.postalCode,
dist.address.countryCode
};
// set the size for each array
addresses.Size = 6;
try
{
//t_address = dist.address;
cmd = database.GetStoredProcCommand(spName);
cmd.Parameters["I_BROADCASTER_REF_USAGE"].Value = "PDS";
cmd.Parameters["I_COMPANY_KEY"].Value = dist.companyKey;
cmd.Parameters["I_NAME"].Value = dist.name;
//cmd.Parameters.add(addresses);
cmd.Parameters["I_ADDRESSES"].Value = addresses;
cmd.Parameters["I_CONTACT_FIRST_NAME"].Value = dist.contactFirstName;
cmd.Parameters["I_CONTACT_LAST_NAME"].Value = dist.contactLastName;
cmd.Parameters["I_COMPANY_TYPE"].Value = "D";
cmd.Parameters["I_STATION_ID"].Value = "0";
database.LoadDataSet(cmd, resultDataSet, new string[] { "Result" });
if ((cmd.Parameters["O_RESPONSE_CURSOR"].Value != null))
{
return resultDataSet.GetXml().ToString();
}
}
catch (OracleException ex)
{
throw RaiseException("web.pilatmedia.com", "ProgramAPI", ex.Message, ex.Number.ToString(), "SampleWithCustomException()", FaultCode.Server);
}
I'm getting following error
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: OracleParameter.Value is invalid
at Oracle.DataAccess.Client.OracleParameter.ResetCtx(Int32 arraySize)
at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Pilat.Data.Oracle.PilatDatabase.DoLoadDataSetCore(DbCommand command, DataSet dataSet, String[] tableNames)
at Pilat.Data.Oracle.PilatDatabase.DoLoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at Pilat.Data.Oracle.PilatDatabase.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at DiscoveryWebService.Service2.createDistributor(Distributor dist)
--- End of inner exception stack trace ---
Can you please help me with this?
I'm not sure whether this is the correct way of mapping Oracle user defined types.
Thanks,
I have a requirement where I need to pass values to a Oracle user defined type TABLE from .NET.
Here is what I have ....
Oracle type
--type describes adress object
TYPE t_address IS RECORD(
addressline1 company.address_line_1%TYPE,
addressline2 company.address_line_2%TYPE,
addressline3 company.address_line_3%TYPE,
addressline4 company.address_line_4%TYPE,
postalcode company.postcode%TYPE,
countrycode company.country_code%TYPE);
--table of adresses
TYPE t_addresses IS TABLE OF t_address;
Here is the signature of Oracle API (Part of Oracle Package)
PROCEDURE create_distributor(
i_broadcaster_ref_usage IN broadcaster_reference.reference_usage%TYPE DEFAULT g_broadcaster_ref_usage,
i_company_key IN broadcaster_reference.broadcaster_reference%TYPE,
i_name IN company.company_name%TYPE,
i_addresses IN t_addresses, -- This is where i pass the Oracle Type
i_contact_first_name IN contact.contact_first_name%TYPE,
i_contact_last_name IN contact.contact_last_name%TYPE,
i_company_type in company.company_type%type default 'D',
i_station_id in station.station_id%type default 0,
o_company_key out company.company_id%type,
o_response_cursor OUT SYS_REFCURSOR) IS
In .Net I tried it this way
Int32 companykey = 0;
t_response.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
t_address.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
string spName = "ACQUISITIONS_API.CREATE_DISTRIBUTOR";
DbCommand cmd;
OracleParameter addresses = new OracleParameter();
addresses.OracleDbType = OracleDbType.Varchar2;
// set the collection type for each parameter
addresses.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
addresses.Value = new string[6] { dist.address.addressLine1,
dist.address.addressLine2,
dist.address.addressLine3,
dist.address.addressLine4,
dist.address.postalCode,
dist.address.countryCode
};
// set the size for each array
addresses.Size = 6;
try
{
//t_address = dist.address;
cmd = database.GetStoredProcCommand(spName);
cmd.Parameters["I_BROADCASTER_REF_USAGE"].Value = "PDS";
cmd.Parameters["I_COMPANY_KEY"].Value = dist.companyKey;
cmd.Parameters["I_NAME"].Value = dist.name;
//cmd.Parameters.add(addresses);
cmd.Parameters["I_ADDRESSES"].Value = addresses;
cmd.Parameters["I_CONTACT_FIRST_NAME"].Value = dist.contactFirstName;
cmd.Parameters["I_CONTACT_LAST_NAME"].Value = dist.contactLastName;
cmd.Parameters["I_COMPANY_TYPE"].Value = "D";
cmd.Parameters["I_STATION_ID"].Value = "0";
database.LoadDataSet(cmd, resultDataSet, new string[] { "Result" });
if ((cmd.Parameters["O_RESPONSE_CURSOR"].Value != null))
{
return resultDataSet.GetXml().ToString();
}
}
catch (OracleException ex)
{
throw RaiseException("web.pilatmedia.com", "ProgramAPI", ex.Message, ex.Number.ToString(), "SampleWithCustomException()", FaultCode.Server);
}
I'm getting following error
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: OracleParameter.Value is invalid
at Oracle.DataAccess.Client.OracleParameter.ResetCtx(Int32 arraySize)
at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Pilat.Data.Oracle.PilatDatabase.DoLoadDataSetCore(DbCommand command, DataSet dataSet, String[] tableNames)
at Pilat.Data.Oracle.PilatDatabase.DoLoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at Pilat.Data.Oracle.PilatDatabase.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at DiscoveryWebService.Service2.createDistributor(Distributor dist)
--- End of inner exception stack trace ---
Can you please help me with this?
I'm not sure whether this is the correct way of mapping Oracle user defined types.
Thanks,
Status:
Open May 16, 2010 - 04:25 AM
Do you have the same question? Follow this Question
1answer
Share Your Own Experience & Expertise
We look to ensure that every question is answered by the best people with relevant expertise and experience, the best answers include multiple perspectives. Do you have relevant expertise or experience to contribute your answer to any of these commonly asked questions?
Add New Comment