Wednesday 21 April 2021

Insert '&' into a oracle table

By default, SQL*Plus treats the ampersand character (&) as a substitution variable prefix. It prompts for user input and replaces the variable with the entered value in the SQL statement. However, sometimes you may want to use the ampersand character as a literal character in your SQL statement, without triggering substitution.

To disable the substitution variable feature and treat the ampersand character as a literal, you can use the "SET DEFINE OFF" command. Once this command is executed, SQL*Plus will not prompt for variable substitution when encountering an ampersand.


SELECT 'This is & just a sample' FROM dual;

With "SET DEFINE OFF" enabled, the above query would simply return the string 'This is & just a sample', without prompting for substitution.

No comments:

Post a Comment