Bug in Export File Browser (date format for Croatian NLS)

One of the many coolest feature in Dell's Toad for Oracle is Export File Browser (accessible via menu Database|Export|Export File Browser). With this tool user can open any export file dump (exp) and see it's data in it, tables, views as well as packages, functions and procedures. Who was working with original Oracle exp/imp tool will know it's biggest limitations and would appreciate this "quick view" feature in Toad. And this is not all ... any data can be filtered and exported in a way like it is presented in normal Toad grid. Cool indeed!
First let me show mine default session NLS settings from sqlplus:

SQL> select sysdate from dual;

SYSDATE
--------
11.06.13

SQL>

As you can see 'DD.MM.RR' is NLS session date format setting. This NLS session setting is proven looking from specialized Toad three views (menu Database|Administer|NLS Parameters):

Let me show original EXP_FILE_BROWSER_DATE_BUG table data, how they look in Toad grid:

Just to remind you that grid's date format is defined differently from any NLS! The matter of fact it is inherited from Toad's Option|Data Grids|Data ... Display ... "Date format", which is in mine case defined as 'dd.mm.yyyy'.

The problem

In this Toad version (11.6.1.6, latest in this moment and probably last one in 11.x version) I found a pretty nasty bug, which rely on wrong NLS conversion, for Croatian ... and probably others non English languages.
Let me say that Croatian name for month February is Veljača, represented by Oracle NLS as "vel". Here is correct result from SQLPlus:

SQL> select to_char(to_date('28.02.2013','dd.mm.yyyy'),'dd-mon-yyyy hh24:mi:ss') correct_date from dual;

CORRECT_DATE 
--------------------------------------------------------------------------- 
28-vel-2013 00:00:00

SQL>

And when you open an exp dump of mentioned EXP_FILE_BROWSER_DATE_BUG table, you have a situation:

First of all, this grid is not following Toad Option date format definition. The matter of fact seems that date format is hardcoded with definition "dd-mon-yyyy hh24:mi:ss" regardless date definition is active!
Then, you might see Toad's programmers thought February month value in Croatian NLS is vlj what is wrong. "lj" is one Croatian letter with two signs-and that obviously confused them. This is why Oracle defined that value as "vel"... So if you export data from grid (to sqlplus or sqlldr) your import will fail.
Workaround is to edit exported file content and replace "vlj" values with "vel".

The End

Because this feature is purely Toad work, their programmers had to implement own NLS support and this is sometimes not so easy to do ... in a light of many different countries and values that might happened. Always keep in mind that whenever any Toad feature is only an interface for any Oracle feature this might be pretty bug less option. In other case (like in this one) many bugs might happened because of lack of testing resources Dell might have. So test them more carefully before production use!
I think it is easy to fix it in next release, especially I have reported this bug to Dell support.
Hope this will helps someone.

Cheers!

Retrieve Oracle password from Toad for Oracle

One of the oldest feature Dell Toad has is saving login passwords. This is accomplish easy with enabling check box "Save passwords" on login screen.

The whole connection process is defined through three files located in %USERPROFILE%\AppData\Roaming\Quest Software\Toad for Oracle\11.6\User Files\, where "11.6" is Toad version and may vary in your cases:

1. CONNECTIONS.INI
2. CONNECTIONACTIONS.INI
3. CONNECTIONPWDS.INI

Passwords are stored in encrypted way in CONNECTIONPWDS.INI file. However they are not exposed in any normal way (you can read them) but only to use them as login without knowing password, which was once placed. This may raised some security issue, which I'll cover at the end. But having stored passwords allow Toad many beautiful automation and wide a lot actions that might need password as input. However, saving passwords also gave me additional feature (which is originally mine trick)-a way to retrieve Oracle passwords from any saved connection.

The solution

The trick is based on another Toad for Oracle feature-get SQL for any kind of DDL action, which was performed through GUI, in this case creating db link. Here is what you have to do to retrieve scott password:

1. Connect in Toad as any user for which you DO NOT WANT TO RETRIEVE password (in mine case this is vadas user)
2. Choose Database|Create|DB Link menu item
3. Fill data as shown in the picture:
   
   
   
   As you can see I have chosen scott user and password is automatically retrieved from saved passwords file.
4. Choose Show SQL as shown in the picture and you'll get pure SQL which contains password
   
   
   
   

And that's it! Pretty cool isn't it?
The trick is working for every user's password. In next case I'm showing how to retrieve sys password, retrieved through scott connection.

The End

Someone might say this is security issue, but I do strongly think it is not! Mentioned file with stored passwords is encrypted with two keys:

1. Domain user name
2. Some kind of workstation unique hash value

These ensures that password file cannot be copied to another workstation and Domain admins (or other privileged users on that workstation) cannot use that file in any way! For me this is more then fair insurance.

Keep in mind that newer releases of Toad do not use "workstation unique hash value", but only domain username as a pattern for hashing. Check and test before dropping old laptop data. For the end let me tell that if someone find storing password as a security issue regardless motioned, he/she can always disable that option and live with shorter
Hope this helps someone.

Cheers!