This discussion is archived
10 Replies Latest reply: Jan 22, 2013 3:39 AM by stefan nebesnak RSS

Need to find out extended ASCII characters in database

yadala Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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)

Legend

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