I recently worked at a company that used “Product X” for all of it SQL and PL/SQL activities. There’s no real need to reveal what “Product X” is, because this isn’t a post about whether as a product it was good, bad or somewhere in between. “Product X” met the needs of the Oracle developers in the company, and that’s all that matters. There was just one issue – for Product X, the company was paying a little under $100k annually in license fees.
Enter SQL Developer. Similarly, it’s not particularly relevant whether I think, or anyone else at the company thinks its better or worse than “Product X”. What does matter is – can it do the required tasks of the developers. And the answer to that is a resounding yes. And once that question is answered, then paying any kind of annual license fee for Product X becomes not an intelligent business choice. So the organisation is in the process of moving to SQL Developer. The trick now is how to enforce assist 🙂 the developers with moving to SQL Developer. And the reality is – once you show developers how to re-map the shortcut keys in SQL Developer to match what they were used to in Product X, then the task is virtually done.
But some developers still clung to Product X…and hence were threatening a violation of a the no-longer current license agreement. So the task then became – how to stop dissuade 🙂 them from still using Product X. This post gives you a few ideas on how you might want to tackle it.
Version 1
I assumed the best of our developers 🙂 and put a simple login trigger to check if the program they’d connected with was “ProductX.exe”. If it was, then a job was submitted (via dbms_job) to pop an email to them to let them know that Product X was no longer the company standard.
Version 2
But old habits die hard…and within a few days, some developers had renamed ProductX.exe to SQL Developer.exe and were back on the database thrilled with their ingenuity 🙂 So the trigger was adjusted to also check the MODULE, as ProductX also set its session module value to “Product X”. But, you can’t keep a good developer down, and with a little assistance from hex editor, the module was now cleared.
Version 3
Most development tools, when they connect to the database, run a series of queries to check things like privileges and the like. So by tracing a connection by ProductX, a list of cursors that were executed could be identified. So the login trigger was altered to always submit a job. The job would check the program and module were checked as before, but it would also check V$OPEN_CURSOR for the new session. If it contained a selection of the statements that Product X would execute, then the trap has been sprung 🙂
So Version 3 has been active for a couple of weeks now, and plenty of emails have been sent. It will soon be amended to terminate connections, and thus, the migration to SQL Developer will be complete 🙂
Learning is not a spectator sport 
Leave a reply to connormcdonald Cancel reply