This discussion is archived
6 Replies Latest reply: Jan 17, 2013 4:15 AM by 936666 RSS

ORA-12703

936666 Newbie
Currently Being Moderated
hi ,
declare Column_name Nvarchar2(4000);
begin
select listagg('P_'||COLUMN_NAME ||' '||data_type, ',') within group (order by column_id) INTO Column_name
from user_tab_columns where table_name= upper('My_Table_Name');
end;

Getting the Error as

ORA-12703 : this character set conversion is not supported

how to resolve this

my CHARACTERSET

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8MSWIN1252

Thanks!

Edited by: 933663 on Jan 17, 2013 1:34 AM
  • 1. Re: ORA-12703
    jeneesh Guru
    Currently Being Moderated
    From docs:

    NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode character data. The character set of NCHAR and NVARCHAR2 datatypes can only be either AL16UTF16 or UTF8 and is specified at database creation time as the national character set. AL16UTF16 and UTF8 are both Unicode encoding.


    Whats your db national character set?
  • 2. Re: ORA-12703
    936666 Newbie
    Currently Being Moderated
    SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'

    PARAMETER VALUE
    ----------------------------------------
    NLS_CHARACTERSET WE8MSWIN1252
  • 3. Re: ORA-12703
    jeneesh Guru
    Currently Being Moderated
    So, you know the problem now.. Your character set is neither UTF8 nor AL16UTF16
    SQL> select value
      2  from nls_database_parameters
      3  where parameter='NLS_CHARACTERSET';
    
    VALUE
    --------------------
    AL32UTF8
    
    SQL> set serverout on
    
    SQL> declare
      2     Column_name Nvarchar2(4000);
      3  begin
      4     select listagg('P_'||COLUMN_NAME ||' '||data_type, ',')
      5             within group (order by column_id)
      6     INTO Column_name
      7     from user_tab_columns
      8     where table_name= upper('DEPT');
      9     dbms_output.put_line(column_name);
     10  end;
     11  /
    P_DEPTNO NUMBER,P_DNAME VARCHAR2,P_LOC VARCHAR2
    
    PL/SQL procedure successfully completed.
    Edited by: jeneesh on Jan 17, 2013 3:15 PM
    Between, why you use NVARCHAR2?Why not VARCHAR2?

    Edited by: jeneesh on Jan 17, 2013 3:15 PM
  • 4. Re: ORA-12703
    NSK2KSN Journeyer
    Currently Being Moderated
    am not getting any error with your block
    DECLARE
       Column_name   NVARCHAR2 (4000);
    BEGIN
       SELECT LISTAGG ('P_' || COLUMN_NAME || ' ' || data_type, ',')
                 WITHIN GROUP (ORDER BY column_id)
         INTO Column_name
         FROM user_tab_columns
        WHERE table_name = UPPER ('emp');
    
       DBMS_OUTPUT.put_line (column_name);
    END;
  • 5. Re: ORA-12703
    jeneesh Guru
    Currently Being Moderated
    NSK2KSN wrote:
    am not getting any error with your block
    Did you check your NLS_CHARACTERSET ?
  • 6. Re: ORA-12703
    936666 Newbie
    Currently Being Moderated
    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points