I’m always telling people to instrument their code. Invariably they don’t. Then this happens:
- Dev: Why is this call failing?
- Me: What are the parameter values you are calling it with in your code?
- Dev: Values X, Y and Z.
- Me: Have you called the routine directly with those values?
- Dev: Yes, and it worked fine.
- Me: That would suggest those are not the values you are really using then.
- Dev: But they are.
- Me: How do you know. Have to traced the variable values before you made the call.
- Dev: No, but they are the correct values.
- Me: Can you put in some trace messages to check?
- Dev: (walks away) … grumble … grumble … stupid trace … wasting my time …
- Me: (some time later) So what values were you passing in?
- Dev: One of the values was not what I was expecting, which is why it broke.
No matter who you are or how cool you think you are at programming, you can never know exactly what is going on in your code unless you instrument it. Just shut up and do it!
- Use DBMS_APPLICATION_INFO to indicate exactly what your code is doing at all times. The information can be used for targeting SQL tracing (DBMS_MONITOR), it shows up in the performance reporting (ADDM, AWR, Cloud Control) and some of it gets added to the audit trail, which is very handy. You might prefer to use the Method-R wrapper called ILO.
- Put tracing messages into your code. You can use DBMS_OUTPUT directly, but it is probably better to use one of the wrappers that allows you to do clever things, like redirecting the output to different destinations. I wrote my own wrapper code (dsp.pks and dsp.pkb), but there are a few out there. You might want to consider log4plsql or logger.
- You can even direct messages to the alert log or trace files using the undocumented package DBMS_SYSTEM, if you are feeling brave.
Experience tells me you will read this and ignore it, but that is a big mistake! A real professional *always* comments and instruments their code!