We help IT Professionals succeed at work.

list of table containing other language

marrowyung
marrowyung asked
on
102 Views
Last Modified: 2019-03-07
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

Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

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

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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 .
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.