Inserting huge text with single quotes,ampersands...

[hemanr]

Hi Gurus,

I need to insert huge chunks of text into database fields.
These text blocks contain single quotes and ampersands.
How do I set an escape character for this?

Thanks!

[stecal]

You can mess with escaping single quotes with other single quotes (there can be up to four single quotes in a row and it looks confusing), or you can concatenate chr(39) and chr(38) for the single quote and ampersand (&). How do you get the ascii code for characters? Glad you asked.

1 declare
2 v_char varchar2(4);
3 begin
4 for i in 32..80 loop
5 select chr(i) into v_char from dual;
6 dbms_output.put_line(i||', '||v_char);
7 end loop;
8* end;

SQL> /
32,
33, !
34, "
35, #
36, $
37, %
38, &
39, '
40, (
41, )
42, *
43, +
44, ,
45, -
46, .
47, /

[hemanr]

Thanks Stecal!

Hey I have one more small problem. In my text I have Bullets.
These are appearing in the table as question marks '?'.

Any suggestions?

Thanks again!