Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01460: unimplemented or unreasonable conversion requested (Oracle 10G)
ORA-01460: unimplemented or unreasonable conversion requested [message #419561] Mon, 24 August 2009 04:46 Go to next message
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 #419562 is a reply to message #419561] Mon, 24 August 2009 04:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you try to convert it from a column%type (LONG) to a CLOB (and back)?

[Updated on: Mon, 24 August 2009 04:51]

Report message to a moderator

Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419563 is a reply to message #419561] Mon, 24 August 2009 04:52 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are you using a CLOB variable for a LONG column? For that matter why are you copying the parameters to local variables at all? Just use the parameters direct in the update.

And why are you using a ref cursor? Surely two out parameters would make more sense.
Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419566 is a reply to message #419561] Mon, 24 August 2009 05:03 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12410
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 Go to previous messageGo to next message
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
Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419589 is a reply to message #419581] Mon, 24 August 2009 06:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why are you passing back a ref cursor that point to this:
OPEN ret_cursor FOR 
SELECT  RowsAffected as RowsAffected,
        SYSDATE  as UpdatedDateTime
FROM    DUAL;

Why not pass back the two values?
Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419594 is a reply to message #419561] Mon, 24 August 2009 07:23 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
In DOT Net Framework it is being handled in such a way that for all the similar procedures..I am returning the o/p results in the form of ref cursor...as they need in the form of result set from the other procedures...
Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419598 is a reply to message #419561] Mon, 24 August 2009 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
If your .net framework requires all out parameters to be ref_cursors then it's rubbish and will impact the performance of your system.

As far as your error goes it'd help a lot if you could copy and paste the full stack trace of the error so we can see which line is raising it.
Re: ORA-01460: unimplemented or unreasonable conversion requested [message #419631 is a reply to message #419594] Mon, 24 August 2009 09:54 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you still getting the error?
Previous Topic: Stored proc using merge
Next Topic: Automation Call procedure in SQLPlus to task it with Batch File
Goto Forum:
  


Current Time: Wed Dec 07 06:33:15 CST 2016

Total time taken to generate the page: 0.10735 seconds