Avatar of marrowyung
marrowyung
 asked on

list of table containing other language

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?
Oracle Database

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
SOLUTION
Mark Geerlings

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
Hi,

I mean any quick way to check which table contain Chinese characters on oracle. not if the oracle allow chinese characters.
SOLUTION
Geert G

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
so no need any specify setting on DB instance level or table level ?
Mark Geerlings

That is correct.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
marrowyung

ASKER
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.
slightwv (䄆 Netminder)

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
marrowyung

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Geert G

NLS_language is a setting on the client
the last part is most important
that part won't mention american
marrowyung

ASKER
"the last part is most important"

I am sorry, which one is the last part?

see this :

Oracel-settting.jpg
slightwv (䄆 Netminder)

>>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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
marrowyung

ASKER
"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 .
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
"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.
marrowyung

ASKER
tks all.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.