sesh2002
asked on
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')
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
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')
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Clarification to my previous comment: you will have to modify the glogin.sql file on every client machine.
ASKER
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.
ASKER
Sorry markgeer, your answer also worked, but it goes to one only. Yoren, read my earlier comment and pleave give me an answer - sesh2002
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?