list of table containing other language

marrowyung
marrowyung used Ask the Experts™
on
hi.

Any way to show a list of Oracle table name which has any column storing any language which is not ENGLISH?

e..g chinese?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark GeerlingsDatabase Administrator
Commented:
No.  From the Oracle documentation: "NLS_LANGUAGE specifies the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT."

So, the NLS_LANGUAGE value is set at the database level.  This cannot be set at a schema, table or individual column level.  However, it is possible for an application to include data in a column (or in multiple columns) that is actually in a different language than the default language of the database.  You would have to check application code to see if your application does this.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Hi,

I mean any quick way to check which table contain Chinese characters on oracle. not if the oracle allow chinese characters.
Geert GOracle dba
Top Expert 2009
Commented:
nope, you can technically store chinese in ansi tables
it would look like jiberish, but it's possible
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
so no need any specify setting on DB instance level or table level ?
Mark GeerlingsDatabase Administrator

Commented:
That is correct.
marrowyungSenior Technical architecture (Data)

Author

Commented:
but any query for me to list out which table containing characters other than english?

I want to compare data if I replicate the whole table to another DB and compare data.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You would need to write a query that looked in every column for any character you didn't want.  That would be a VERY LONG running query and not really accurate.

There are many examples out on the web for these.  For example:
https://community.oracle.com/thread/2470289?start=0&tstart=0

The problem you will have is deciding EXACTLY what characters you want to allow.

For example is this considered "English":
https://en.wikipedia.org/wiki/Copyright_symbol
or this:
https://en.wikipedia.org/wiki/Trademark_symbol
marrowyungSenior Technical architecture (Data)

Author

Commented:
slightwv,

"and not really accurate."

why it is not accurate ? please share. you are referring to write a script to compare both side data?

what I mean is only found out the table containing chinese and I will use another comparison program to compare it.

I will try migrating data first then compare it.


I most like compare using
http://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation19.5/page/15/Compare-Tools


Today I found out that the Oracle NLS_language is American but it can store chinese. good! but in target MariaDB, once migrate using Ispirer, all chinese characters gone !

"The problem you will have is deciding EXACTLY what characters you want to allow.
"
traditional chinese.

"There are many examples out on the web for these.  For example:
https://community.oracle.com/thread/2470289?start=0&tstart=0"

this can't help me at all, I can't find which table containing Chinese characters and let me start converting it.
Geert GOracle dba
Top Expert 2009

Commented:
NLS_language is a setting on the client
the last part is most important
that part won't mention american
marrowyungSenior Technical architecture (Data)

Author

Commented:
"the last part is most important"

I am sorry, which one is the last part?

see this :

Oracel-settting.jpg
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>why it is not accurate ?

I already explained what I meant.
For example is this considered "English":
https://en.wikipedia.org/wiki/Copyright_symbol
or this:
https://en.wikipedia.org/wiki/Trademark_symbol 

To try and compile an accurate list of "English" characters will be a rather long process.

>>this can't help me at all, I can't find which table containing Chinese characters and let me start converting it.

Why does that not help?  It addresses the question asked here:   table name which has any column storing any language which is not ENGLISH?

As mentioned above there is nothing that automatically detects a non-English language.  You have to look for any specific data you want.

>>Today I found out that the Oracle NLS_language is American but it can store chinese.

I believe you are looking for a magic wand that doesn't exist.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"To try and compile an accurate list of "English" characters will be a rather long process."

but this doesn't mean it is not accurate but just long process, right?

"Why does that not help?  It addresses the question asked here:   table name which has any column storing any language which is not ENGLISH?"

sorry I am looking for table name containing chinese characters.

even for any other LANGUAGE then English, I don't see the link showing any query,  please share with me which query you found out is going to do that.

so I need the name of tables which contain that.

"I believe you are looking for a magic wand that doesn't exist."

I just confirm what other contributor (Geert G) said before and I confirm it is a yes.

"As mentioned above there is nothing that automatically detects a non-English language.  You have to look for any specific data you want."

you mean manually by eyes, right?

I may need to ask developer which tables containing chinese, this may be the way to go .
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>even for any other LANGUAGE then English, I don't see the link showing any query,

I posted a link that had a query that looked for columns that did not contain specific characters:
https://community.oracle.com/thread/2470289?start=0&tstart=0

You just need to modify that to look in ALL character columns in ALL tables.

There are examples out there:
https://www.experts-exchange.com/questions/28069987/identifying-columns-in-all-the-tables-with-a-specific-value.html#a39001854

You will have problems if you have LONG data types.

Just modify the DBMS_XMLGEN query to look for columns with a data type of:
CHAR,VARCHAR2,NCHAR,NVARCHAR2, etc...

like:

WHERE ...
and data_type in ('VARCHAR2','NVARCHAR2','CHAR','NCHAR','CLOB','NCLOB')

>>you mean manually by eyes, right?

No.  You can script it using a query similar to the one in the link I provided.  I've been trying to explain how difficult it will be to come up with the exhaustive list of "English" characters.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"I've been trying to explain how difficult it will be to come up with the exhaustive list of "English" characters."

yeah !

right now I can only get a list of table from developers and keep that list.

I've been working with Ispirer on that , their tools need some OS configuration to convert that well.

it is fine now by using their tools now.

tks.

"WHERE ...
and data_type in ('VARCHAR2','NVARCHAR2','CHAR','NCHAR','CLOB','NCLOB')"

I think this is the part make the result not that accurate. this is not going to sure that language is not English.

will read though the rest of the link later.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial