1 2 Previous Next 26 Replies Latest reply on Jul 14, 2020 3:48 PM by 580988

    Unable to insert special symbols in table

    580988

      Hi All,

       

           I have a special symbol (double dollar) in the line in a.txt file for e.g

           0§EBBS§BH§20200520§20200520.ACCOUNT§20200520161733§ACCOUNT§

       

           There is a stored procedure which reads the files from Unix server using utl_file and tries to insert into a table.

           After executing the procedure, i am getting the result as below

       

      0�BS��200520�200520.ACCOUNT�200520161733�COUNT

       

           However when i insert s sample directly in sql developer for verification, i am able to insert the special symbol

       

      insert into table1 values ('0§EBBS§BH§20200520§20200520.ACCOUNT§20200520161733§ACCOUNT§');

       

      I have tried transferring the file in both "Text" mode and "Binary" mode in Winscp. Can someone kndly tell me why the special symbol is not being inserted as it is ?

       

      Thanks

      Gautam

        • 1. Re: Unable to insert special symbols in table
          Tubby

          580988 wrote:

           

          Hi All,

           

          I have a special symbol (double dollar) in the line in a.txt file for e.g

          0§EBBS§BH§20200520§20200520.ACCOUNT§20200520161733§ACCOUNT§

           

          There is a stored procedure which reads the files from Unix server using utl_file and tries to insert into a table.

          After executing the procedure, i am getting the result as below

           

          0�BS��200520�200520.ACCOUNT�200520161733�COUNT

           

          However when i insert s sample directly in sql developer for verification, i am able to insert the special symbol

           

          insert into table1 values ('0§EBBS§BH§20200520§20200520.ACCOUNT§20200520161733§ACCOUNT§');

           

          I have tried transferring the file in both "Text" mode and "Binary" mode in Winscp. Can someone kndly tell me why the special symbol is not being inserted as it is ?

           

          Thanks

          Gautam

          https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:19309016771652

           

          Cheers,

          • 2. Re: Unable to insert special symbols in table
            580988

            Hi,

             

            I wrote this pl sql block to read from the file and executed it in SQL Developer

             

            SET SERVEROUTPUT ON

            DECLARE

            f_dir VARCHAR2(100);

            fname VARCHAR2(500);

            f utl_file.file_type;

            f_line VARCHAR2(4000);

            BEGIN

            f_dir := 'CCS_RCMS_CHEQUE_LISTENER';

            fname := 'SAMPLE.TXT';

            f := utl_file.fopen(f_dir,fname,'r');

            utl_file.get_line(f,f_line);

            DBMS_OUTPUT.PUT_LINE('1TEST'||f_line);

            utl_file.fclose(f);

            end;

            /

             

            It shows the output as below:

             

            1TEST0?EBBS?BH?20200520?20200520.ACCOUNT?20200520161733?ACCOUNT?

             

            It is not able to display the section symbol.

             

            I ran the sample statement.

            select replace('My text with [COPY] in it','[COPY]',(UNISTR(CHR(167)))) from dual;

            Output :

            My text with �n it

             

            Can some body kindly suggest what needs to be done to read the symbol. Any help would be highly appreciated.

            Regards

            Gautam

            • 3. Re: Unable to insert special symbols in table
              Peter de Vaal

              This looks more like you do not have the proper characterset settings, either in the database or in the client (SQL Developer) or in the SAMPLE.TXT file.

              In order to handle special characters in a proper way it is recommended since more than 20 years to use UTF8 encoding, but unlucky enough I see a majority still wrestling with 8-bits encodings, such as iso8859-1 etc.

              So first find out what are your settings?

               

              In the database: select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

              In SQL Developer: Preferences->General->Encoding

              To determine the encoding of the text document: Open it in a tool such as Notepad++ or Atom. Then the tool will show you the encoding

               

              The most important in your test from SQL Developer is that you have chosen an encoding that can hold the character that you have problems with. UTF8 is the most obvious, it can have every thinkable character. If the character is correctly stored in the database then the database will take care of conversion to the client's encoding.

               

              When you still have problems it might be that the database characterset can not store the character.

              Note that the text file should have the same encoding as the database when using utl_file, see in the manual -> utl_file -> Operational Notes.

              1 person found this helpful
              • 4. Re: Unable to insert special symbols in table
                Mike Kutz

                What is your database's characterset?

                • 5. Re: Unable to insert special symbols in table
                  580988

                  Hi,

                   

                  select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

                  Output:

                  NLS_CHARACTERSET UTF8

                   

                  In Prefernce-> Environment I changed Encoding to UTF8

                   

                  I  also changed the encoding in the file to UTF8 without BOM in Notepad++.

                   

                  But still i am not able to read the special character from the file.

                  Could you pl help ?

                  • 6. Re: Unable to insert special symbols in table
                    580988

                    Hi,

                     

                    The character set of my DB is UTF8.

                    select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

                    Output:

                     

                    NLS_CHARACTERSET UTF8

                     

                    Can you pl point out how to read the special symbol?

                     

                    Thanks

                    Gautam

                    • 7. Re: Unable to insert special symbols in table
                      cormaco

                      You can check if the data has been loaded correctly into the database by using the DUMP function:

                      https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/DUMP.html

                      select dump('0§EBBS§BH§20200520§20200520.ACCOUNT§20200520161733§ACCOUNT§',1016) from dual
                      
                      Typ=96 Len=66 CharacterSet=AL32UTF8: 30,c2,a7,45,42,42,53,c2,a7,42,48,c2,a7,32,30,32,30,30,35,32,30,c2,a7,32,30,32,30,30,35,32,30,2e,41,43,43,4f,55,4e,54,c2,a7,32,30,32,30,30,35,32,30,31,36,31,37,33,33,c2,a7,41,43,43,4f,55,4e,54,c2,a7
                      

                       

                      As you can see the character § appears as c2a7 in UTF-8

                      If your data look the same the the load was successful and you have a problem with the client software that displays the data.

                      • 8. Re: Unable to insert special symbols in table
                        Hub Tijhuis

                        You are dealing with 3 environments that can use different characters sets  (probably windows to create your  text file,  the unix server and the database).
                        In addition every move from one environment to another can take into account that difference  and do an automatic conversion  (or not) and  every tool  you use to view the data can do that conversion or not. That can be very confusing.

                         

                        In my experience the problem is in the transfer from pc to unix.  So check the character set you have on your pc and on your unix-server and  use for example the iconv utility on the unix to convert your text file from pc format tot unix-format (for example an iso-format to utft8.) 
                        (if you produce a text file with utl-file , convert the other way before  transferring it to the pc).

                         

                        You can also find a tool on your pc that can create a textfile in the Unix-charactersset or convert it to Unix-charactersset before sending it to the unix. 

                         

                        There may als be issues with the linefeeds  (check unix2dos and dos2unix commands)

                         

                        With the UTF8 characterset in the database I expect no trouble with using utl-file to read  from a file on the Unix that is made up in the Unix-characterset. 

                         

                        (and if you don't use utl-file but for example an 'external table' you problaby you wil have to convert to the characterset of the database)

                        1 person found this helpful
                        • 9. Re: Unable to insert special symbols in table
                          580988

                          Hi,

                           

                          I saved the file in UTF8 without BOM in Notepad++.

                           

                          I transferred the file via the WinSCP tool in the Binary mode.

                           

                          Character set in the UNIX server:

                          rcmsadm@hklpdurcm001[~] $ echo $LANG

                          en_US.UTF-8

                           

                          Character set in the DB:

                          select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

                          Output:

                          NLS_CHARACTERSET UTF8

                           

                          Now in my SQL developer when i run the above anonymous block i can see the special symbol correctly. However the Interface team which sends this file to us is sending without the same encoding so it is again failing to read the special symbol. So what could be done here so that the file sent by interface team is read correctly irrespective of what format they are encoding the file with. Any help would be highly appreciated.

                           

                          Thanks

                          Gautam

                          • 10. Re: Unable to insert special symbols in table
                            Solomon Yakobson

                            And what this has to do with Oracle? Use iconv OS command to convert file from whatever character set you get from windows to utf-8.

                             

                            SY.

                            1 person found this helpful
                            • 11. Re: Unable to insert special symbols in table
                              Solomon Yakobson

                              Another possibility would be using UTL_FILE.GET_RAW and then using UTL_RAW.CONVERT + UTL_RAW.CAST_TO_VARCHAR2.

                               

                              SY.

                              1 person found this helpful
                              • 12. Re: Unable to insert special symbols in table
                                Hub Tijhuis

                                If you want an interface without character-set encoding problems don't use  characters with an ascii value > 125.
                                What happens if you try to convert it yourself ?

                                 

                                dbms_output.put_line(replace(f_line,substr(f_line,-1,1),'§'));


                                If it works the conversion would depend on the format that the last character is always the seperator.

                                • 13. Re: Unable to insert special symbols in table
                                  Mike Kutz

                                  Why are you not using an EXTERNAL TABLE?

                                  • 14. Re: Unable to insert special symbols in table
                                    jaramill

                                    You also failed to give us one piece of important information, from the link on --> Re: 2. How do I ask a question on the forums? , #5

                                     

                                    5) Database Version and IDE Version
                                    Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.

                                     

                                    If you're not sure what it is you can do the following:

                                    select * from v$version;

                                    in an SQL*Plus session and paste the results.

                                     

                                    1 2 Previous Next