Re: passing parameters to sql (i'm puzzeled)
Date: Tue, 8 Oct 2002 20:48:44 +0200
Message-ID: <anv9pg$jlh$1_at_reader12.wxs.nl>
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You are fooling us.
I assume that you have been running these scripts several times.
Don't forget that once a variable is assigned a value, it keeps that =
value until it is assigned another value.
Add
undefine 1
undefine 2
undefine 3
after your sql-statement
(I have tried this in oracle9i release 9.2.0.1)
If I run this script (using single quotes)
_at_test ''1'' ''2'' ''3''
i get this result
old 1: select '&1' new 1: select '' old 2: , '&2' new 2: , '1''' old 3: , '&3' new 3: , ''
' '1''' '
- ------ -
1'
This happens because you are using single quotes in your query and =
variables
The resulting query looks like this
select '' , '1''' , ''
from dual
As you can see everything passed between single quotes or without quotes = is considered a parameter
_at_test 1 '2 2' 3
old 1: select '&1' new 1: select '1' old 2: , '&2' new 2: , '2 2' old 3: , '&3' new 3: , '3'
'1' '22' '3'
--- --------- --- 1 2 2 3
Even double quotes works fine
_at_test 1 "2 2" 3
old 1: select '&1' new 1: select '1' old 2: , '&2' new 2: , '2 2' old 3: , '&3' new 3: , '3'
'1' '22' '3'
--- --------- --- 1 2 2 3
Please try again after you added the undefine's to your script and show = us the results
"rob" <rob_at_dsdelft.nl> wrote in message =
news:anut7t$qea$1_at_news.tudelft.nl...
> I've encountered the following problem. On one client the delimiter =
for
> passing parameters is double-quote and on the other (Dutch W2000) it =
has to
> be a single-qoute. Does anyone have an idee what setting is =
responsible for
> this behaviour? >=20 >=20 > I have two W2000 clients oracle 8.1.7. > One is a Dutch version. > I have a sql script test.sql > select '&1' > , '&2' > , '&3' > from dual; >=20 > On US Windows: > c:\sqlplus un/pw _at_test.sql "1" "2 2" "3" > old 1: select '&1' > new 1: select '1' > old 2: ,'&2' > new 2: ,'1 2' > old 3: ,'&3' > new 3: ,'3' >=20 > ' '12 ' > - --- - > 1 1 2 3 >=20 >=20 > On Dutch Windows: > c:\sqlplus un/pw _at_test.sql "1" "2 2" "3" > oud 1: select '&1' > nieuw 1: select '1' > oud 2: ,'&2' > nieuw 2: ,'2' > oud 3: ,'&3' > nieuw 3: ,'2' >=20 > ' ' ' > - - - > 1 2 2 >=20 >=20 > Regards, > Rob >=20 >=20
------=_NextPart_000_005D_01C26F0C.17BDF190
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; charset=3Diso-8859-1" =http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.3315.2870" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DCourier size=3D2>You are fooling us.</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>I assume that you have been running =these scripts=20
several times.</FONT></DIV>
<DIV><FONT face=3DCourier size=3D2>Don't forget that once a variable is = assigned a=20
value, it keeps that value until it is assigned another = value.</FONT></DIV>
<DIV><FONT face=3DCourier size=3D2> <DIV><FONT face=3DCourier size=3D2>Add</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>undefine 1</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>undefine 2</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>undefine 3</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>after your sql-statement</FONT></DIV> <DIV> </DIV> <DIV>(I have tried this in oracle9i release 9.2.0.1)</DIV> <DIV> </DIV></FONT></DIV> <DIV><FONT face=3DCourier size=3D2>If I run this script (using =single=20
quotes)</FONT></DIV>
<DIV><FONT face=3DCourier size=3D2>_at_test ''1'' ''2'' ''3''</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>i get this result</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>old 1: select=20, '&3'<BR>new 3:=20 , ''</FONT></DIV>
'&1'<BR>new 1: select ''<BR>old 2:=20
, '&2'<BR>new 2:=20 , '1'''<BR>old 3:=20
<DIV> </DIV> <DIV><FONT face=3DCourier size=3D2>' '1''' '<BR>- ------ = -<BR> =20
1'</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DCourier size=3D2>This happens because you are using = single quotes=20
in your query and variables</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>The resulting query looks like = this</FONT></DIV>
<DIV><FONT face=3DCourier size=3D2>select ''</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>, =20
'1'''</FONT></DIV>
<DIV><FONT face=3DCourier size=3D2>, =
''</FONT></DIV>
<DIV><FONT face=3DCourier size=3D2>from dual</FONT></DIV> <DIV><FONT face=3DCourier size=3D2>As you can see everything passed = between single=20
quotes or without quotes is considered a parameter</FONT></DIV> <DIV> </DIV>
<DIV><FONT face=3DCourier size=3D2>_at_test 1 '2 2' 3<BR>old 1: = select=20
'&1'<BR>new 1: select '1'<BR>old 2:=20
, '&2'<BR>new 2:=20 , '2 2'<BR>old 3:=20 , '&3'<BR>new 3:=20 , '3'</FONT></DIV><DIV> </DIV>
<DIV><FONT face=3DCourier size=3D2>'1' =
'22' '3'<BR>---=20
--------- ---<BR>1 2 2 =20 3</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DCourier size=3D2>Even double quotes works = fine</FONT></DIV>
<DIV><FONT face=3DCourier size=3D2></FONT> </DIV> <DIV><FONT face=3DCourier size=3D2>_at_test 1 "2 2" 3<BR>old 1: = select=20
'&1'<BR>new 1: select '1'<BR>old 2:=20
, '&2'<BR>new 2:=20 , '2 2'<BR>old 3:=20 , '&3'<BR>new 3:=20 , '3'</FONT></DIV><DIV> </DIV>
<DIV><FONT face=3DCourier size=3D2>'1' =
'22' '3'<BR>---=20
--------- ---<BR>1 2 2 =20 3</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DCourier size=3D2>Please try again after you added the = undefine's=20
to your script and show us the results</FONT></DIV> <DIV> </DIV>
<DIV><FONT face=3DCourier size=3D2>"rob" <</FONT><A=20 href=3D"mailto:rob_at_dsdelft.nl"><FONT face=3DCourier=20 size=3D2>rob_at_dsdelft.nl</FONT></A><FONT face=3DCourier size=3D2>> = wrote in message=20
</FONT><A href=3D"news:anut7t$qea$1_at_news.tudelft.nl"><FONT =
face=3DCourier=20 size=3D2>news:anut7t$qea$1_at_news.tudelft.nl</FONT></A><FONT = face=3DCourier=20 size=3D2>...</FONT></DIV><FONT face=3DCourier size=3D2>> I've =encountered the=20
following problem. On one client the delimiter for<BR>> passing = parameters is=20
double-quote and on the other (Dutch W2000) it has to<BR>> be a = single-qoute.=20
Does anyone have an idee what setting is responsible for<BR>> this=20 behaviour?<BR>> <BR>> <BR>> I have two W2000 clients oracle=20 8.1.7.<BR>> One is a Dutch version.<BR>> I have a sql script=20 test.sql<BR>> select '&1'<BR>> , =
'&2'<BR>> , '&3'<BR>> from=20
dual;<BR>> <BR>> On US Windows:<BR>> c:\sqlplus un/pw _at_test.sql =
"1" "2=20
2" "3"<BR>> old 1: select '&1'<BR>> =
new 1:=20
select '1'<BR>> old 2: ,'&2'<BR>> new =
2: ,'1=20
2'<BR>> old 3: ,'&3'<BR>> new 3: =
,'3'<BR>>=20
<BR>> ' '12 '<BR>> - --- -<BR>> 1 1 2 3<BR>> <BR>> =
<BR>> On=20
Dutch Windows:<BR>> c:\sqlplus un/pw _at_test.sql "1" "2 2" "3"<BR>> =
oud 1:=20
select '&1'<BR>> nieuw 1: select '1'<BR>> oud 2: =
,'&2'<BR>>=20
nieuw 2: ,'2'<BR>> oud 3: ,'&3'<BR>> nieuw 3: ,'2'<BR>> =
<BR>> '=20
' '<BR>> - - -<BR>> 1 2 2<BR>> <BR>> <BR>> =
Regards,<BR>>=20
Rob<BR>> <BR>> </FONT></BODY></HTML>
------=_NextPart_000_005D_01C26F0C.17BDF190-- Received on Tue Oct 08 2002 - 20:48:44 CEST