ORA-01460: unimplemented or unreasonable conversion requested [message #419561] |
Mon, 24 August 2009 04:46  |
Hemanth123
Messages: 56 Registered: April 2009
|
Member |
|
|
Hi,
We have table User_Config in our database where in the user from the front end(DOT NET) try to update the column Configuration Data which is of LONG data type(we can not alter the data type of the column) with XML data which doesn't exceeds more than 2MB.
When trying to update the column with 1.5MB of data we are getting the following error:
ORA-01460: unimplemented or unreasonable conversion requested
Structure of the table:
userid --- VARCHAR2(8)
configurationid ---- VARCHAR2(35)
configurationdata --- LONG
I searched with the above error in many links but couldn't find any solution.
Please find below the procedure being called to update the table.
PROCEDURE USP_UserConfig_Update(
UserId IN UserConfig.UserId%Type DEFAULT NULL,
ConfigurationId IN UserConfig.ConfigurationId%Type DEFAULT NULL,
ConfigurationData IN UserConfig.ConfigurationData%Type DEFAULT NULL,
ret_cursor OUT SYS_REFCURSOR)
IS
l_UserId UserConfig.UserId%Type ;
l_ConfigurationId UserConfig.ConfigurationId%Type ;
l_ConfigurationData CLOB ;
RowsAffected NUMBER(15) := 0;
BEGIN
l_UserId := UserId;
l_ConfigurationId := ConfigurationId;
l_ConfigurationData := ConfigurationData;
UPDATE UserConfig
SET ConfigurationData = l_ConfigurationData
WHERE UserId = l_UserId
AND ConfigurationId = l_ConfigurationId;
RowsAffected := SQL%ROWCOUNT;
OPEN ret_cursor FOR
SELECT RowsAffected as RowsAffected,
SYSDATE as UpdatedDateTime
FROM DUAL;
--COMMIT;
END USP_UserConfig_Update;
Any helpful is appreciated.
Thanks,
Hemanth
|
|
|
|
|
Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419566 is a reply to message #419561] |
Mon, 24 August 2009 05:03   |
Hemanth123
Messages: 56 Registered: April 2009
|
Member |
|
|
Sorry for not changing the datatype...for testing purpose I have changed it from LONG to CLOB and mistakenly I have copied the same..in the code it was LONG only..
and about the local variables..that was the format given for coding..as the input parameters match that of the column names from the tables and can not be used in Update procedure...as the logic I am using in other update procedurees is as below..
UPDATE DA_Detail
SET DA_DETAIL_ID = NVL(l_DADetailId,DA_DETAIL_ID),
DA_NO = NVL(l_DANo,DA_NO),
CREDIT_STATUS = NVL(l_CreditStatus,CREDIT_STATUS),
DA_LINETYPE_ID = NVL(l_DALineTypeId,DA_LINETYPE_ID),
TEXT = NVL(l_Text,TEXT)...
Please ignore them..if that is the issue..I have changed it and run it again..but still causing the same error..
Thanks,
Hemanth
|
|
|
Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419569 is a reply to message #419566] |
Mon, 24 August 2009 05:08   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Hemanth123 wrote on Mon, 24 August 2009 11:03 |
and about the local variables..that was the format given for coding..as the input parameters match that of the column names from the tables and can not be used in Update procedure...
|
That's why you should use prefixes for parameters as well as local variables. Most places use p_
Hemanth123 wrote on Mon, 24 August 2009 11:03 |
I have changed it and run it again..but still causing the same error..
|
So post the modified code.
|
|
|
Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419581 is a reply to message #419561] |
Mon, 24 August 2009 06:22   |
Hemanth123
Messages: 56 Registered: April 2009
|
Member |
|
|
Hi,
Please find below the modified code.
PROCEDURE USP_UserConfig_Update(
UserId IN UserConfig.UserId%Type DEFAULT NULL,
ConfigurationId IN UserConfig.ConfigurationId%Type DEFAULT NULL,
ConfigurationData IN UserConfig.ConfigurationData%Type DEFAULT NULL,
ret_cursor OUT SYS_REFCURSOR)
IS
l_UserId UserConfig.UserId%Type ;
l_ConfigurationId UserConfig.ConfigurationId%Type ;
l_ConfigurationData LONG ;
RowsAffected NUMBER(15) := 0;
BEGIN
l_UserId := UserId;
l_ConfigurationId := ConfigurationId;
l_ConfigurationData := ConfigurationData;
UPDATE UserConfig
SET ConfigurationData = l_ConfigurationData
WHERE UserId = l_UserId
AND ConfigurationId = l_ConfigurationId;
RowsAffected := SQL%ROWCOUNT;
OPEN ret_cursor FOR
SELECT RowsAffected as RowsAffected,
SYSDATE as UpdatedDateTime
FROM DUAL;
--COMMIT;
END USP_UserConfig_Update;
Let me know the better alternative.
Thanks,
Hemanth
|
|
|
|
|
|
|