DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Returning/logging errors approaches

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339

    Returning/logging errors approaches

    Good day, all.

    I am interested in approaches taken for returning multiple errors from a given request.

    I have created a process to generically dump a single SQL statement to multiple files. This process is being used in a data download job that dumps ~20 files per requestor, with up to 50 requestors being handled at the same time.

    Needless to say, I can't stop the entire process for a single (recoverable) error on a single requestor. Taking that thought further, if even one requestor is error-free, the process needs to complete. I then need to inform the calling routine of all the errors.

    So, I'm thinking of creating some type of generic 'logging' table with a few numeric fields to generically contain some IDs, then maybe an error number field and an error description field and maybe a couple trigger-populated fields (ID, date/time, user...).

    I'm thinking of using autonomous transactions (conditionally, with retries for when distributed transactions cause failure)

    This is now starting to look/act a lot like my generic debugging tables, so I'm wondering if I'm going down the right path.

    Any and all comments/critiques/new ideas/past approaches/links are welcomed!

    Thanks,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Returning/logging errors approaches

    Originally posted by chrisrlong
    I'm thinking of using autonomous transactions (conditionally, with retries for when distributed transactions cause failure)
    Hi Chris,

    I'm a bit confused about the above part of the story. Are you saying that distributed queries (i.e. some tables are accessed via db links) are involved? If so, then I guess you might have a problem: autonomous transactions can not be used with distributed queries/transactions!

    Or have I completely missed that part?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Since I want to make a generic solution, I have to be able to handle all situations. In my generic debugging solution, I use autonomous transactions. However, I also capture the error generated when the autonomous transaction conflicts with a distributed transacion, and I then try a different call that doesn't use an autonomous transaction. So all I was saying was that I will do the same thing here - use autonomous transactions (optionally, but by default), but automatically retry it without an aut. trans. if it breaks (due to being in a dist. trans.)

    Did that make sense?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Can you use a collection to gather the errors?

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, it makes more sence now. However I'm still currious about one thing. You say when you can't use autonomous trtansactions you'll make another call that will use "normal" transaction to populate logging table. If this is the case, then what's the point in using autonomous transactions in the first place? It's obvious that if "thing gets tough" you still have a backdor way to do it using normal transaction - so why complicating with AT at al? Why not allways using this second option, why not keeping it simple? Are there any particular reasons?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The answer to both questions is that I want to be able to record even non-recoverable errors here. If the job bombs in the middle, I want the table to still hold as much information as possible. In the case of my debugging solution, one of its main purposes was to hold those 'Got to step 1', 'Got to step 2'... type of messages so one could see how far they got before things broke and the transaction rolled back. But as a generic debugging package, the least/best I could do when in a dist. trans. existed was to still record the information, I just couldn't handle a rollback anymore - beyond my control.

    The same would apply here. The debugging solution is only turned on in dev. here - (all the calls are always in the code, just turned the package off - very cool, actually, IMHO ) So in production, this would be a more formalized version of that. When a big job like this is kicked off, I was thinking it also might record its progress in such a table, successes and errors alike. So again, if any unexpected (read:unhandled) error occurs that would cause a rollback, I would want support to be able to look into this table to get as much info as possible. Again, however, if the calling routine came from a dist. trans., then I still recorded what I could (in case things worked or whatever), but I just couldn't do the aut. trans. part (thank you, Oracle).

    So, the collection would present the same problem - no persistence.

    Good feedback, though - keep it coming

    - Chris
    Last edited by chrisrlong; 03-24-2003 at 10:41 AM.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Can't claim to understand all of this - but it seems to me the problem is that the error recording is mixed up with the error "generation" if you use the same technology. I would consider using UTL_FILE, possibly with a close after every write to make sure OS buffers are flushed. I'm sure you can devise a format that can be read back easily for reporting. It's not pretty, but it could be bomb-proof.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    OOOPS - showing my ignorance - it's UTL_FILE.FFLUSH to flush buffers.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Well, I'd qualify the ability to hold info from a distributed transaction as an added extra as opposed to a requirement, so I'm not too focused on that.

    The main, driving requirement is to return multiple 'handled' errors from the call. I need to be able to say that 'Request X1 failed on step Y1 for reason Z1' and 'Request X2 failed on step Y2 for reason Z2'. This must work in any type of transaction (distributed or not).

    An added extra would be the ability to say 'Request X3 completed successfully'. This should work in any type of transaction.

    Another, slightly less important added extra would be the ability to say 'Step Y4 started successfully'. This should work in any type of transaction.

    Another, slightly less important added extra would be the ability to see any of these statements after an un-handled exception. This would not work in a distributed transaction.


    This thread got a little off-track because I was simply not clear about the mixing of distributed and autonomous transactions.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  10. #10
    Join Date
    Dec 2000
    Posts
    138
    How about using dbms_pipe for the distributed transactions, (I am not sure if they'd work for dist tx though) instead of Aut Tx.
    -dharma

Posting Permissions

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


Click Here to Expand Forum to Full Width