Home » SQL & PL/SQL » SQL & PL/SQL » How special characters got inserted into DB.
How special characters got inserted into DB. [message #589635] Tue, 09 July 2013 05:27 Go to next message
VIP2013
Messages: 84
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--ggсÆÔéÓÞ³µ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 #589638 is a reply to message #589635] Tue, 09 July 2013 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See the discussion there and come back after checking what is mentioned.

Regards
Michel
Re: How special characters got inserted into DB. [message #589792 is a reply to message #589638] Wed, 10 July 2013 14:40 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Yes , read the discussion. NLS_LANG is same. I think i have a bit different case.
We are having one special character string being inserted into one of the column and its too not repeating, i mean to say its not reproducible.That field is getting updated through a value which comes from UI drop down list, so it minimises the chances of manually entering special character into the column from client M/C. So just want to know how to track down towords the source which caused this? Can we have anything(log) in block level which will give us some hint?
Re: How special characters got inserted into DB. [message #589795 is a reply to message #589792] Wed, 10 July 2013 14:44 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
AUDIT the INSERT & UPDATE against this table.
Can you modify the table to add new DATE column for when DML occurs against records?
Re: How special characters got inserted into DB. [message #589878 is a reply to message #589792] Thu, 11 July 2013 10:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
Registered: May 2013
Location: World Wide on the Web
Senior Member
VIP2013 wrote on Thu, 11 July 2013 01:10
That 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 #589882 is a reply to message #589878] Thu, 11 July 2013 11:01 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
yes. But the drop down list itself fetching characters from a specific column from database to show in the list. Thats why i think its not possible, for the special characters being populated from UI as there is no manual entry possible.
Re: How special characters got inserted into DB. [message #589888 is a reply to message #589882] Thu, 11 July 2013 11:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
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.
Re: How special characters got inserted into DB. [message #590065 is a reply to message #589888] Sat, 13 July 2013 23:29 Go to previous messageGo to next message
manubatham20
Messages: 472
Registered: September 2010
Location: Champaign, IL
Senior Member

Okay, it's not a database solution, but setup meeting with Java guy, and during meeting validate their statement by verifying the modification date of the file which they uploaded. It must not be later than when the issue occurred, otherwise Java guy is culprit.

[Updated on: Sat, 13 July 2013 23:30]

Report message to a moderator

Re: How special characters got inserted into DB. [message #590649 is a reply to message #590065] Sun, 21 July 2013 01:14 Go to previous message
VIP2013
Messages: 84
Registered: June 2013
Member
Thanks all for all your help. Actually we are not able to reproduce the issue by any chance nor the user. So we close the issue by saying , it will be catered if comes next time. Thanks all.
Previous Topic: Find Difference
Next Topic: ORA-04080: trigger 'WRONG_UPDATE_WSDL' does not exist
Goto Forum:
  


Current Time: Mon Oct 20 18:26:16 CDT 2014

Total time taken to generate the page: 0.10222 seconds