Home » SQL & PL/SQL » SQL & PL/SQL » Really need help... (10g)
icon5.gif  Really need help... [message #418083] Wed, 12 August 2009 21:51 Go to next message
grantx5
Messages: 3
Registered: August 2009
Location: Puyallup, WA
Junior Member
Ok, simple procedure, truncates a table, loops through a cursor, inserts reocrds as it loops, does a commit.

My problem: It ran once and did not place values into the target table as it should have. It inserted all the records but missed dollar values in one of the fields. Didn't miss the values on every record just on some.

Ran it again and it got everything right, 100%.

I am going crazy on this one. I can't replicate the problem, can only run the proc over and over.

How is it possible for a simple proc like this to miss a value in one field out of many?

Thanks for the assist.

Jim
Re: Really need help... [message #418085 is a reply to message #418083] Wed, 12 August 2009 21:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Where did the INSERT pick up the values? From another table? Using VALUES clauses? SQL*Loader from a file?

Could the INSERT script have been executed from an environment that interprets '$' differently? eg. Embedded SQL*Plus calls in a Unix Shell Script.

Ross Leishman
Re: Really need help... [message #418089 is a reply to message #418085] Wed, 12 August 2009 22:29 Go to previous messageGo to next message
grantx5
Messages: 3
Registered: August 2009
Location: Puyallup, WA
Junior Member
Yes, the records come from a different table. Very simple proc like;

declare
cursor c1
is
select invoice, invoice_dollar_amt, additional_charge
from invioce_table;
begin
for c in c1
loop
insert into another_table(invoice, invoice_dollar_amt, additional_charge)
Values(c.invoice, c.invoice_dollar_amt, c.additional_charge);
commit;
end loop;

end;

Nothing fancy, that's what worries me the most.

Here's more on it. The table being loaded contains invoice information, partiularly dollars billed to the customer. On a validation report one of my co-workers noticed that the total dollar value on one invoice was $700 off. The $700 dollar was an additional charge that should have been added to a field of its own and also added to the grand total, neither happened. The field contained a zero and total was off. I ran the proc again and, guess what, the $700 showed up.

I am missing something, can't believe the db would decide to not put the $700 in the field as directed but who knows.

[Updated on: Wed, 12 August 2009 22:32]

Report message to a moderator

Re: Really need help... [message #418091 is a reply to message #418083] Wed, 12 August 2009 22:39 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I am missing something, can't believe the db would decide to not put the $700 in the field as directed but who knows.
Oracle is too dumb to arbitrarily toss some values.
My initial assumption is that the root cause is PEBKAC.

DECLARE
  CURSOR c1 IS
    SELECT invoice,
           invoice_dollar_amt,
           additional_charge
    FROM   invioce_table;
BEGIN
  FOR c IN c1 LOOP
    INSERT INTO another_table
               (invoice,
                invoice_dollar_amt,
                additional_charge)
    VALUES     (c.invoice,
                c.invoice_dollar_amt,
                c.additional_charge);
    
    COMMIT;
  END LOOP;
END; 


COMMIT inside LOOP is a bad, bad, bad thing!

[Updated on: Wed, 12 August 2009 22:40]

Report message to a moderator

Re: Really need help... [message #418097 is a reply to message #418091] Wed, 12 August 2009 23:05 Go to previous messageGo to next message
grantx5
Messages: 3
Registered: August 2009
Location: Puyallup, WA
Junior Member
Ok, consider it gone. I'll move it out of the loop.

Help me understand the commit issue. Guess I didn't consider having it inside a loop as being bad.

Anyway, thanks a million. I'll make the change and see how things go.

Jim
Re: Really need help... [message #418113 is a reply to message #418097] Thu, 13 August 2009 01:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since your code example is obviousy simplified, did you perhaps leave out an exception handler?
Something like
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);

?
Re: Really need help... [message #418212 is a reply to message #418113] Thu, 13 August 2009 08:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The sort of behaviour you are describing is totally bizarre. Before looking for a totally bizarre explanation, look for a more rational one.

When a magician disappears through one door and instantly reappears through another 20m away, logic tells us he is using a double - it's not the same guy.

When the same report gets different results, it's because it is doing different things. Check things like:
- Search paths picking up programs in your local dirctory
- Connecting via different users with synonyms mapped differently or local copies of tables or local copies of procedures.
- Environment settings like $ORASID connecting to different databases.
- Triggers on the table affecting INSERT behaviour

Ross Leishman
Re: Really need help... [message #418214 is a reply to message #418089] Thu, 13 August 2009 08:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
As previously mentioned, it is clear that you have simplified your example, but can you tell us why you are not simple doing it all in a single insert?:

insert into another_table(invoice, invoice_dollar_amt, additional_charge)
select invoice, invoice_dollar_amt, additional_charge
from invioce_table
Re: Really need help... [message #418220 is a reply to message #418214] Thu, 13 August 2009 08:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Was anyone else doing any processing in the system at the same time?
Re: Really need help... [message #418249 is a reply to message #418220] Thu, 13 August 2009 12:08 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Thu, 13 August 2009 09:46
Was anyone else doing any processing in the system at the same time?


I'm going with this one. At the time of the cursor being opened, someone accidentally changed the value to $0 and then realized their error and changed it back before anyone would notice.
Re: Really need help... [message #418253 is a reply to message #418083] Thu, 13 August 2009 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>At the time of the cursor being opened, someone accidentally changed the value to $0 and then realized their error and changed it back before anyone would notice
LOGMINER could be used to determine if this hypothesis is valid.
Oracle's READ CONSISTENT view might be a complicating factor.
Until proven guilty beyond a reasonable doubt, I believe Oracle to be innocent.
Re: Really need help... [message #418267 is a reply to message #418253] Thu, 13 August 2009 13:49 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
BlackSwan wrote on Thu, 13 August 2009 18:20

Until proven guilty beyond a reasonable doubt, I believe Oracle to be innocent.

Hear Hear!

Previous Topic: Select Sdo_elem_Info & Sdo_ordinates data
Next Topic: Field Combination query (merged 3)
Goto Forum:
  


Current Time: Wed Dec 07 18:40:09 CST 2016

Total time taken to generate the page: 0.17207 seconds