Very likely you need to review your business rules about whether there is
supposed to be a distinction between all uppercase, all lowercase, and mixed
case of each column constrained to be unique. (The answer might vary by
column.)
For columns where the business rule considers them to all be identical, ie.
ABC=aBC=AbC=ABc=abC=Abc=abc, then as an operational matter you should
probably pick a case, cleanse your data, prevent cases from re-appearing,
and strip any upper or lower function calls used to establish identity in
comparisons.
From the delete when UPPER(stage) matches, it would APPEAR that storage of
ALLCAPS was intended. Without implementing a functional index on the
incumbent column value you are likely causing extra work for your new
equijoin. Finding where the insert from staging after the delete takes place
or not should point you at the code that fails to uppercase the staging
data. Whether there is an opportunity to massage all the staging data for
allowable and desired transforms BEFORE the "possibly delete existing, then
insert new" flow of the afferent leg is reached is a question about your
processing flow.
In the long haul accommodations to dirty data tend to be more expensive than
cleaning data on the inbound side, but the bigger and faster the world
becomes the more you have to make sure that cleaning along the way is cheap.
If there is a one-time read of an external file into the database with
deterministic single column references of the needed cleaning that is
probably the best place to clean. (This will remain true even if Oracle has
optimized some internal functions to still utilize indexes with at the
release level you've reached.)
I know it is considered antique in some quarters to rely on the afferent leg
to transform center to efferent leg method of structured analysis, but in my
experience it will always produce at least a tie for the best result on
performance and makes considerations of correctness much more simple.
Good luck,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Sweetser, Joe
Sent: Friday, October 09, 2015 9:48 AM
To: Sweetser, Joe; oracle-l@xxxxxxxxxxxxx
Subject: RE: quick pl/sql question (solved...or DBA has head removed from
ass with no long term, negative impacts)
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
--
//www.freelists.org/webpage/oracle-l
