Fwd: quick pl/sql question

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_at_kibeha.dk
_at_kibeha <http://twitter.com/kibeha>

On Thu, Oct 8, 2015 at 7:08 PM, Sweetser, Joe <JSweetser_at_icat.com> 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.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l