You’re right. I typically work with cursors and loops. It will work in that
instance.
Jeremy
From: Kim Berg Hansen [mailto:kibeha@xxxxxxxxx]
Sent: Thursday, October 08, 2015 1:40 PM
To: Sheehan, Jeremy
Cc: JSweetser@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: quick pl/sql question
Jeremy,
:1 is a bind variable in the SQL statement.
If you surround it with quotes the SQL_Stmt string will contain ':1' and that
will be the literal string :1 and not the content of the bind variable.
Regards
Kim Berg Hansen
http://www.kibeha.dk
kibeha@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>
@kibeha<http://twitter.com/kibeha>
On Thu, Oct 8, 2015 at 7:29 PM, Sheehan, Jeremy
<JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx<mailto:JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx>>
wrote:
I would put in some quotes. This should interpret the space as a part of the
string.
SQL_Stmt := 'DELETE FROM renewal_expacc_data WHERE policy_num =
'||chr(39)||:1||chr(39) ;
Thanks,
Jeremy
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Sweetser, Joe
Sent: Thursday, October 08, 2015 1:09 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: quick pl/sql question
This is an EXTERNAL email. Exercise caution. DO NOT open attachments or click
links from unknown senders or unexpected email.
________________________________
(at least, I hope it's quick!)
I have a program that has these lines in it:
SQL_Stmt := 'DELETE FROM renewal_expacc_data WHERE policy_num = :1'; EXECUTE
IMMEDIATE SQL_Stmt USING stage_data_rec.policy_num;
Policy_num is a character string. Everything seems to work fine unless there
is a space in policy_num. Am I missing something obvious? Is there an easy
way to quote the string for a bind variable that may contain spaces? Should I
even have to do that??
Thanks in advance for any ideas/suggestions.
-joe
Confidentiality Note: This message contains information that may be
confidential and/or privileged. If you are not the intended recipient, you
should not use, copy, disclose, distribute or take any action based on this
message. If you have received this message in error, please advise the sender
immediately by reply email and delete this message. Although ICAT, Underwriters
at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does
not guarantee that either are virus-free and accepts no liability for any
damage sustained as a result of viruses. Thank you.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
