Home » SQL & PL/SQL » SQL & PL/SQL » procedure executing and data not getting processed
procedure executing and data not getting processed [message #284451] Thu, 29 November 2007 13:19 Go to next message
nallapati
Messages: 4
Registered: November 2007
Junior Member
Hi,

I use oracle 10g DB. It receives data every 5 minutes from the hardware. There is a job called etl_mgr that runs every 5 minutes and processes these recordds and moves the processed data to some other table so that my application displays that data in the front end. This worked fine till 4 days back. Suddenly etl_mgr job stopped processing data that comes into the DB every 5 minutes. The job is still running and rescheduling every 5 minutes. It internally runs etl_mgr procedure to process data. The procedure compiles and executes without any issues. I am unable to figure out the rootcause. Any help in this regard would be very helful to me. Thanks in advance..

regards,
Nallapati
Re: procedure executing and data not getting processed [message #284453 is a reply to message #284451] Thu, 29 November 2007 13:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Copy the incoming data to a development env and debug the code.
If the job itself does not crash, and you have no way of logging, that is about all you can do.
And this is about all I can say about it with the limited amount of info provided.
Re: procedure executing and data not getting processed [message #284454 is a reply to message #284453] Thu, 29 November 2007 13:39 Go to previous messageGo to next message
nallapati
Messages: 4
Registered: November 2007
Junior Member
Thanks a lot for your suggestion. I'll try that and post the results.

Just to add something, the data being processed here is already validated. Also the same data is processed on other environment successfully. So i think there won't any data issues.

Can you think of any other one..

thanks in advance...

[Updated on: Thu, 29 November 2007 14:30]

Report message to a moderator

Re: procedure executing and data not getting processed [message #284471 is a reply to message #284451] Thu, 29 November 2007 15:39 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Can you update the procedure to put some debug messages in a table?

Maybe you are out of space in your tablespace and no more data can be loaded. Maybe someone changed privileges.

although this is very little info in your message. What does it mean that the database receives data from the hardware? How do you know you job is still running? What loads the data, SQL*Load, UTL_FILE, external table?
Re: procedure executing and data not getting processed [message #284475 is a reply to message #284471] Thu, 29 November 2007 17:14 Go to previous messageGo to next message
nallapati
Messages: 4
Registered: November 2007
Junior Member
java application collects data from the real time probes[i mean hardware] and insert that raw data record into one table[isup_working]. The DB job called etl_mgr processes that raw data into number of real time tables. From where the java applciation get the data to display in the front end. Now the data is coming inot the isup_working and the job that processes that data is also running fine. The data is not being processed. The procedrue code is same as that in production. I could compile nad execute the procedures. I could see the status of the jobs from the toad. It runs fine and reschedules every 5 minutes. I hope this helps.

Regards,
Nallapati
Re: procedure executing and data not getting processed [message #284477 is a reply to message #284451] Thu, 29 November 2007 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The data is not being processed.
So figure out why is the data is not being processed & fix the problem.

Keep in mind that we don't have your data & we don't have your procedure, so therefore only YOU can directly address this issue.

[Updated on: Thu, 29 November 2007 17:24] by Moderator

Report message to a moderator

Re: procedure executing and data not getting processed [message #284479 is a reply to message #284477] Thu, 29 November 2007 18:56 Go to previous messageGo to next message
nallapati
Messages: 4
Registered: November 2007
Junior Member
Hi, another update with the issue is, I updated the existing procedure with the production[which is working copy] one and it processed 200 records of the 2000000 records. then it stopped processing. Do you think any issues with the tablespace and if yes, please let me know how to check the table spaces and their max limit.. earlier it used to store millions of records in that table. Every night that table data will be moved to another table.

[Updated on: Thu, 29 November 2007 19:00]

Report message to a moderator

Re: procedure executing and data not getting processed [message #284480 is a reply to message #284451] Thu, 29 November 2007 19:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I updated the existing procedure with the production[which is working copy] one and it processed 200 records of the 2000000 records. then it stopped processing
Without knowing exactly why it "stopped" processing, folks can only make wild guesses.
Why don't you tell us EXACTLY why it stopped processing so we don't have to guess.
It could have "stopped" for any number of DIFFERENT reasons.
You need to provide us with specific details.

Are you seeing any interesting lines in the alert_SID.log file?

Keep in mind that we don't have your data & we don't have your procedure, so therefore only YOU can directly address this issue.

[Updated on: Thu, 29 November 2007 19:51] by Moderator

Report message to a moderator

Re: procedure executing and data not getting processed [message #284487 is a reply to message #284451] Thu, 29 November 2007 21:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you need to find out what error is causing the job to fail and quit. It is a guess but I bet there is an exception handler in your procedure that is handling the error but not reporting it correctly. You should disable all excpetion handlers and let the error show.

Additionally, the suggestion to look in your alert logs is a good one. Ask your DBA for help with that.

Good luck, Kevin
Re: procedure executing and data not getting processed [message #284490 is a reply to message #284451] Thu, 29 November 2007 21:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>you need to find out what error is causing the job to fail
While it is likely that an actual error has occurred, we have NO evidence to know for sure.
The unknown procedure may be reacting to a data dependent bug.
Any number of bad things could produce the general symptoms that have been presented.
Why not wait for actual facts before heading down the Yellow Brick Road?
Re: procedure executing and data not getting processed [message #284491 is a reply to message #284451] Thu, 29 November 2007 21:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
sometimes people need a little prodding or help in figuring out how to find the needed information. I was offering up an avenue of action.

Also, my vast experience obviosly exceeds your own for if you had my deep knowledge and understanding of pl/sql you would know that my suggestion is a very good one.

oh Michel, look what your posts this evening have done to me tonight. I am being rude to anacedent.

Kevin
Re: procedure executing and data not getting processed [message #284516 is a reply to message #284491] Thu, 29 November 2007 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Shocked Are you saying I used to be rude? Wink Surprised

Regards
Michel
Re: procedure executing and data not getting processed [message #284517 is a reply to message #284475] Thu, 29 November 2007 23:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Wild guess:

Check your (set of) procedures for "WHEN OTHERS"
If you find any see if they silently ignore the error (i.e. "handle" without a raise.)
If so, remove it (and do NOT put it back after this issue is solved)
Re: procedure executing and data not getting processed [message #284541 is a reply to message #284517] Fri, 30 November 2007 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes! this should be a standard answer when nothing is visible.

Regards
Michel

Re: procedure executing and data not getting processed [message #285264 is a reply to message #284516] Tue, 04 December 2007 02:58 Go to previous messageGo to next message
snehorafaq
Messages: 1
Registered: October 2007
Junior Member
as per your conversation,I think u can try this:
1) if there is any exception handling done and u are returning the err through out mode of parameter passing from a procedure,then it may be possible that it will not throw an error.
I dont know ur proc.But if this is the case then remove the exception handling part and then check ur proc.
Re: procedure executing and data not getting processed [message #285265 is a reply to message #285264] Tue, 04 December 2007 03:01 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
First read OraFAQ Forum Guide, there is a section about IM speak ("u", "ur"...)

Then can you explain xhat does your answer add to Frank's one 4 days ago?

Regards
Michel
Previous Topic: Comparing strings in Oracle
Next Topic: calling an exe file
Goto Forum:
  


Current Time: Sat Dec 10 02:58:41 CST 2016

Total time taken to generate the page: 0.13531 seconds