Hi Joe
Do you get an error? Or does it just not delete what you expect?
Is policy_num a VARCHAR2 both in renewal_expacc_data and in stage_data_rec?
If it is VARCHAR2 in both the table and your record type, then it should
work. When you use a bind variable in the dynamic statement, "escaping"
spaces is not relevant.
If either one of them actually is a NUMBER and you sometimes get '123 456'
with space in it, you'll get error "invalid number" from implicit
conversion one way, or you'll not get the delete you expect from implicit
conversion the other way.
But why is that a dynamic statement and not just:
DELETE FROM renewal_expacc_data r WHERE r.policy_num =
stage_data_rec.policy_num;
That would be my preferred way rather than dynamic SQL. (But it won't make
a difference concerning implicit conversions if either of the policy_nums
are actually NUMBER datatype.
Regards
Kim Berg Hansen
http://www.kibeha.dk
kibeha@xxxxxxxxx
@kibeha <http://twitter.com/kibeha>
On Thu, Oct 8, 2015 at 7:08 PM, Sweetser, Joe <JSweetser@xxxxxxxx> wrote:
(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
