ORA-01460 unimplemented or unreasonable conversion requested

ORA-01460 unimplemented or unreasonable conversion requested
Cause: The requested format conversion is not supported.
Action: Remove the requested conversion from the SQL statement. Check the syntax for the TO_CHAR, TO_DATE, and TO_NUMBER functions to see which conversions are supported.

Click here to visit Oracle Documentation for ORA-01460

Recently I faced ORA-01460 in one of our production environment. It was really a cryptic kind of the error message, as my code was fairly simple, following is the modified copy of that..

create or replace package body device_data_response_pkg  
as  
    procedure upsert_record( 
        p_device_id       in      number,  
        p_data_id         in      number,  
        p_update_request  in      char,  
        p_request_xml     in      varchar2
    )  
    is  
        v_data_lid      device_data.lineage_id%type;  
        v_request_xml   varchar2(4000);  
    begin  
        select  lineage_id  
        into    v_data_lid  
        from    device_data  
        where   id = p_data_id;  
        begin  
            select decode(p_update_request, '1', p_request_xml, request_xml) into v_request_xml from device_data_responses where device_id = p_device_id and data_lineage_id = v_data_lid;
        exception when no_data_found then  
            if (p_update_request = '1') then  
                v_request_xml := p_request_xml;  
            end if;  
        end;  
        .  
        .  
        .  
    end upsert_record;  
end;
/

ORA-01460 was coming at the line where I am using decode, device_data_responses.request_xml was also of varchar2(4000) datatype.

  select decode(p_update_request, '1', p_request_xml, request_xml) into v_request_xml from device_data_responses where device_id = p_device_id and data_lineage_id = v_data_lid;

After spending some time to debug ORA-01460 on my testing machine, I found that reason behind ORA-01460 is that I was using the p_request_xml parameter in my SQL query, and because it's was PL/SQL parameter it was defined as being the max size of a PL/SQL varchar2 which is 32767 bytes, which mismatch device_data_responses.request_xml which has length limit of 4000 characters.

As per the SQL manual about the DECODE function "The string returned is of VARCHAR2 data type and is in the same character set as the first result parameter". Here the first result parameter is p_request_xml which was varchar2 potentially with more than 4000 characters and other was device_data_responses.request_xml which has length limit of 4000 characters. So I was facing "ORA-01460 unimplemented or unreasonable conversion requested"

Lets try to reproduce ORA-01460 with an simple example:

SQL> declare
  2    p_request_payload varchar2(32767);
  3    v_request_payload varchar2(4000);
  4  begin
  5    v_request_payload := 'Test String';
  6    -- assiging more than 4000 chars
  7    p_request_payload := lpad('n ',2500,'x') || lpad(' ',2500,'y');
  8    dbms_output.put_line(length(p_request_payload));
  9    select decode('0', '1', p_request_payload, v_request_payload) into v_request_payload from dual;
 10    dbms_output.put_line(v_request_payload);
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 9

So keynote here is to check the length of data coming in PL/SQL varchar2 parameter before using it with other varchar2 variables/columns which have lenght limits. Otherwise it may lead you to ORA-01460.


Related Posts:
- ORA-02303: cannot drop or replace a type with type or table dependents
- ORA-01723: zero-length columns are not allowed
- ORA-01489: result of string concatenation is too long
- ORA-06502: PL/SQL: numeric or value errorstring
- ORA-01722: invalid number

- ORA-01403: no data found