PL/SQL Special Character & literal???? 1

[bgreen]

I have this variable that I want to be a constant CHAr or VARCHAR2 doesnn't really matter. Like the following:

Init CONSTANT VARCHAR2(60) := "/eE/e&l0o2a7C/e(10U/e(s0p11h0s3b4099T/e&a5L/e&f0s300y4x1S";

When I declare it like this it causes a promt to ask for a user variable. How do I make this variable work in pl/sql?
The value:

/eE/e&l0o2a7C/e(10U/e(s0p11h0s3b4099T/e&a5L/e&f0s300y4x1S

 

[djbjr]

I think all you have to do is change the define to a different character and then switch it back at the end.

i.e.

SET DEFINE % (or whatever sybol you want to replace & with)

Let me know if that works for you

 

[nagornyi]

I think the prompt for user input is caused by the & character. So try replacing it by chr(38) -- that is ASCII code for it.

Init CONSTANT VARCHAR2(60):=
"/eE/e[b]"||chr(38)||"[/b]l0o2a7C/e(10U/e(s0p11h0s3b4099T/e[b]"||chr(38)||"[/b]a5L/e[b]"||chr(38)||"[/b]f0s300y4x1S";

 

[sem]

If yo don't have "unused" character you may SET DEFINE OFF

Regards, Dima

http://seminihin.narod.ru

 

[bgreen]

Here's another question.

I have two variables:

ESC CONSTANT VARCHAR2(1) := chr(27);
PrettyFont CONSTANT VArCHAR2(25) := '/e(s1p12v1s3b4197T';

I want to do a replace for any occurence of /e with ESC.

 

[carp]

Well, if you want to replace, I would use REPLACE!

SQL> declare
  2  ESC CONSTANT VARCHAR2(1) := chr(27);
  3  PrettyFont CONSTANT VArCHAR2(25) := '/e(s1p12v1s3b4197T';
  4  begin
  5  dbms_output.put_line(prettyfont);
  6  dbms_output.put_line(REPLACE(prettyfont,'/e',ESC));
  7  end;
  8  /
/e(s1p12v1s3b4197T
?(s1p12v1s3b4197T

PL/SQL procedure successfully completed.

 

[bgreen]

Thanks for all the help. All your suggestions work!