ORA-01460 When the DB Characterset And National Characterset Are UTF8 (Doc ID 564277.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 11-Nov-2013***


Symptoms

After upgrading the database from 9.2.0.8.0 to 10.2.0.3.0 receiving error ORA-01460 when DB and national character set are set to 'UTF8'. This occurs when query involving more than one table and with nvarchar2 variable.

Testcase description:

- creates 2 tables each with an nvarchar2(64) column.
- creates a function with two nvarchar2 parameters which:
- opens a cursor to select from the two tables where the  nvarchar2 column in the first table is one parameter and the nvarchar2 column in the second table is the other
- fetches one row and returns the value



drop table nvarchar2_1;
create table nvarchar2_1
(
id NUMBER(4) NOT NULL,
val1 NVARCHAR2(64) NOT NULL
);

insert into nvarchar2_1 values(12,'afadafadfdafadfafa');
commit;

insert into nvarchar2_1 values(13,'afadafadfdafadfafafafdafdaffad');
commit;


drop table nvarchar2_2;
create table nvarchar2_2
(
id NUMBER(4) NOT NULL,
name NVARCHAR2(64) NOT NULL
);

insert into nvarchar2_2 values(12,'nbvnvnbvnbvnbvnvnvbn');
commit;
insert into nvarchar2_2 values(13,'uytutyutyutyituitkgkgtuituit');
commit;


create or replace function FN_TEST_NVARCHAR2
(
param1 in NVARCHAR2,
param2 in NVARCHAR2
)
return NUMBER
IS
CURSOR cur_test ( in_param1 NVARCHAR2,
in_param2 NVARCHAR2
)
IS
SELECT
a.id
FROM
nvarchar2_1 a,
nvarchar2_2 b
where a.val1=in_param1
and b.name = in_param2;
var_idnr NUMBER(4);
BEGIN
OPEN cur_test ( param1,
param2
);
FETCH cur_test INTO var_idnr;
CLOSE cur_test;

return( var_idnr );
end FN_TEST_NVARCHAR2;
/


select FN_TEST_NVARCHAR2('afadafadfdafadfafa', 'uytutyutyutyituitkgkgtuituit')val from dual;
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SCOTT.FN_TEST_NVARCHAR2", line 12
ORA-06512: at "SCOTT.FN_TEST_NVARCHAR2", line 22

Changes

Upgraded the database from 9.2.0.8.0 to 10.2.0.3.0.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms