10 Replies Latest reply: Jan 22, 2013 5:39 AM by stefan nebesnak RSS

    Need to find out extended ASCII characters in database

    yadala
      Hi All,
      I am looking for a query that can fetch list of all tables and columns where there is a extended ASCII character (from 128 to 256). Can any one help me?
      Regards
      Yadala
        • 1. Re: Need to find out extended ASCII characters in database
          sb92075
          yadala wrote:
          Hi All,
          I am looking for a query that can fetch list of all tables and columns where there is a extended ASCII character (from 128 to 256). Can any one help me?
          Regards
          Yadala
          remove all characters which have ASCII value 127 or lower;
          then any non-zero length column contain the higher value characters.
          • 2. Re: Need to find out extended ASCII characters in database
            yadala
            Thanks for the Help. I am getting qheries but confused which one to use. My requirement is
            We have an application that uses Oracle Databese to store meta data. Now we are upgrading the application and the newer version does not display extended ASCII. So now my task is to generate a report with various tables and columns that have Extended ASCII. I request some one to show how it could be done.

            Yadala
            • 3. Re: Need to find out extended ASCII characters in database
              sb92075
              yadala wrote:
              Thanks for the Help. I am getting qheries but confused which one to use. My requirement is
              We have an application that uses Oracle Databese to store meta data. Now we are upgrading the application and the newer version does not display extended ASCII. So now my task is to generate a report with various tables and columns that have Extended ASCII. I request some one to show how it could be done.
              post SQL that removes all ASCII values of 127 or lower from a column
              • 4. Re: Need to find out extended ASCII characters in database
                rp0428
                >
                I am looking for a query that can fetch list of all tables and columns where there is a extended ASCII character (from 128 to 256).
                >
                Well then you have a pretty good-sized problem. You need to examine your application to identify the tables it stores the data in. And you should be able to tell from your app what columns the data is stored in.

                If you app uses stored procedures to store data then examine those stored procedures.

                You don't need to try to search every table in the database since your app can't be storing data into all of them.

                A general purpose solution isn't very feasible either since data can be stored in object columns using user-defined types, xml columns and the like.

                You will have to write the code yourself. The table columns are in the xxx_TAB_COLS (DBA/ALL/USER) views.

                You can use the ASCII function (http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions007.htm#sthref1035) to get the ascii value of a character and see if it is greater than 127. But you will need to check every character in every string.

                As stated above you have likely bitten off more than you can chew and more than you need to do. Reduce the problem to the minimum.
                • 5. Re: Need to find out extended ASCII characters in database
                  Arild
                  I think you may want to have a look at this page:

                  http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch12scanner.htm#NLSPG485

                  The scanner is a DBA tool, so you will perhaps need to have him/her do this:
                  +"To use the Database Character Set Scanner, you must have DBA privileges for Oracle Database."+

                  Our guy at work used it to check for lossy data prior to a DB character set conversion (if I remember correctly).
                  The 0-127 range is US7ASCII, isn't it? There are some examples there, and there are blog entries if you google:

                  http://gavinsoorma.com/2011/07/changing-the-database-characterset-from-us7ascii-to-we8mswin1252/

                  Hopefully this is what you're after. If not, good luck :)
                  • 6. Re: Need to find out extended ASCII characters in database
                    stefan nebesnak
                    yadala wrote:
                    Hi All,
                    I am looking for a query that can fetch list of all tables and columns where there is a extended ASCII character (from 128 to 256). Can any one help me?
                    Regards
                    Yadala
                    This should match your requirement:
                    select t.TABLE_NAME, t.COLUMN_NAME from ALL_TAB_COLUMNS t
                    where length(asciistr(t.TABLE_NAME))!=length(t.TABLE_NAME)  
                    or length(asciistr(t.COLUMN_NAME))!=length(t.COLUMN_NAME);
                    The ASCIISTR function returns an ASCII version of the string in the database character set.
                    Non-ASCII characters are converted to the form \xxxx, where xxxx represents a UTF-16 code unit.

                    The CHR function is the opposite of the ASCII function. It returns the character based on the NUMBER code.

                    ASCII code 174
                    SQL> select CHR(174) from dual;
                     
                    CHR(174)
                    --------
                    Ž
                    
                    SQL> select ASCII(CHR(174)) from dual;
                     
                    ASCII(CHR(174))
                    ---------------
                                174
                     
                    SQL> select ASCIISTR(CHR(174)) from dual;
                     
                    ASCIISTR(CHR(174))
                    ------------------
                    \017D
                    ASCII code 74
                    SQL> select CHR(74) from dual;
                     
                    CHR(74)
                    -------
                    J
                     
                    SQL> select ASCII(CHR(74)) from dual;
                     
                    ASCII(CHR(74))
                    --------------
                                74
                     
                    SQL> select ASCIISTR(CHR(74)) from dual;
                     
                    ASCIISTR(CHR(74))
                    -----------------
                    J
                    • 7. Re: Need to find out extended ASCII characters in database
                      fac586
                      yadala wrote:
                      Thanks for the Help. I am getting qheries but confused which one to use. My requirement is
                      We have an application that uses Oracle Databese to store meta data. Now we are upgrading the application and the newer version does not display extended ASCII.
                      And has no one in your organisation questioned why—in 2012—a supposed application "upgrade" is introducing a restriction to 7-bit ASCII? Sounds like a distinct downgrade in fact. {noformat}*coff* Unicode coff{noformat}
                      • 8. Re: Need to find out extended ASCII characters in database
                        BrendanP
                        Doubt that is actually what OP meant, but if it were your code seems to work:
                        SQL> CREATE TABLE "A column" ("für Elise" VARCHAR2(1));
                        
                        Table created.
                        
                        SQL> CREATE TABLE "Für Elise" ("...a table" VARCHAR2(1));
                        
                        Table created.
                        
                        SQL> 
                        SQL> select t.TABLE_NAME, t.COLUMN_NAME from ALL_TAB_COLUMNS t
                          2  where length(asciistr(t.TABLE_NAME))!=length(t.TABLE_NAME)
                          3  or length(asciistr(t.COLUMN_NAME))!=length(t.COLUMN_NAME);
                        
                        TABLE_NAME                     COLUMN_NAME
                        ------------------------------ ------------------------------
                        A column                       für Elise
                        Für Elise                      ...a table
                        :)
                        • 9. Re: Need to find out extended ASCII characters in database
                          Nicosa-Oracle
                          yadala wrote:
                          Now we are upgrading the application and the newer version does not display extended ASCII.
                          Let's rephrase it :
                          The new application cannot do what the previous version did, so the database has to be corrected/kept from using non-US7ASCII characters ?
                          (o_0)

                          Isn't it just a problem in application parameters ?
                          Does the application official support team confirms that the new version is NOT capable of what the previous version was ?
                          Do you think the new version will be able to handle years on four digit ?
                          ;-)
                          • 10. Re: Need to find out extended ASCII characters in database
                            stefan nebesnak
                            BrendanP wrote:
                            Doubt that is actually what OP meant, but if it were your code seems to work:
                            Anyway, this technique is possible to apply on data as well. ( ͡° ͜ʖ ͡°)

                            (bud in this case, as Nicosa said, the problem is on the application side)