Just a while ago we had an issue with ORA-30678 on one of test databases.
Scenario
The scenario looks like the following:
- there exists a script, which reacts for certain events by connecting to the database and calling a procedure, which among other things sends also a notification email
- the script keeps persistent connection to the database
- at some occasion the script was received wrong parameters
- it make a call to procedure, which opened SMTP connection
- due to wrong parameter value the procedure threw an exception, which apparently was catched at some level (may be on the db level, but I suppose it simply returned to the script level, which ended a loop turn and started another one with the advent of a new triggering event)
- it seems that there is a built-in limit set to 16 TCP connections per session in Oracle - apparently 15 more event loop turns were performed
- at every next loop turn the ORA-30678 was threw
What was wrong?
Apparently the code was wrong. It worked well, but only for good parameters. The lack was exception handling, which have to close any TCP connection at the end of run no matter the communication outcome. Of course good programmer will log the exception circumstances, etc., but the cleanup is crucial here.
It is important for all the packages, which use TCP connections: UTL_SMTP, UTL_HTTP, UTL_TCP
