Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8563
  • Last Modified:

escape characters

I am using oracle 8.1.6 on win NT4. I want to know escape characters for all keyboard
characters. What I mean is insert into emp(empno,ename) values(1111,'br'o'neal'); will fail.
But insert into emp(empno,ename) values(1111,'br''o''neal'); will succeed.
I know select ascii('&') from dual;
ascii('&')
----------
   38
insert into emp(empno,ename) values(2222,'P' || chr(38) || 'G'); works.
Also, rpad, lpad or both can be used to get around the problem.
So, ' is escape character for '. I want these type of escape characters for all keyboard
characters. More important and urgent need is for hyphen('-') and ampersand('&'). Can we make
use of default or set escape character of SQL*Plus i.e. '\' etc. while selecting OR inserting.
If so how. Also, can we make it database based with a setting in parameter files so that it is
permanent and applies to all client windows and application programs that use oracle as
backend. Will it help if I use replace and translate functions to avoid prompting for value when inserting '&' and other such problems. -----sesh2002
0
sesh2002
Asked:
sesh2002
  • 2
  • 2
1 Solution
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, you can change the escape character in SQL*Plus and you can "set define off" if you want to, so SQL*Plus will not treat the ampersand (&) as a substitution variable marker.

No, you cannot do this in the database and have it apply to any/all clients.  Oracle client programs always over-ride database defaults if the value can be specified in both the server and the client.

Also these SQL*Plus settings only apply to SQL*Plus, but that is not the tool most people use for data entry and queries with an Oracle database.  Sure, developers and DBA's use it, but your users don't, do they?
0
 
yorenCommented:
Hi sesh,

The problems you describe with escape sequences and ampersands don't appear in non-sqlplus applications (when you use bind variables). Here's what to do for SQL*Plus:

Edit the global login file which is executed for all SQL*Plus sessions: [orahome]/sqlplus/admin/glogin.sql

In that file, add the command "set scan off". That will prevent any '&' prompting for users of sqlplus.

-Yuval
0
 
yorenCommented:
Clarification to my previous comment: you will have to modify the glogin.sql file on every client machine.
0
 
sesh2002Author Commented:
Dear Experts, I will give it to the one who gives me a escape character like ' for ' for as many characters as possible OR at least circumventing the problem using replace, translate functions and '\' etc.
0
 
sesh2002Author Commented:
Sorry markgeer, your answer also worked, but it goes to one only. Yoren, read my earlier comment and pleave give me an answer - sesh2002
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now