Simple PL/SQL script to remove the unwanted non-ascii characters from oracle or 11i database.
This is an oracle function to remove extended ASCII code
Some of the fields are copied and pasted into oracle applications that comes with non-ascii characters.
In such situation, the concurrent program fails and tracing the failure is really challenging issue.
These characters are not visible while review. In some cases, data migration from sql server produces \0 (null) characters.
Here is plsql script that removes unwanted non-ascii characters from the data fields. Tested and working in 11i applications and oracle 10g database.
RETURN VARCHAR2
IS
v_TMP VARCHAR2(32767);
v_CLEAN VARCHAR2(32767);
v_CHAR VARCHAR2(3);
v_ERR VARCHAR2(32767);
BEGIN
FND_FILE.PUT_LINE(fnd_file.output,‘Entered String :: ‘ ” P_TXT);
FOR i IN 1 .. LENGTH(P_TXT)
LOOP
v_CHAR := SUBSTR (P_TXT, i, 1);
IF (ASCII (v_CHAR) BETWEEN 32 AND 127)
OR
(ASCII (v_CHAR) IN (9, 10, 13))
THEN
v_CLEAN := v_CLEAN ” v_CHAR;
ELSE
v_ERR := v_ERR ” v_CHAR;
END IF;
END LOOP;
IF LENGTH (v_CLEAN) != LENGTH (p_TXT) THEN
FND_FILE.PUT_LINE(fnd_file.output,‘Removed ‘” TO_CHAR(LENGTH(P_TXT) – LENGTH(v_CLEAN)) ” ‘ Characters’);
END IF;
FND_FILE.PUT_LINE(fnd_file.output,‘Modified String :: ‘ ” P_TXT);
RETURN v_CLEAN;
END REMOVE_NONASCII;
/
Courtesy, Copyright & thanks to my friend ashish
Comments
No comments yet.