Friday, December 5, 2008

Using Oracle SQL Developer Copy Wizard to create local XE schemas

Teleworking is great.  Actually, it’s a modern wonder.  There are tons of benefits to the employee and employer.  In addition to better QoL scores, in many cases teleworking boosts developer productivity thanks to fewer distractions.  There are some disadvantages however.  Working remotely places one at the whim of residential bandwidth.  Once considered ginormous, 10Mbps connections are almost the norm in many suburban areas, however local conditions can vary and an outage can be devastating.  Even when the bits are flowing, downloading large record sets can clog even the biggest pipe costing the developer time during repeated compile/test cycles.  That brings us to development “unplugged”.  If you are a Microsoft shop, running a local copy of SQL Server express is nothing new, but if your application is tied to an Oracle backend, replicating that was typically beyond the skill set/toolset of the average code jockey.  Oracle 10g Express (XE) and SQL Developer has changed all that.  Now the process is as simple as adding a new user (see screenshot) to your local database with the browser admin utility and using the dead-simple Copy Wizard in SQL Developer’s Tools menu.  Unplugged is fast, but don’t forget to keep your integration server in-synch (daily at a minimum) so your work is not lost if the workstation croaks.  Note, the tns-free connection string did make my forehead wrinkle:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=XE)(SERVER=DEDICATED)));user id=<user>;password=<password>;

Tip: keep multiple connection strings in your web.config to be able to switch between the local and integration server; whichever key is last, “wins”.

No comments: