Often I want to include a carriage return or other character into my SQL statement. Single tics are especially confusing due to breaking them on and off to be effective. I now use the CHR() function to write single tics into my SQL. If I forget what integer is required in the CHR() function, I can use the script below to determine what integer I should use.
--------------------------------------------------------------------- -- Script Name: find_chr_val.sql -- Author: Karen Porter -- Date: February 20, 2002 -- Purpose: Determine integer required to display specific characters -- Explanation: CHR(34) returns the ASCII representation of that integer. -- IE: chr(34) displays " on my system. -- Limitations: -- 1) Run as system or user with select privilege on dba_objects and dba_source -- 2) Created on v188.8.131.52 of Oracle RDBMS -- 3) If searching for ', you must type twice. IE: '' -- Flow: -- 1) Find the specific integer to display the character you choose -- 2) Display all chr(integers) and their corresponding ASCII values --------------------------------------------------------------------- -- Adjust SQL Plus Settings set echo off clear screen set linesize 80 set feedback off set verify off -- Accept user input accept inpt char prompt 'Enter Character You Wish to Find CHR() Value For: ' -- Format Column Headings column valu format A12 heading 'Value Found' column chrctr format A13 heading 'For Character' -- Determine correct integer for character given -- LINE field from DBA_SOURCE where NAME=DBMS_UTILITY is queried -- but any table can be used as long as the field has > 256 values -- and those values are consecutive select lpad(line,12) as valu, lpad(chr(line),13) as chrctr from dba_source where name = 'DBMS_UTILITY' and line < 257 and chr(line) = '&inpt'; -- Accept User Input accept full char prompt 'Full Listing? (y/n): ' -- Adjust SQL Plus Settings clear screen set head off -- Prompt user to press enter because pause is being turned on select 'Press Enter to Continue or End...' from dual where upper('&full')='Y'; -- Adjust SQL Plus Settings set head on set pagesize 20 set pause on -- Format Column Headings column fullvalu format A60 heading '
Press Enter to Continue... ' -- Show a full listing of integers and corresponding chr() characters -- This time I used a rownum on dba_objects table -- Any table could be used as long as the table -- Has a minimum of 256 records -- The dashes will identify characters such as TAB or Carriage Return select rownum||' -'||chr(rownum)||'-' as fullvalu from dba_objects where rownum < 256 and upper('&full')='Y'; -- Adjust SQL Plus Settings set pause off set feedback on set verify on set echo on
Stephan B. writes: This is an interesting approach, but here's an easier way that uses the following anonymous PL/SQL block:
set serverout on size 1000000 begin dbms_output.put_line('ASCII codings on this database'); dbms_output.put_line('=============================='||CHR(10)); dbms_output.put_line('Ascii Character'); dbms_output.put_line('------- ---------'); for i in 1 .. 256 loop dbms_output.put_line(rpad(to_char(i, '099'), 7, '.')||': '||CHR(i)); end loop; end; /
The disadvantages are 1) all ascii characters are shown in one go (... but I do not care, because I can easily scroll back on my windows bar, be it in a Unix or NT environment); and 2) Okay, single character inquiries are not implemented (yet ;-).
The advantages are 1) I can run this as any user -- I do not need any specific db privilege (other than CREATE SESSION); 2) runs under any Oracle version (7.*, 8.0.*, 8.1.*, 9.0.*); 3) Representation of digits is correct regarding Unix notation; and 4) it's a quick way to test the full set of the current national character setting on your DB instance.
For More Information
- What do you think about this tip? E-mail the Editor at email@example.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.