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?
Any way to show a list of Oracle table name which has any column storing any language which is not ENGLISH?
e..g chinese?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so no need any specify setting on DB instance level or table level ?
That is correct.
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.
I want to compare data if I replicate the whole table to another DB and compare data.
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
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
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.
"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.
NLS_language is a setting on the client
the last part is most important
that part won't mention american
the last part is most important
that part won't mention american
ASKER
"the last part is most important"
I am sorry, which one is the last part?
see this :
I am sorry, which one is the last part?
see this :
>>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.
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.
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 .
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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','C
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.
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','C
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.
ASKER
tks all.
ASKER
I mean any quick way to check which table contain Chinese characters on oracle. not if the oracle allow chinese characters.