Treatment of ampersand in data in PL/SQL
Date: Fri, 22 Oct 1999 11:12:49 EDT
From: "john lee" johnlee321@hotmail.com
To: OraApps-L@cpa.qc.ca
Subject: SQL Question ...
Hi Everbody,
I have a doubt in Sql (Cursors) :
My Cursor is like this below :
cursor get_service_type(var3 varchar2) is
select
substr(description,1,30) service_type
from
pa_service_type_lov_v
where
code = var3;
.........
.........
insert into drm_legacy_data(fld0,fld1,fld2,fld3,fld4,fld5,
fld6,fld7,fld8,fld11)
values ('66',v_fund,v_org,v_project,v_appropriation,v_function,
v_task,v_service_type,v_award,l_msg);
.......
etc etc ...
The values in the service_type from the above query are of this type :
1. STATION BLD & STREET
2. BLDG & EQUIPMENT etc....
so my problem is how will the cursor fetch the values like this ...
See this query below :
select description from pa_service_type_lov_v
where code = 'STATION BLD & STREET';
If we compile this query then it will ask for Enter values for STREET :
My problem is I dont want this to happen what ever the value the cursor
picks it should fetch the same values i.e It should not ask for ENTER
values for STREET : ???
Any suggestions are valuable ...
Thanks,
John..
Date: Fri, 22 Oct 1999 11:32:01 -0400
From: "Maran, Ramanathan (Ramanathan)** CTR **" rammayan@lucent.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL Question ...
Hello,
You need to use 'STATION BLD'||CHR(38)|| 'STREET'
Regards
Maran
Date: Fri, 22 Oct 1999 10:56:41 -0500
From: "Kanakam, Venkat (c)" vkanakam@Carlson.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL Question ...
Hi,
The '&' is the default variable indicator(define) character in Sql*Plus. So
at any place if the SQL*PLUS sees a '&' it will ask for the value. You have
to set the define character in SQL*PLUS to any other character by using the
command:
Set define "any_character"
Then it won't care '&'
HTH
Venkat kanakam
Consultant
CIBER, Inc.
Date: Fri, 22 Oct 1999 14:40:27 EDT
From: "john lee" johnlee321@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: RE: SQL Question ...
Hi Venkat,
Thanks for your suggestion . I have done that by doing
SET DEFINE OFF
Thanks,
John..