[Solved] ODI-26039 - java.sql.SQLException: Object already exists

Scenario: Creating an XML Data Server in ODI Studio > Topology > Techonologies > XML and testing connection twice or deleting and creating the same XML Data Server in that same ODI Studio session

Error Description: ODI-26039 Connection Failed. java.sql.SQLException: object name already exists.

ODI Error Object Name Already Exists

Cause: This is because the first time you created the XML Data Server or tested the connection ODI created and stored the objects for this particular XML file in it's JVM.

Resolution: Well this is embarrassing. Hope ODI resolves this in the future. You have to close ODI Studio and open a new session and try again.

ODI XML Data server Successful connection

Courtesy: oracle.developer-works

[Oracle 11g] Run a SQL Script from SQLPlus

Scenario: So there was a set of sql scripts to be executed into my database in order to work on a training session. Instead of copy pasting each script to SQL Developer and then running it. We thought of saving time by executing it from SQL Plus.

Resolution: Place your .sql extension file with the script at a particular folder in your system.

Now go to run and open sqlplus. Connect as a user with SYSDBA or DBA privileges since it is highly likely that this scripts will contain DDL statements like create, alter, or drop.

After connecting to SQLPlus as SYSDBA user, Enter the following in the command line

SQL > @PATH/FILENAME.sql

where PATH specifies the path where the sql file resides and FILENAME the name of the sql file script to be executed. We created a sql script named test.sql with content as shown below.

Sample SQL Script

 Save the file and copy the folderpath/filename.sql and go to cmd and paste it as shown below

SQL Script output after execution

[ODI 11g] [Solved] Error 26178 Unable to Connect to Repository User

Scenario: Creation of Master Repository in ODI and have to create a connection to the database to connect to the Master Repostiory schema.

Error Description: ODI-26178: Unable to connect to the Repository user. Listener refused the connection with following error. ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Connection Error at ODI Repository Creation

Cause: This is either because you don't have an SID with the same name in your database server or you are trying to connect with a service name.

Resolution: Check whether the string after 1521: in our case as you see in the screenshot above is orcl.0.2.15. Now go to app/product/dbhome/network/admin and open the tnsnames.ora check the connection detail and check whether this particular string is the SID or the Service Name. This was the service name in our case and if it's a Service Name then you have to modify your connection string as below,

jdbc:oracle:thin:@HOSTADDRESS:PORTNO/SERVICE_NAME

See that instead of PORTNO:SERVICE_NAME we have used PORTNO/SERVICE_NAME. Now try connecting again you will see that the connection proceeds.

[Solved] Oracle TNSPING Resolve Name Error (TNS-03505)

Scenario: Issue while connecting to SYS schema in a newly installed database from SQL Developer. So we tried to TNSPING the ORCL connection and we faced a 'failed to resolve name' error as below,

Failed to Resolve Name.

Cause: The issue is because we had both Oracle DB Home and Client installed. The tnsnames.ora file was present only in the network/admin folder in DB_Home and not in Client/network/admin 

Resolution: Copy the tnsnames.ora file and paste a copy to the network/admin folder in client installed directory. Try again as below and connection will be fine.

Error resolved.

[Solved] Oracle SQL Developer JVM not Found Error

Scenario: Setting up Oracle SQL Developer for the first time from inside Database home. The first time you try to run SQLDeveloper.exe you will be prompted for a JDK bin path. 

At this step you might face an error as below,

Unable to find Java Virtual Machine error

Cause: The java path defined is a 64bit JDK.

Resolution: Install a new 32bit JDK and then open the sqldeveloper.conf file from app/product/dbhome_1/sqldeveloper/sqldeveloper/bin and delete the JAVA_HOME entry as below,

Changing JavaHome path

After deleting run the SQLDeveloper.exe file and again you will be prompted for a JDK bin path. Now give the path to the new 32bit JDK you just installed. This will make SQLDeveloper run in your machine.

[ODI 11g] AppCrash error during ODI Installation [Solved]

We were trying to setup a VM ODI Instance on a Windows 2008 Server R2 when we came across this error below.

APPCRASH error - ODI Installation

I was at the step where I run try to run the setup.exe file from command prompt followed by the location for JDK. See below

Command Prompt command to run setup.exe

Cause: This is because the Java folder path has spaces in between.

Resolution: This can easily be resolved by moving the jdk folder to a path without spaces, simply put it outside the program files folder to C: Drive

If the first command in the above CMD doesn't work for you try out the second. 

[Oracle11g] Set Oracle Database User Account and Password to Never Expire

Here is a small tip on how to create a new user in Oracle 11g and set the password to never expire. This would come in handy when you are creating new metadata schema's for OBIEE, when for long time these schema's are not in use they tend to get locked and OBIEE services will fail to start.

So in order to avoid this scenario it is better to create a new Oracle user profile and set the parameters to unlimited. Please follow the steps below

After you create a new Oracle user, say for example OBIUSER then the next step is to create  a new User Profile OBIUSER_PROFILE as below

CREATE PROFILE OBIUSER_PROFILE LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED;

If you check the DDL statement above you can see that we have set properties like PASSWORD_LIFE_TIME, PASSWORD_LOCK_TIME, FAILED_LOGIN_ATTEMPTS to unlimited. Hence all the users having this user profile will never get expired. 

The next step is to assign this new User Profile characteristic to the new user OBIUSER,

ALTER USER OBIUSER PROFILE OBIUSER_PROFILE;

Now this user/schema password will never expire. 

References: Oracle Docs

[Oracle11g] [Solved] Arabic text update and insert issue in Oracle Database

Issue:  Unable to update or insert Arabic data to a database. Specifically any Arabic data inserted is shown in database as '?????'

Cause:  The issue occurs because the database character set doesn't support  UTF characters.

Resolution: Changing the character set of database to one that supports UTF characters would resolve the issue. 

Check the default character set of the database by running this query,

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

Now if you try inserting or updating any rows with Arabic language data then the data would be displayed as a set of special characters as below,

Log into database as SYS and shutdown the database services giving the below command,

SQL > shutdown immediate;

Now restart the database in restrict mode. When a database is started up in restricted mode then only users with special privileges will be able to access it. We are starting up in restricted mode as we are going to alter a database default value.

SQL > startup restrict

Once the database is up and mounted in restricted mode proceed with changing the default dataset,

SQL > ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Now again issue a shutdown immediate command followed by the startup command,

SQL > startup

Once database has been mounted confirm that the character set has been changed 

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

Now check again in the database to see whether Arabic data can be viewed now.

This is how you change the default characterset of a database.