RE: Inserting raw ascii into a varchar2 field

From: Nicoll, Iain (Calanais) <iain.nicoll_at_calanais.com>
Date: Fri, 11 Jan 2002 08:23:09 -0800
Message-ID: <F001.003EDC2A.20020111072024@fatcity.com>

I think the only sure non-printable characters are those of less than 32, on or above 32 and they may well be printable depending on the character set. I'm not sure what you're looking for example wise as it should just be a case of using chr(asciicode) to do the insert.

If you have more than 255 objects in your database you could try

select rownum-1 ascii_code, chr(rownum-1) character from dba_objects
where rownum < 256

to get an indication of what's printable

It's also arguable that BS, TAB, LF and CR (chr(8), chr(9), chr(10)? and chr(13)) are all printable but just have nothing seen.

To check whether your data contains unprintable characters you could try adapting the code below

SELECT *
FROM table_name
WHERE filed_name !=
TRANSLATE(field_name,CHR(0)||CHR(1)||CHR(2)||CHR(3)||CHR(4)||CHR(5)||CHR(6)| |

CHR(7)||CHR(8)||CHR(9)||CHR(10)||CHR(11)||CHR(12)||CHR(13)||
CHR(14)||CHR(15)||CHR(16)||CHR(17)||CHR(18)||CHR(19)||CHR(20)||
CHR(21)||CHR(22)||CHR(23)||CHR(24)||CHR(25)||CHR(26)||CHR(27)||
CHR(28)||CHR(29)||CHR(30)||CHR(31),'                                ')

Cheers

Iain Nicoll

-----Original Message-----
Sent: Thursday, January 10, 2002 9:10 PM To: Multiple recipients of list ORACLE-L

Hello,
Does anyone have an example of how to insert raw ascii into a varchar2 field?
For example,
CREATE TABLE LH_test

( col1 varchar2(10), 
  col2 varchar2(10), 
  col3 varchar2(10)  )  

 PCTFREE 0 PCTUSED 80 INITRANS 1 MAXTRANS 255 LOGGING  STORAGE(INITIAL 5M NEXT 5M MINEXTENTS 1  MAXEXTENTS 2147483645 PCTINCREASE 0
 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE REGDAT ;
commit ;
insert into lh_test values ( 'X'||chr(9), 'Y'||chr(A), 'Z'||chr(D) ) ;   

Why am I doing this? Because some non-printable ascii codes have been inserted in some fields and I am tasked with finding the bad data. I need a test bed to insure I can scan for ranges of ascii characters, and need a range of known ascii printable and non-printable characters in a test table.

The bad data can be in over 200 fields, so I need a broad tool; I'll gen the select statements after I have some test data to work with. Any suggestions or referrals are appreciated. Regards,
Linda
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Nicoll, Iain (Calanais)
  INET: iain.nicoll_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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 Jan 11 2002 - 10:23:09 CST