By Pavel Shchegolevatykh / August 19, 2013

Common issues with Oracle XE database

Working with Oracle XE as a beginner I faced with many issues that took me hours or even days to fix. Here I'd like to mention two of them: ORA-12705 and ORA-12514.

Fixing ORA-12705

This issue relates to people who connects to the Oracle DB server from the the environment with different language settings. For in my case the server was located in Germany and I was connecting from the Russian PC. I found many answers on the Internet and most of them just didn't work. Some of them suggested configuring the server enviroment which I didn't have access to. But first things first.

When I try to connect to Oracle Database via SQL Developer or plain JDBC I was getting an error:

ORA-12705: Cannot access NLS data files or invalid environment specified.

Of course none of my German colleagues were having this issue. It was all fine for them, and because I was the only foreigner in the project I had to find a solution to this all by myself. Solving problems was my job after all.

The most popular advice on the Web was to change the values for NLS_LANG subkey from Windows Registry or remove it completely (HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE). I tried both but it didn't work for some reason.

What did work is to manually tell the JVM what region and language it should be using. For example to make the connection work in Oracle SQL Developer you should find the sqldeveloper.conf config file (under sqldeveloper\bin folder) then open it and add those two lines:

AddVMOption -Duser.language=en
AddVMOption -Duser.region=us

If you use Eclipse IDE you should add those lines into the eclipse.ini config file (it can be found under the root eclipse folder):

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

Also you should supply those parameters to your JRE(JDK). To do this go to Window -> Preferences -> Java -> Installed JREs -> [Chose your JRE(JDK)] -> Edit. Near the Default VM Parameters enter this line:

-Duser.language=en -Duser.region=US

This should solve your connection problems without any code changes. If none of this works you can always hardcode the language locally before making a DB connection through the code:

Locale.setDefault(Locale.ENGLISH);

Do not commit this and use as a workaround. Hope you won't need this.

Fixing ORA-12514

This is connected to making a database backup, which I had to do quite often as a part of my job. I was not a DBA or Oracle expert in any way. I thought this should be a pretty simple task. I was wrong. In Oracle we don't trust.

So I was getting an ORA-12514 error trying to make a database dump file (*.dmp) of the existing schema using the imp command via console:

imp pshchegolavatykh/mypassword@mydb file=C:\SomeFolder\DbDump\LatestDate.dmp full=y

Oh by the way if you don't specify the full=y flag you will get a message that import was successful but there would not be any real changes. WTF Oracle?

Anyway it turned out that the system did not know about mydb identifier by default. You have to manually edit tsnames.ora file and map this id to the actual connection string. After you've done that you can use mydb as an alias.

Usually tsnames.ora file is located under <OracleInstallationFolder>\NETWORK\ADMIN directory. If you forgot where the Oracle DB Instance is installed use Windows File Search.

Open the file, using any text editor. Remove everything and paste those lines:

<addressname> =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = <sid>)
 )
)

Insted of the placeholders use your own data. It should look something like this:

MYDB =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = mydb)
 )
)

If you are not sure what sid you should put there try this query in SQL Developer or any other similar tool:

SELECT value FROM v$parameter WHERE name='service_names';

sqlplus pshchegolevatykh/masterkey@mydb