DROP TABLE table_1;
CREATE TABLE table_1(data VARCHAR2(10));
INSERT INTO table_1 VALUES(CHR(1)||'ABC');
INSERT INTO table_1 VALUES('ABC'||CHR(25));
INSERT INTO table_1 VALUES(CHR(25)||'@'||CHR(30));
INSERT INTO table_1 VALUES(CHR(25)||'@'||CHR(31));
INSERT INTO table_1 VALUES('ABC');
COMMIT;
VARIABLE npc VARCHAR2(33);
VARIABLE np0 VARCHAR2(33);
VARIABLE np VARCHAR2(33);
BEGIN
:npc := '';
:np0 := '';
:np := '';
FOR i IN 0 .. 31
LOOP
:npc := :npc || CHR(i);
:np0 := :np0 || CHR(0);
END LOOP;
:np := '@' || :npc;
END;
/
COLUMN data FORMAT A10
COLUMN dump FORMAT A30
SELECT ROWID
, data
, DUMP(data) dump
, LENGTH(data) - LENGTH(TRANSLATE(data, :np, '@')) numer_of_np_chars
, INSTR(TRANSLATE(data, :npc, :np0), CHR(0)) first_position
FROM table_1
WHERE TRANSLATE(data, :np, '@') <> data
/
It's not for unicode. FBI could be used as well.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Robson, Peter wrote:
> Some people have requested this code, so I thought you might as well all
> have the chance to pick it to bits... Its a function called BAD_ASCII, and
> it hunts out for any ascii characters with an ascii value of less than 32 in
> a specified field. (Acknowledgments to my colleague Keith Holmes for help
> with this code.)
>
> Use it as follows:
>
> Where a field called DATA in a table TABLE_1 may contain an ascci character
> with a value less than 32 (ie a non-printing character), the following SQL
> will find the row in question:
>
> select rowid,DATA,dump(DATA) from TABLE_1
> where BAD_ASCII(DATA) > 0;
>
> You could use the PK of the table instead of rowid, of course. You will also
> note that I select the DATA field in both normal and ascii 'dump' mode, the
> better to locate where the corruption is located.
>
> peter
> edinburgh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: Vladimir.Begun_at_oracle.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Oct 10 2003 - 17:04:24 CDT
