SP2-0552: Bind variable "AMP" not declared.

[teky]

I am trying to update a row in a table with the following text:

You must be an active participant for gettin S&P offer, you'll be asked to switch to S&P Service when
you enroll in the S&P 11¢ plan.

As soon as I enter the sql command, I get a prompt
"Enter value for amp: ".
I type in &amp.

But I get the following error "SP2-0552: Bind variable "AMP" not declared." after entring the values.

Can anybody please help me with this.

Teky.

 

[karluk]

You are getting this error because Oracle treats "&" as a special character. The best workaround that I've seen to the problem is to use the "chr(38)" function to generate the ampersand, then buid the desired string with the concatenation function. With this method, your insert string would become

'You must be an active participant for gettin S'
|| chr(38)
|| 'P offer, you'll be asked to switch to S'
|| chr(38)
|| 'P Service when you enroll in the S'
|| chr(38)
|| 'P 11¢ plan.'

 

[sem]

You may also change & symbol to any unused, e.g. ` by
SET DEFINE `
In this case you'll have to use it in your script instead of &.