Sharing SQLDeveloper SQL history across several machines with Dropbox

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick tip for keeping a single shared SQL history repository among different installations of SQLDeveloper, for the same user, on distinct computers synchronized with Dropbox.
 
This seems to be working just fine on Mac OS X and SQLDeveloper 4.0.2 however it's a totally unsupported hack, which means ** use at your own risk **.

I ignore what will happen when I'll upgrade to the next release of SQLDeveloper but I'll find out sooner or later.
(Updated September 24: No problems whatsoever after upgrading to 4.0.3, the shared SQL History folder is still there and working.)
From what I've seen, SQL Developer makes a copy of the history folder when it upgrades an existing installation, may be it crashes or may be I'll have to repeat the whole procedure, I don't know, for the moment I really appreciate the fact that I can pick up a work on a different location exactly from where I left off without having to worry about making copies of certain queries manually.

As your SQL history is going to be bigger than before, it makes sense to increase the maximum number of statements that you want to keep, to do so go to Preferences/Database/Utilities/Worksheet pane and increase the SQL history limit and remember to do so on all the machines, setting the same value *before* proceding with the next step, otherwise the installation with the shortest history will wipe out the files added by the others!

From the shell prompt, assuming you are in your home folder and SQLDeveloper is NOT running:

cd .sqldeveloper
cp -R SqlHistory ~/Dropbox/.SqlHistory

mv SqlHistory SqlHistory.old

ln -sf ~/Dropbox/.SqlHistory/ SqlHistory

SqlHistory.old is your safety net, in case something goes wrong.

Then, on the second machine you may either decide to get rid of the current history and use the history shared from the first machine or you may try to merge them.

In the former case, just execute:

cd .sqldeveloper

mv SqlHistory SqlHistory.old

ln -sf ~/Dropbox/.SqlHistory/ SqlHistory

In the latter case, in addition to the three steps above, execute also:

cp -R SqlHistory.old SqlHistory.tmp 

mv -n SqlHistory.tmp/* ~/Dropbox/.SqlHistory

Any duplicate filename (albeit with different SQL content) will remain in the SqlHistory.tmp folder, so you have a chance to decide what to do with it. If the SqlHistory.tmp is empty at the end of the operation, then get rid of it.

SQLDeveloper keeps the log as a bunch of XML files whose names are generated by prepending a pseudo random number (I ignore the details) to the fixed string "history.xml".
As I don't know if this number can be unique across different computers in virtue of some clever algorithm, I guess that occasionally there could be a conflict with an existing one, but when I merged the histories of my SQL Developer installations, there were no conflicts at all (may be the magic number is based on the timestamp, which explains why there were no conflicts as I haven't been given the gift of ubiquity...)

If there is a third machine, repeat the same steps as for the second one and so on.


Probably it could make sense to do the same with the library of connections, but this is an exercise I'll keep for another occasion.

I guess the same procedure should work also on most Linux distros as I don't see any difference under the hood.

How to change the language of SQL Developer's user interface?

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Yesterday i upgraded SQL Developer from version 1.5.1 to 1.5.4 and when i opened it, i noticed that the user interface was automagically showing up in Italian.
I say automagically because i am italian indeed, but the operating system on this particular machine is localized in mexican-spanish, so i wonder if SQL Developer was spying my conversations... ;-)

While i appreciate the globalization efforts, i feel much better having certain programs with an English user interface for the simple reason that when i need to communicate with someone else who doesn't speak Italian, i can talk about menu entries, settings, button names using a commonly understood language and without having to guess what the original entry in English language could be.

How to get rid of this automatic translation then?
I started wandering through the preference menu and either because i am getting old or there are too many settings or this setting can't be changed in the preferences, i failed to find a list available translations.

After a quick OTN search i found a thread where users were reporting some problems when creating new connections and i noticed a workaround where one must add the following two lines to the Sqldeveloper.conf file in bin subdirectory.

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

I promptly tried it out to see if that could solve my problem and bingo!

I don't know if this is the only "supported" way of changing the user interface language, but it's certainly working.

Please let me know if there are any other options.


See more articles about SQL Developer.

PS: I encourage everyone interested in this feature to cast their vote in the SQLDeveloper Feature request application: https://apex.oracle.com/pls/apex/f?p=43135:7:0::NO:RP,7:P7_ID:10821

SQL Developer, Mac OS X and the weird bug of key presets

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick note for those who came across a weird bug in Oracle SQL Developer that makes its editor virtually useless under Mac OS X.
If you are wondering why suddenly the backspace key doesn't work or the arrow keys simply do nothing, it seems that, for some reason currently being investigated, the keyboard preset for Mac OS X is not correctly applied or it's not properly loaded.
If you go to the menu TOOLS/PREFERENCES/ACCELERATORS, then you click on the button Load Preset, and finally you choose "default Mac OS X", you should be able to get the keys to work again.
And thanks to -K- for the useful workaround!




See the OTN thread where this problem is discussed.

Apparently this elusive bug affects SQL Developer both 1.5.1, 1.5.3 and possibly 1.5.4 according to Sue's reply.

Finding your way out of SQLDeveloper's DBMS_OUTPUT pane

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

SQL Developer 1.2.1 build 32-13:
have you ever used the DBMS_OUTPUT pane inside Oracle SQL Developer?

Unlike my friend John Scott, i like to open several SQL Worksheet against the same database connection.
This must be the reason as to why i noticed one curious fact about the DBMS_OUTPUT pane that he wasn't apparently aware of, according to a recent message interchange.

In brief, if you open multiple worksheets against the same connection, you'll notice that each one comes with an associated series of tabs, among which there is the DBMS_OUTPUT pane (see the center of the picture in the screenshots below).
According to my personal experience, the pane associated with the first worksheet where you enabled the DBMS_OUTPUT will drive all the DBMS_OUTPUT of the session, as illustrated by the following series of pictures.

The first worksheet belongs to the first session. After enabling server output and after executing the procedure, it will display the content of the buffer:

The second connection has got its own DBMS_OUTPUT pane also displaying some output:

The third worksheet however is created against the first connection and it won't display anything.


However, if we switch back to the first worksheet, we find the output of the third worksheet...


This can be explained by looking at the active oracle sessions opened by SQL Developer, where it becomes clear that multiple SQLDeveloper worksheets against the same database connection will be mapped to the same oracle session:

select sid, serial#, username, module, action
from v$session
where program = 'SQL Developer';

SID     SERIAL#   USERNAME  MODULE                      ACTION
------- --------- --------- --------------------------- -------------
116     38309     SYS       SQL Developer            
117     8352      SYS       Oracle SQL Developer        Code Insight
121     13186     YOCOYA    Oracle SQL Developer        Code Insight
125     26877     YOCOYA    SQL Developer            

4 rows selected

SQL Developer uses two distinct sessions for each connection, one is marked as "Code Insight" and is for handling the object tree, the other one is for executing the SQL scripts. I opened two db connections and this accounts for the 4 rows that you see in the report above.

Although this makes perfectly sense from a technical point of view, it can result in puzzling results, as far as DBMS_OUTPUT is concerned.

Note also that if you set server output off and on again from in the third pane, then you will redirect the output to the active tab and the other one will stop being updated.

Not a big deal, but something to be aware of.