Home » RDBMS Server » Server Administration » ORA-12714 (Oracle 10.1.0.2.0)
ORA-12714 [message #407225] Tue, 09 June 2009 03:42 Go to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
Hi,

On a Database I have the following view:
CREATE OR REPLACE VIEW VCOMCD_CLM AS
SELECT -- Comptes
J.NMINT
FROM
COMCRG X,
COMTRA T,
COMTLC U,
COMPRF P,
TYMCOM C,
COMCRD D,
INTERV J,
COMCRA A,
COMCRE E
WHERE
T.NOTCO=X.NOTCO AND
U.CTLCO=X.CTLCO AND
P.NPRFT=T.NPRFT AND
C.CTCOM=P.CTCOM AND
C.COLAN=U.COLAN AND
D.NOLCO=X.NOLCO AND
A.NOECO=D.NOECO AND
E.NOECO=D.NOECO AND
J.COINT=D.CODES AND
D.CTDES='K'
UNION ALL
SELECT
TO_NCHAR(NULL)
FROM
DUAL


When I try to perform a select on this view I get this error:
ORA-12714: jeu de caractères national indiqué non valide


The NLS_NCHAR_CHARACTERSET parameter is set to AL16UTF16

If I execute the query of my view I have no problem.

If I execute only
SELECT TO_NCHAR(NULL)FROM DUAL
it works without problem.

I don't get this issue on 10.2.0.4.0 database.

Any idea ?

Re: ORA-12714 [message #407228 is a reply to message #407225] Tue, 09 June 2009 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-12714: invalid national character set specified
 *Cause: Only UTF8 and AL16UTF16 are allowed to be used as the national
         character set
 *Action: Ensure that the specified national character set is valid

What is the datatype of J.NMINT?
Post a desc of INTERV

Regards
Michel
Re: ORA-12714 [message #407242 is a reply to message #407228] Tue, 09 June 2009 04:20 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
Name   Type          Nullable Default Comments 
------ ------------- -------- ------- -------- 
COINT  VARCHAR2(11)                            
CTINT  VARCHAR2(1)                             
NMINT  NVARCHAR2(30) Y                         
LICIV  VARCHAR2(20)  Y                         
ADINT1 NVARCHAR2(30) Y                         
ADINT2 NVARCHAR2(30) Y                         
ADINT3 NVARCHAR2(30) Y                         
ADINT4 NVARCHAR2(30) Y                         
COLAN  VARCHAR2(1)                             
COTAX  VARCHAR2(1)   Y                         
CREFI  VARCHAR2(2)   Y                         
COCAI  VARCHAR2(1)   Y                         
NUCPT  VARCHAR2(8)   Y                         
NBJDP  NUMBER(1)     Y                         
NUBNK  VARCHAR2(23)  Y                         
NMFRA  VARCHAR2(30)  Y                         
NMANG  VARCHAR2(30)  Y                         
NMHOL  VARCHAR2(30)  Y                         
NMESP  VARCHAR2(30)  Y                         
NMITA  VARCHAR2(30)  Y                         
CLINT  VARCHAR2(2)   Y                         
COSIE  VARCHAR2(5)   Y                         
NUTEL  VARCHAR2(100) Y                         
NUTLX  VARCHAR2(100) Y                         
NUFAX  VARCHAR2(100) Y                         
COECO  VARCHAR2(3)   Y                         
COJUR  VARCHAR2(2)   Y                         
CPTIT  VARCHAR2(8)   Y                         
CIINT  VARCHAR2(13)  Y                         
NOSIR  VARCHAR2(13)  Y                         
CDICP  VARCHAR2(1)   Y                         
CCOMP  VARCHAR2(1)   Y                         
SURFA  VARCHAR2(1)                             
NRECO  NUMBER(1)     Y                         
CCONG  VARCHAR2(1)   Y                         
CTCCO  VARCHAR2(1)   Y                         
CSINT  VARCHAR2(1)   Y                         
INTAC  VARCHAR2(1)   Y                         
CPISO  VARCHAR2(2)   Y                         
COCNV  VARCHAR2(1)   Y                         
CDVCV  VARCHAR2(3)   Y                         
NMGER  VARCHAR2(30)  Y                         
EMAIL  VARCHAR2(100) Y                         
CBUFI  VARCHAR2(25)  Y                         
NUENR  VARCHAR2(30)  Y                         
CDPBN  VARCHAR2(2)   Y                         
NMGRE  VARCHAR2(30)  Y                         
COING  VARCHAR2(11)  Y                         
COCGP  VARCHAR2(1)   Y                         
DASYS  DATE                                    
COINF  VARCHAR2(8)   Y                         
FXRAC  VARCHAR2(1)   Y                         
ADINT5 NVARCHAR2(30) Y                         
CDVRF  VARCHAR2(3)   Y                         
NINT2  NVARCHAR2(30) Y                         
CONAS  NUMBER(9)     Y                         
COTIN  VARCHAR2(9)   Y                         
STTQI  VARCHAR2(1)   Y                         
RGCOD  VARCHAR2(2)   Y                         
RGREF  VARCHAR2(5)   Y                         
RISET  NUMBER(6)     Y                         
TYPNG  VARCHAR2(3)   Y                         
CCAME  VARCHAR2(1)   Y                         
FACCL  VARCHAR2(1)   Y                         
CCOTH  VARCHAR2(1)   Y                         
CORIG  VARCHAR2(20)  Y                         
PORIG  NUMBER(6)     Y                         
DACRE  DATE                                    
IDINT  NUMBER(10)                              
IDETY  NUMBER(10)    Y                         
RGCID  NUMBER(3)     Y                         
DAOUV  DATE          Y                         
DAFER  DATE          Y                         
CPTTI  VARCHAR2(21)  Y                         
CMPTY  VARCHAR2(1)   Y                         
TAXDF  VARCHAR2(1)   Y                         
DABIR  DATE          Y                         
COUTI  VARCHAR2(8)   Y                         
MGMIN  NUMBER(8)     Y                         
MGPER  VARCHAR2(1)   Y                         
MGPED  NUMBER(2)     Y                         
MGFTR  VARCHAR2(1)   Y                         
NRGMC  VARCHAR2(1)   Y                         
FDTRF  VARCHAR2(1)   Y                         
ACVCD  VARCHAR2(1)   Y        'N'              
CLMCM  VARCHAR2(3)   Y                         
MGMMB  NUMBER(8)     Y                         
CLOPS  VARCHAR2(1)   Y                         
CLRTY  NUMBER(1)     Y                         
COPNR  VARCHAR2(20)  Y                         
EXDLY  VARCHAR2(1)   Y        'N'              
EXMLY  VARCHAR2(1)   Y        'N'              
EXYLY  VARCHAR2(1)   Y        'N'              
LOGOC  VARCHAR2(80)  Y                         
FACOP  VARCHAR2(1)   Y        'N'              
FACOA  VARCHAR2(1)   Y        'N' 
Re: ORA-12714 [message #407256 is a reply to message #407242] Tue, 09 June 2009 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is INTERV a view or a table?
Is it local or remote?

Use SQL*Plus and copy and paste the result of:
select property_value 
from database_properties 
where property_name = 'NLS_NCHAR_CHARACTERSET'
/
SELECT TO_NCHAR(NULL) FROM DUAL
/
SELECT J.NMINT FROM INTERV J WHERE ROWNUM = 1
/
SELECT J.NMINT FROM INTERV J WHERE ROWNUM = 1
UNION ALL
SELECT TO_NCHAR(NULL) FROM DUAL
/

Regards
Michel
Re: ORA-12714 [message #407290 is a reply to message #407256] Tue, 09 June 2009 07:57 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
INTERV is a table.

select property_value
  2  from database_properties
  3  where property_name = 'NLS_NCHAR_CHARACTERSET'
  4  /

PROPERTY_VALUE
--------------------------------------------------------------------------------
AL16UTF16

SQL> SELECT TO_NCHAR(NULL) FROM DUAL
  2  /

TO_NCHAR(NULL)
--------------

SQL> SELECT J.NMINT FROM INTERV J WHERE ROWNUM = 1
  2  /

NMINT
------------------------------------------------------------
Gestionnaire_recettes

SQL> SELECT J.NMINT FROM INTERV J WHERE ROWNUM = 1
  2  UNION ALL
  3  SELECT TO_NCHAR(NULL) FROM DUAL
  4  /

NMINT
------------------------------------------------------------
Gestionnaire_recettes
Re: ORA-12714 [message #407292 is a reply to message #407290] Tue, 09 June 2009 08:04 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
But il I create a view like this:
create view aa_toto as
SELECT J.NMINT FROM INTERV J WHERE ROWNUM = 1
UNION ALL
SELECT TO_NCHAR(NULL) FROM DUAL;


and if I select this view I get the ORA-12714 error

If I create a view like this:
create view aa_toto2 as
SELECT TO_NCHAR(NULL) toto FROM DUAL
UNION ALL
SELECT J.NMINT FROM INTERV J WHERE ROWNUM = 1
;


When I perform a select on it, it works.

How it's possible ?
Re: ORA-12714 [message #407293 is a reply to message #407292] Tue, 09 June 2009 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
desc aa_toto 
select object_type from user_objects where object_name='INTERV';

Regards
Michel

[Updated on: Tue, 09 June 2009 08:08]

Report message to a moderator

Re: ORA-12714 [message #407294 is a reply to message #407293] Tue, 09 June 2009 08:08 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member

SQL> desc aa_toto
Name  Type          Nullable Default Comments 
----- ------------- -------- ------- -------- 
NMINT NVARCHAR2(60) Y      
Re: ORA-12714 [message #407296 is a reply to message #407294] Tue, 09 June 2009 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select object_type from user_objects where object_name='INTERV';

Regards
Michel
Re: ORA-12714 [message #407297 is a reply to message #407296] Tue, 09 June 2009 08:18 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
SQL> select object_type from user_objects where object_name='INTERV';

OBJECT_TYPE
-------------------
TABLE


I told you it was a table. You don't trust me ? Sad
Re: ORA-12714 [message #407301 is a reply to message #407297] Tue, 09 June 2009 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I just wanted to check if there is no other object like a synonym and if the object is owned by you and not by another one.
In short, I just try to see what is your environment. Remember the only thing I know is the one you post here.
Is the owner of the view the same one than the table owner.
That is:
select object_type from user_objects where object_name='AA_TOTO';


Regards
Michel
Re: ORA-12714 [message #407307 is a reply to message #407301] Tue, 09 June 2009 09:42 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
select object_type from user_objects where object_name='AA_TOTO';

OBJECT_TYPE
-------------------
VIEW
Re: ORA-12714 [message #407454 is a reply to message #407307] Wed, 10 June 2009 04:19 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
So??????
No idea?
Re: ORA-12714 [message #407469 is a reply to message #407454] Wed, 10 June 2009 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Currently no idea.
Select s.parameter, s.value "SESSION", d.value "DB"
from v$nls_parameters s, nls_database_parameters d
where d.parameter (+) = s.parameter
  and s.parameter = 'NLS_CHARACTERSET';

Regards
Michel
Re: ORA-12714 [message #407491 is a reply to message #407454] Wed, 10 June 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can also check with something like the following procedure:
declare
  last_val varchar2(100);
  x_chset exception;
  pragma exception_init (x_chset, -12714);
begin
  for rec in (select NMINT from aa_toto order by 1) loop
    last_val := convert(rec.NMINT,'<your database charset>','AL16UTF16') ;
  end loop;
exception 
  when x_chset then 
    dbms_output.put_line('last value: '||last_val);
end;
/

Then you can get the offending value from your table (it is the next one) and post it.

Regards
Michel
Re: ORA-12714 [message #407505 is a reply to message #407491] Wed, 10 June 2009 06:21 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
SQL> Select s.parameter, s.value "SESSION", d.value "DB"
  2  from v$nls_parameters s, nls_database_parameters d
  3  where d.parameter (+) = s.parameter
  4    and s.parameter = 'NLS_CHARACTERSET';

PARAMETER        SESSION      DB
---------------- ------------ ------------
NLS_CHARACTERSET WE8ISO8859P1 WE8ISO8859P1

[Updated on: Wed, 10 June 2009 06:35] by Moderator

Report message to a moderator

Re: ORA-12714 [message #407514 is a reply to message #407505] Wed, 10 June 2009 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I think you have a problem due to some data that are not in your database character set (probably an Oracle bug).
Can you execute the PL/SQL block I posted and send the offending data I can check it on my place.

Regards
Michel
Re: ORA-12714 [message #407516 is a reply to message #407514] Wed, 10 June 2009 06:45 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
declare
  last_val varchar2(100);
  x_chset exception;
  pragma exception_init (x_chset, -12714);
begin
  for rec in (select NMINT from aa_toto order by 1) loop
    last_val := convert(rec.NMINT,'WE8ISO8859P1','AL16UTF16') ;
  end loop;
exception
  when x_chset then
    dbms_output.put_line('last value: '||last_val);
end;

ORA-06550: Ligne 6, colonne 21 :
PL/SQL: ORA-12714: jeu de caractères national indiqué non valide
ORA-06550: Ligne 6, colonne 14 :
PL/SQL: SQL Statement ignored
ORA-06550: Ligne 7, colonne 25 :
PLS-00364: utilisation de variable d'index boucle 'REC' invalide
ORA-06550: Ligne 7, colonne 5 :
PL/SQL: Statement ignored
Re: ORA-12714 [message #407526 is a reply to message #407516] Wed, 10 June 2009 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So seems to be an Oracle bug and you have to open a SR.

Regards
Michel
Re: ORA-12714 [message #407530 is a reply to message #407526] Wed, 10 June 2009 07:15 Go to previous messageGo to next message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
or migrate to oracle 10.2

thank you very much for your investigations
Re: ORA-12714 [message #407534 is a reply to message #407530] Wed, 10 June 2009 07:22 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sure it is a prerequisite, I don't think Oracle will investigate much in 10.1. Wink

Regards
Michel
Previous Topic: DBLinks Inactive Sessions
Next Topic: ORA-03134: Connections to this server version are no longer supported.
Goto Forum:
  


Current Time: Wed Dec 04 19:15:51 CST 2024