6 Replies Latest reply: Jan 17, 2013 6:15 AM by 936666 RSS

    ORA-12703

    936666
      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
          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
            SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'

            PARAMETER VALUE
            ----------------------------------------
            NLS_CHARACTERSET WE8MSWIN1252
            • 3. Re: ORA-12703
              jeneesh
              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
                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
                  NSK2KSN wrote:
                  am not getting any error with your block
                  Did you check your NLS_CHARACTERSET ?
                  • 6. Re: ORA-12703
                    936666
                    thanks