Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Handling failed packages/stored procedures

    Hello all,

    I am using Oracle Sql Developer and was wondering if someone could help out with capturing failed packages and stored procedures. I am looking for some code or guidance on how to handle and email me when a packages/stored procedure goes down in my production server. Why I need this is to be proactive and so that I know when a package/stored procedure fails before someone else

    Let me know if there is some built in oracle function to do this or some example code would really help. I am using oracle 9.2. Thanks again.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >packages/stored procedure goes down
    I see the words, but I have no idea what they mean.
    EXACTLY how does PL/SQL code "goes down"?
    How would an independent observer conclude the "failure" has occured?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Red face

    anacedent,

    What usually happens is a developer makes a change to a front end application that changes a database field name for instance and then when the procedure looks for that particular field it can't find it anylonger. In oracle sql developer this is shown by a red x(signifying a compiling error). These packages/procedures are used for reporting purposes, so if it goes down(via a change to the database field), the report will not work. Let me know if that makes sense.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    IMO, you do not have an Oracle problem.
    You have a mis-management problem.
    1) No developer should have DML access to any production server.
    2) The whole QA team should be suspended for a month for letting fatally flawed s/w into Production
    3) The Pointy Headed Boss (PHB) in charge of the department should be fired for incompetence.
    Last edited by anacedent; 04-11-08 at 00:50.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    FWIW I totally agree with Anacedent, you should not have developers making structural changes to a live database i this manner.

  6. #6
    Join Date
    Apr 2006
    Posts
    140

    Red face

    I hear you guys, but regardless of whether it is right or wrong, could you provide a coding solution. Is there a way to check if the package or procedure goes down with code? Thanks.

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    Use your exception handling routines that should be in all of your subprograms

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Rhetorical question - What action do you plan on taking assuming you can identify the broken procedure?
    In other words how will YOU know what changes are required in the code to produce CORRECT results?
    If you know what changes are needed to make the code correct, the DUHveleoper should be fired & you can replace him permanently!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    You'd want to query the dba_objects table, maybe on a regular basis using a scheduler:

    Code:
    select *
    from dba_objects
    where status = 'INVALID'
    You can further filter by OBJECT_TYPE if there are only some that concern you. Just place this in some PL/SQL which emails you, texts you, whatever, when INVALID objects arise.

    My 2 cents, I think there's too much control at the DBA level, with it forming a bottleneck in many cases. While I don't think anyone should be able to affect production, I do think relaxing these standards can work. That's how we do it, and the number of errors that have resulted are minimal.

    --=cf

  10. #10
    Join Date
    Apr 2006
    Posts
    140

    Red face

    hahaha. DUHveleoper I like that.

    chuck_forbes I will look into the dba_objects. Is there nothing else that could be written to inform me when a package goes down. Usually the fixes are rather simple such as recompliling the proc package. This doesn't happen very much but the code(error trapping) will be used as a precaution so as to say to the DUHveloper what the heck are you doing and possibly fire him Then again it takes a lot to get fired here

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    Oracle will attempt to recompile invalid PACKAGES when someone tries to use them, and if they compile, no errors for the end-user. so I don't think that is the situation you'll be trying to remedy.

    You can always put a standard in place which requires anyone moving objects into prod to run a VIEW (based on dba_objects) and report to you if there's a problem. They should be doing the same thing in DEV and TEST, though, so that hopefully by the time they get to PROD there are no such problems.

    --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •