Stored Procedure working Intermittently

[newora]

Hi There, I wonder if anyone could help with a problem that I seem to be having with a stored procedure that I have developed under Oracle 8i.

The stored procedure has been working for a week and stopped working yesterday. I checked for errors in the procedure using SQL plus and it told me that there were no errors. All I did to solve the problem was to create/replace the procedure again, with exactly the same code that was already there.

Am I missing something somewhere as I can not understand why it should work ok one minute and not the next and be solved by reloading / recreating exactly the same procedure? The values being passed to the procedure did not change either i.e. they were the same on both days when the procedure was working and was not working.

Thanks your your help / ideas.

 

[BJCooperIT]

When posting a problem such as this it is most helpful if you provide more details. What do you mean when you say "it stopped working"? Can you provide an Oracle error message?

Just a guess, but it sounds as though the procedure may have been marked invalid in the database. This occurs when a referenced object (package/procedure/function)in the database is marked invalid or a table/view used in your procedure is dropped. Even if the referenced object was later successfully recompiled, or the table recreated, your procedure may not have been recompiled.

Are you using a tool such as SQL Navigator, Toad, or PL/SQL Developer? If this happens again you may use one of these to easily check the status of your procedure. Or you can go into a session of SQL*Plus as the owner of the objects and try:

select OBJECT_NAME
from   USER_OBJECTS
where  STATUS = 'INVALID' 
and    OBJECT_TYPE not like '%BODY%';

to see what objects are not valid.

See: "Compiling all invalid objects" thread186-684228

Good luck.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[newora]

Hi many thanks for your advice - unfortunately I do not have the Oracel error message, as the program that calls my stored procedure is written by a third party and just returns the error message "error on Oracle server".

I can not be 100 % sure but I do not think an database objects / tables have been modified (not by me anyway), but I will run your SQL query above and see what it returns.

Thanks again.

 

[BJCooperIT]

By "reloading" the procedure it should now be marked as valid. The query may only be helpful if the symptoms reappear.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[sem]

It may be due to dozen of reasons. The most common is error in you own procedure. The fact that odd numbers 3, 5, and even 7 are prime doesn't mean that 9 is also . The same with terms of successfull functioning.

BTW how did you check for errors in sql*plus?

I may suggest you to write some error log (once the calling program suppresses your errors). You may write WHEN OTHERS exception handler and drop error code at least (or better the whole error stack). You may insert this data in autonomous transaction or through DBMS_PIPE or even into OS file with the mean of UTL_FILE package.

It may happen that you get an error from calling program but this handler doesn't catch an error. In such case an error most probably occured during the call itself: wrong parameters passed or call was really made to invalid procedure.

Regards, Dima

 

[newora]

Hi There, well I spent about 2 hours last night checking all of the database tables and it turned out that someone had actually modified one of the tables that the stored procedure references!!

It was difficult to check the contents of the stored procedure, as the server does not have Toad / DBA studio on it, only SQL* Plus.

To show errors for a procedure in SQL* PLus I used:-

show errors procedure [Procedure_name]

Thanks for al of your help.

 

[sem]

show errors shows syntax errors only. If this command shows anything, your procedure can not be called at all. Moreover, unless it's invoked dynamically, the calling procedure should be also invalidated.

Regards, Dima