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

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sweetser, Joe Sent: Thursday, October 8, 2015 11:09 AM To: oracle-l_at_freelists.org
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.
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 09 2015 - 15:47:49 CEST