Solved

Oracle escape character

Posted on 2001-08-22
2
20,764 Views
Last Modified: 2011-08-18
Hi,

I am working with SQL plus on oracle database. When I try to insert a string that contains escape character like '&', it prompts for asking input for variable substitution as below:

Input query: "update table1 set column1 = 'AB&C'
Prompt: "Enter value for C:"

I try to add a slash before the '&' but it works the same.
Also, I try to blacket the '&' as '{&}' in the query and it gets success. But the field stores the '{}' at the same time. (e.g 'AB{&}C')

I would like to ask any good solutions to handle such escape characters when I input query in SQL plus ?

Thanks !

Ken
0
Comment
Question by:kencheng123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6416310
In SQL* do this

SQL> Set scan off

SQL> update dept set dname = 'A&BC';
5 rows updated.


Setting the scan off tells sqlplus that dont scan for variables in the statements.

Regards
U
0
 
LVL 1

Accepted Solution

by:
dsthas earned 100 total points
ID: 6416428
As UsamaMunir states can "set scan off" do the job for your update command.

An alternative is to use escape-sharacters, which makes it possible to be prompted for some parts of the SQL.
Enable the use of escape-characters (default it is off)
SQL> set escape on

The escape character is backslash ("\"), but you can change it if needed, fx "set escape #".

Regards
Hans Henrik
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 75
Fill Date time Field 12 45
looking for guidance on Oracle Sql Formatting standards 9 45
sql script run from cron does not work 46 30
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question