mercoledì 31 maggio 2017

ODI Tips: ODI initialization hangs

I have been on the same OBIA/ODI project for almost two years now and every now and then I get this issue.
ODI Studio hangs on ODI initialization - Initialize Search Engine window.

No matter how much patiently you wait, it won't allow you to progress further.
The only option you have is to:
  1. Kill ODI Studio process using Task Manager
  2. Locate windowinglayout.xml file in your computer. Typically, you will find it under: C:\Users\<USER>\AppData\Roaming\odi\system11.\o.ide.   

  3. Delete the file and restart ODI Studio

giovedì 26 gennaio 2017

Oracle Tip: recovering SQL Developer passwords

I must admit that I am quite lazy (and not a very tidy person either). When I am given a new database connection to work with, I configure it in SQL Developer and save the password (so I do not have to type it when I connect and, more important, I do not have to remember it or store it safely somewhere else - shame on me!). As a consequence, when I am ask to provide the password to somebody else, it happens quite often that I do not remember it.
SQL Developer stores the saved passwords in an encrypted fashion in a file called connections.xml, which is normally available in \AppData\Roaming\SQL Developer\system folder.

Googling around, I have found a number of Java routines that can decrypt the passwords, but why spending time in compiling some Java code if a solution is already available?
This link has the instructions for installing a simple SQL Developer extension. It works great and it's the perfect shortcut for a lazy and forgetful programmer.

mercoledì 18 gennaio 2017

ODI Tip: Get Physical Schema from Logical Schema in an ODI variable

When it comes to file paths you do not want to hard-code them because they will need to be manually updated when you migrate from one environment to another.
The best way to retrieve a file path at runtime is to get it from the physical schema that you have configured in the Topology.
You can create an ODI variable and use the following code for its refresh:

select '<%=odiRef.getSchemaName("EXTRACT_FILES_LS","W")%>' 
from dual

where EXTRACT_FILES_LS is the Logical Schema name.

When you migrate to a new environment, the only thing that will need to be updated is the Topology Physical Schema and by refreshing the variable you will have the updated file path.

martedì 11 ottobre 2016

Oracle Tip: executing SQL scripts in SQL Developer

One common problem with SQL Developer is executing huge SQL scripts (i.e. INSERT statements from the export of a big table).
Typically you will get the following error when trying to open the file: 

which is caused by an OutOfMemoryError of the Java Heap space.

One convenient way to execute .sql without having to open the script in the SQL Developer editor is to configure the default path for scripts.
Go to Tools -> Preferences->Database->Worksheet:

and select the path where you want to place the SQL scripts to execute.
Then, go to the SQL Developer editor and simply use the @ command to reference and execute the script.

lunedì 10 ottobre 2016

ODI Tip: Generating flat files with spaces in column names

I had a requirement of generating a .csv file with header out of a database table.
I had created the model as follows:
  • File Format: Delimited
  • Record Separator: Unix  
  • Field Separator: , (comma)
In the interface, I had placed the file as Target Datasource and used 'IKM SQL to File Append' with the following options:

Unfortunately, the header columns were truncated at the first space and adding double quotes did not help. The column header were all truncated after the first space found.
After investigating, I came to the conclusion that there is was no quick or easy way to deal with spaces. I guess the only way is creating a custom version of the IKM.
Therefore, since I have very little time, I have replaced the spaces with underscores and added a OdiOSCommand in the package to replace the underscores with spaces in the output file.

ODIOSCommand is a more flexible version of OSCommand and allows you to invoke a sed command to do the replacement in place (-i) of the underscores with spaces.

sed -i 's/_/ /g' <filename>

lunedì 18 luglio 2016

Oracle Tip: Exception getObjectName("R", "OBI_EXEC_SESS_<$=Y$>", "D")

When running OBIA Load Plan, today I got into the following error when running SIL_DomainGeneral_CM_Source_Load scenario: ODI-17517: Error during task interpretation.
 Task: 6
java.lang.Exception: The application script threw an exception: Exception getObjectName("R", "OBI_EXEC_SESS_<$=Y$>", "D") :  BSF info: Run Alter Session Commands-SRC (DB Link) at line: 0 column: columnNo

The solution to this error is given in Doc ID 1948605.1 from Oracle Support.
You should connect to ODI Physical Architecture in ODI Topology and check that BIAPPS_BIACOMP has got a Default Schema defined (and that connectivity is in place). If there is no Physical Schema defined, then check the option. 


If this does not solve the problem, check BIAPPS DWH Physical Connection. Make sure that at least one of the physical schemas is marked as Default Schema.

Somehow during migration activities, we lost the selection on one of them.

lunedì 29 febbraio 2016

Configuring PGP Keys in GNU Private Guard

Imagine you are receiving a PGP encrypted file from a third-party. 
The file has been encrypted using the customer's Public Key and now you have to decrypt it in a separate server where Gnu PG is installed. 
The first thing you need to do is to import the keys so they can be used to decrypt the message.
The following command is used to import the Public Key: 

gpg --import newkey.txt
If a valid Public Key is given, this is how the command output should look like: 

bash-4.1$ gpg2 --import newkey.txt
gpg: key xxxxxx: "Public Key Description <>" not changed
gpg: Total number processed: 1
gpg:              unchanged: 1

To import the Private Key, use the following command: 

gpg --allow-secret-key-import --import newpriv_key.txt
To check if keys have been imported correctly, you can use the following command:

bash-4.1$ gpg --list-keys
pub   2048R/xxxxxxx 2016-02-17 [expires: 2019-02-16]
uid                 Public Key Description <>

sub   2048R/xxxxxxx 2016-02-17 [expires: 2019-02-16]

The Private Key provided has got a passphrase. So if you want to decrypt a sample file that has been encrypted using the associated Public Key, you should use the following command.

bash-4.1$ echo <passphrase>|gpg --output test.txt --batch --passphrase-fd 0 --decrypt test.gpg
gpg: encrypted with 2048-bit RSA key, ID
XXX450C8 , created 2016-02-17
Public Key Description <>"
bash-4.1$ more test.txt
Hello World!!!


If you are getting an error when passing the passphrase, try setting up the following variables: 

export GPG_TTY=`tty` 

By doing this, I was able to decrypt the message successfully.