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?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
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 dbaCommented:
nope, you can technically store chinese in ansi tables
it would look like jiberish, but it's possible
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

marrowyungSenior Technical architecture (Data)Author Commented:
so no need any specify setting on DB instance level or table level ?
Mark GeerlingsDatabase AdministratorCommented:
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.
slightwv (䄆 Netminder) 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 dbaCommented:
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
slightwv (䄆 Netminder) 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 .
slightwv (䄆 Netminder) 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.