| How special characters got inserted into DB. [message #589635] |
Tue, 09 July 2013 05:27  |
 |
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Hi,
We are using Release 11.2.0.3.0 of Oracle.
I am having below special characters inserted into one of my columns, how this value got inserted, (what is the source) i need to track it down.
We dont have any audit trigger on this table to track one level below. As per JAVA guys this is uploaded through a file and the file is having well defined characters and no special characters for this column value also they uploaded the file again but its now going fine with no such special characters. So they put it on DBA's to find how special characters came into database? So is there any way out for this analysis? Please suggest.
Again the editor is not recognising all the characters , so i got the ASCII value for each of the characters in the string, its as below.
String - ‡Mw‹O--ggÑ ÆÔéÓÞ³ µmT¤O ˆÓ`ôiyïÎ!Ž
ASCII character is : ‡ ASCII Value Is : 14844065
ASCII character is : ‹ ASCII Value Is : 14844089
ASCII character is : -- ASCII Value Is : 14844052
ASCII character is : ASCII Value Is : 49793
ASCII character is : Ñ ASCII Value Is : 50065
ASCII character is : ASCII Value Is : 49793
ASCII character is : _ ASCII Value Is : 31
ASCII character is : Æ ASCII Value Is : 50054
ASCII character is : Ô ASCII Value Is : 50068
ASCII character is : é ASCII Value Is : 50089
ASCII character is : Ó ASCII Value Is : 50067
ASCII character is : Þ ASCII Value Is : 50078
ASCII character is : ³ ASCII Value Is : 49843
ASCII character is : _ ASCII Value Is : 20
ASCII character is : _ ASCII Value Is : 28
ASCII character is : µ ASCII Value Is : 49845
ASCII character is : _ ASCII Value Is : 16
ASCII character is : ASCII Value Is : 127
ASCII character is : ¤ ASCII Value Is : 49828
ASCII character is : _ ASCII Value Is : 28
ASCII character is : ˆ ASCII Value Is : 52102
ASCII character is : Ó ASCII Value Is : 50067
ASCII character is : _ ASCII Value Is : 22
ASCII character is : _ ASCII Value Is : 16
ASCII character is : ô ASCII Value Is : 50100
ASCII character is : ï ASCII Value Is : 50095
ASCII character is : Î ASCII Value Is : 50062
ASCII character is : Ž ASCII Value Is : 50621
|
|
|
|
|
|
|
|
|
|
| Re: How special characters got inserted into DB. [message #589878 is a reply to message #589792] |
Thu, 11 July 2013 10:41  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
VIP2013 wrote on Thu, 11 July 2013 01:10That field is getting updated through a value which comes from UI drop down list
Can you clarify here, do you mean the special characters being inserted into the DB table column are the values which get populated in the UI drop down select list?
If yes, then whichever browser the UI is based upon is rich enough to understand almost any special character. So in the drop down list you can very well see the characters the way they are. However, when inserted into the DB, it won't be recognized by Oracle(depending on the DB characterset) and will show you as unrecognized characters.
|
|
|
|
|
|
| Re: How special characters got inserted into DB. [message #589888 is a reply to message #589882] |
Thu, 11 July 2013 11:41 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Well, in your case I don't know how to find the source for the special characters. Without any AUDIT/TRIGGER or any sort of information logging, can't help remotely.
However, going forward, if you want to put a check on special characters from being inserted into the column, you can put a check constraint on the column in this way:-
check ( NOT REGEXP_LIKE (col_name, '^[ [:alnum:]_''\.\-]+$'))
The above check should only allow the alphanumeric characters, anything else it would restrict.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
