Error :ORA-01460 unimplemented or unreasonable conversion requested.
Today i want to discuss an article regarding above mentioned Article Title unimplemented or unreasonable conversion requested.In which scenario you will get this Exception ? What you do to overcome this Exception ?These are the things i will discuss here in detail with code snippets and real time images
In my Present Project that i have a particular task to upload a Excel File and those excel file records will be inserted into Oracle Database.What are the Options in my hand to insert that Bulk data in to Oracle database. Let us see the options and drawbacks of below Options here Option 1 :
Writing a Inline query
So the above option will not yield fruitful result
The Code looks some thing like this...
string insertQuery ="";
foreach(Datarow dr in dataset.Tables[0].Rows)
{
//some where here checking weather that record exists....if exists update using Update query not exists insert query.
insertquery = insertQuery + "insert into Tablename values('"+dr.Rows[0].ToString() +"','"+ dr.Rows[0][1].ToString()+"' );
}
SqlConnection con =new SqlConnection("Connection string");
con.Open();
SqlCommand cmd =new SqlCommand(insertQuery, con);
cmd.ExecuteNonQuery(); Option 2 :
Using BulkCopy
Similar to SqlBulkCopy class there is a OracleBulkcopy class in .Net .I think the Oracle Corporation had lately realized to introduce this class in Oracle Data Access Client dll(Add a reference in Vs.Net).Because this Class will not be available for all versions of Oracle Data Access Client my recent research studies says that Oracle Data Access client dll version 11.0 or higher will have this Class Associated those who are working with less versions of this specified dll (Oracle Data Access client dll) this class will not be available and checking of records(Already exists in the table) will be difficult when using this OracleBulkcopy class.You need to delete all the rows and again insert all records(including old records).But there is no Guarantee you will upload the same excel (There exists the Old records are there in your Excel).If that is the case then there will be huge data loss or the second approach is you need to write a trigger to check there exists a record or not in the Database table.
So this option is some what useful but not at full extent which fulfill our Criteria
Sample Code if you have Oracle Data Access Client version more than 11.0
OracleBulkCopy Obcp =new OracleBulkCopy(write your connection string)
Obcp.DestinationTableName="xyz";
Obcp.writerServerName(dst); Option 3 :
Convert Dataset into xml String
The above two options are failed to solve our Problem.
Now for to accomplish this task first we need to convert the Dataset in to xml string that is done by below code Snippet.
strConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbConnection ObjCon = new OleDbConnection(strConnString);
ObjCon.Open();
DataTable dt = ObjCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
ExcelColumnValidation(dt);
OleDbCommand Oledbcmd = new OleDbCommand("select * from [" + dt.Rows[0]["TABLE_NAME"].ToString() + "]", ObjCon);
OleDbDataAdapter OledbAdapt = new OleDbDataAdapter(Oledbcmd);
DataSet dstset = new DataSet();
OledbAdapt.Fill(dstset);
dstset.Tables[0].TableName = "DATASET TABLE NAME";
System.IO.StringWriter swxml = new System.IO.StringWriter();
dstset.WriteXml(swxml);
string strpartDetails = swxml.ToString();
InsertedOracleDatabase(strpartDetails);
Using this Code if the Records in the Excel are more than 100 records you will get the Following error we will see the Snapshot of that in the below image . Because the length of the String will get exceeded.So the Oracle Database will not accept a Long string length.
This Error will come whenever you are converting Dataset(with Thousands of Records) into xml string and sent to Oracle Database.The Database will not take that long string to insert the rows in the table.Hence the above Error will occur.
Actually this is a Bug with Oracle Database.I had studied this bug was fixed in dlls of 11.0 and above versions.But those who are working less versions of Oracle Client Dlls There is a way to work out.That is to insert the data in Oracle Database into Chunks(in small parts).
For Example if we have 1600 records we need to divide those records in 16 parts.For each time we need to take 100 at a time and sent to the Database.And you will be inserted all the rows in this way and checking is also easy.
See the Oracle Procedure how to insert into Oracle....
CREATE OR REPLACE PROCEDURE SP_Ins_(s_partDetails VARCHAR2) AS
XMLType SYS.XMLTYPE;
/******************************************************************************
NAME: SP_Ins_Soverignpartinfo
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 6/2/2014 1. Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: SP_Ins_Soverignpartinfo
Sysdate: 6/2/2014
Date and Time: 6/2/2014, 3:40:49 PM, and 6/2/2014 3:40:49 PM
Username: (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
XMLType := sys.xmltype.CreateXML(s_partDetails);
FOR partinfo in
(
select
--Soverignpartinfo.Extract('//DATASET TABLE NAME/MAXAVAILABILITYQTY/text()').getstringval() as maxavailibilitys,
Soverignpartinfo.Extract('//DATASET TABLE NAME/PARTNO/text()').getstringval() as partno,
Soverignpartinfo.Extract('//DATASET TABLE NAME/PRICE/text()').getstringval() as price,
Soverignpartinfo.Extract('//DATASET TABLE NAME/CURRENCY/text()').getstringval() as currency,
Soverignpartinfo.Extract('//DATASET TABLE NAME/MAXAVAILABILITYQTY/text()').getstringval() as maxavailibilitys,
Soverignpartinfo.Extract('//DATASET TABLE NAME/MINQTY/text()').getstringval() as Minqty,
Soverignpartinfo.Extract('//DATASET TABLE NAME/AnonymousPrice/text()').getstringval() as AnonymousPrice
FROM
TABLE (XMLSEQUENCE (XMLTYPE.EXTRACT('//NewDataSet/SoverignPartInfo')))Soverignpartinfo
)
loop
DECLARE
Countint NUMBER;
BEGIN
SELECT Count(*) INTO Countint FROM SOVERIGN_PARTINFO
WHERE PARTNO = TRIM(partinfo.PARTNO); -- no such number
IF Countint = 0 then
INSERT INTO SOVERIGN_PARTINFO(PARTNO,PRICE,CURRENCY,MAXAVAILABILITYQTY,LASTUPDATEDDATE,MINQTY,AnonymousPrice) VALUES
(TRIM(partinfo.PARTNO),TRIM(partinfo.price),TRIM(partinfo.currency),TRIM(partinfo.maxavailibilitys),sysdate,trim(partinfo.Minqty),trim(partinfo.AnonymousPrice));
else
UPDATE SOVERIGN_PARTINFO SET PRICE = trim(partinfo.price),CURRENCY=trim(partinfo.currency),MAXAVAILABILITYQTY= trim(partinfo.maxavailibilitys),LASTUPDATEDDATE=sysdate,MINQTY= trim(partinfo.Minqty),AnonymousPrice= trim(partinfo.AnonymousPrice) WHERE trim(PARTNO) = trim(partinfo.PARTNO);
END IF;
END;
End LOOP;
COMMIT;
END SP_Ins_Soverignpartinfo;
now in the above Previous Code i had written how to convert to dataset into xml type or xml string and sent to Database stored procedure .Now we have to partition the dataset so that 100 records were sent into Database at a time.
This is the code for that in .Net
int EndLimit = 100;
int startLimit = 0;
while (startLimit <= dstset.Tables[0].Rows.Count)
{
DataTable dataTable = dstset.Tables[0].AsEnumerable().Skip(startLimit).Take(EndLimit).CopyToDataTable();
DataSet dst = new DataSet();
dst.Tables.Add(dataTable);
System.IO.StringWriter swxml = new System.IO.StringWriter();
dst.Tables[0].TableName = "SoverignPartInfo";
dst.WriteXml(swxml);
string strpartDetails = swxml.ToString();
InsertedOracleDatabase(strpartDetails);
startLimit = startLimit + EndLimit;
}
Points to remember:
1)add the Namespace using System.Linq;
2) Copy all the rows in to Datatable and make it as Enumerable
3) Define the start Limit and End Limit start Limit as 0 and End Limit as 100
4)use skip and Take Limit Functions to skip the Start Limit by using SKIP() and use EndLimit Take Function()
5)Convert the dataset to Xml string and sent to Database Procedure.
6)At the End Add the Start Limit variable to the End Limit Variable.
7)Check the Total records in dataset to the Start Limit records (it should be less than or equal to) using While Loop.
full code
string path = Server.MapPath("~/uploadexcel");
//Connection String to Excel Workbook
path = path + fuploadbrowse.PostedFile.FileName;
fuploadbrowse.SaveAs(path);
strConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fuploadbrowse.PostedFile.FileName + ";Extended Properties=Excel 8.0;HDR=YES";
OleDbConnection ObjCon = new OleDbConnection(strConnString);
ObjCon.Open();
OleDbCommand Oledbcmd = null;
OleDbDataAdapter OledbAdapt = null;
DataTable dt = ObjCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
Oledbcmd = new OleDbCommand("select * from [" + dt.Rows[0]["TABLE_NAME"].ToString() + "]", ObjCon);
OledbAdapt = new OleDbDataAdapter(Oledbcmd);
DataSet dstset = new DataSet();
OledbAdapt.Fill(dstset);
dstset.Tables[0].TableName = "DATABASE TABLE NAME";
int EndLimit = 100;
int startLimit = 0;
while (startLimit <= dstset.Tables[0].Rows.Count)
{
DataTable dataTable = dstset.Tables[0].AsEnumerable().Skip(startLimit).Take(EndLimit).CopyToDataTable();
DataSet dst = new DataSet();
dst.Tables.Add(dataTable);
System.IO.StringWriter swxml = new System.IO.StringWriter();
dst.Tables[0].TableName = "SoverignPartInfo";
dst.WriteXml(swxml);
string strpartDetails = swxml.ToString();
bool retvalue = InsertedOracleDatabase(swritexml);
startLimit = startLimit + EndLimit;
}
if (retvalue)
{
lblBpcResult.Visible = true;
lblBpcResult.Text = " Inserted the Data Successfully";
return;
}
else
{
lblBpcResult.Visible = true;
lblBpcResult.Text = "Failure in Inserting in Database";
return;
}
BindGridUploadExcelData();
}
public void InsertedOracleDatabase(string swritexml)
{
OracleConnection con = new OracleConnection();
con.ConnectionString = eStoreUtils.GetConnectionString("OraProd");
con.Open();
OracleCommand cmd = new OracleCommand("SP_Ins_Soverignpartinfo", con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param = new OracleParameter();
param.ParameterName = "s_partDetails";
param.DbType = DbType.String;
param.Direction = ParameterDirection.Input;
param.Value = swritexml;
cmd.Parameters.Add(param);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.Connection = con;
int retvalue = cmd.ExecuteNonQuery();
return retvalue;
}
Note: If you search you may find some answers for example by changing the input Parameter Datatype to Varchar2 (in Oracle stored Procedure) to some other Datatype.I tried those things but those efforts will be in vain.So by using the above method i got the solution.
Attachments
Follow