RE: quick pl/sql question (solved...or DBA has head removed from ass with no long term, negative impacts)

  • From: "Sweetser, Joe" <JSweetser@xxxxxxxx>
  • To: "Sweetser, Joe" <JSweetser@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 9 Oct 2015 13:47:49 +0000

First, thanks for all for the quick responses. I truly appreciate the
willingness of this list to jump in with ideas.

Second, I was on vacation/holiday/pto/<insert whatever term is current for time
off from work> yesterday and apologize for not responding earlier. There was an
error in an overnight job (unique constraint violated) that I saw in the
morning as was looking into. The basic flow/logic of the program is:

1. select records from staging table
2. see if the current record already has an entry in the destination table
3. if it does, delete the current record and insert the staging record
4. if it doesn't, insert the staging record

I tracked down the problem to a record that had spaces in the value being used
to delete the existing record and thought that was the problem. I couldn't
reproduce the behavior in some basic testing and sent my message to the list
looking for a quick fix (since I wasn't supposed to be working anyway). And
then I left the house. :)

It turns out the issue was a little different. There was a staging record that
had lower case alpha data in a record for the *second* time. The record
inserted correctly the first time months ago. When a change came through, this
particular record was not being deleted prior to the insert. I thought it was
a data issue. It wasn't. The pl/sql program does a select count(*) from the
destination table to see if it exists.

SELECT count(*) INTO count_star
FROM
<destination_table>
WHERE
column = UPPER(stage_data_rec.column);

That was the problem. It was comparing lower case to upper case and,
therefore, not deleting the record. A simple change to the where clause fixed
the issue.

WHERE
UPPER(column) = UPPER(stage_data_rec.column);

I inherited this program and am not sure why the UPPER function is being used.
I thought about taking it out completely but deemed it safer to just make
everything upper-case for the comparison.

-joe

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sweetser, Joe
Sent: Thursday, October 8, 2015 11:09 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: quick pl/sql question

(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


Other related posts: