Broken out into two routes… Consume web service and enqueuer in ActiveMQ Dequeue from ActiveMQ and persist in PO object in Oracle database <route id=”get-po”> <from uri=”timer://simpleTimer?period=30s”/> <to uri=”http://cmhlcarchapp01:8080/tradestone/send_po.jsp“/> <to uri=”jms:queue:testMQ” /> </route> <route id=”insert-db”> <from…
Category: PL/SQL
Drop all tables in a schema
This assumes the tables have no integrity constraints related to a table in another schema. This just loops until all tables are dropped. If you are the nervous type, add an exception handler for the integrity constraint exception, and exit…
The PL/SQL commit “optimization”
For those of you that may not know, PL/SQL has what is normally an enhancement that results in a full commit being performed at the end of a job, rather than when the programmer instructs it do so. The reason…
Gotcha on automatic PL/SQL recompilation
What is below is taken from an actual analysis of heavy latching and invalid objects after a change was made. This was done in early 2011. For a long period of time, we have avoided making changes that would result…
Programatically creating GoldenGate conflict detection/resolution rules
We initially started with checking all columns in every table for any differences in an active-active environment, and aborting the replicat if it failed. That seems a bit over the top in hindsight, so we changed it to simply discard…
Poor man’s partitioning with instead of triggers – PART 1
The Enterprise Edition of Oracle Corp’s. flagship database has many features. One of these is partitioning. Partitioning allows a user to segment table rows into separate physical storage structures. This can improve things such as performance and manageability. Performance can…
Standard exception pragmas
Oracle provides several standard exception pragmas (essentially, a compiler directive) for exceptions found on a fairly frequent basis. These are documented in the PL/SQL User Guide and Reference, but you can also get them at runtime using what is below:…
Posting GV$SESSION_LONGOPS from a user procedure
The GV$SESSION_LONGOPS view can save you if you are nervous about how much time a long running operation has remaining, and are asked for an update from someone in a higher “zip code”. Oracle posts an entry to this memory…
11g compiler warnings
The PL/SQL compiler warnings have been enhanced in 11.2.0.1 and up to include a warning for the dreaded “when others then null” exception handler. Actually, the warning is raised any time an “others” is handled (even if it is logged…
Useful script to print session gv$sesstat changes over time
Oracle provides a plethora of tools for monitoring user activity, including tracing, monitoring wait events, as well as ASH. However, sometimes I want a quick (literally) overview of a session. To do this, I query the gv$sesstat view. However, the…