mercoledì 23 agosto 2017

[Oracle Tip] Changing Default End Date in SCD KM

ODI has an IKM for Loading SCD Type 2 dimensions. The IKM is 'IKM Oracle Slowly Changing Dimension'. The default value for Slow Changing Dimension (SCD) Type 2 End Date is "01 January 2400".
You may need to change this End Date value because of the source data that you are using to populate the Slowly Changing Dimension. In a recent project, I had to deliver an integration between Oracle Fusion HCM and an on-premise DWH. The End Date for Fusion HCM objects is "31-December-4712". So, if you want to use the ODI IKM to create SCDs you need to customize it.
To do so, first you need to create a copy of the default IKM.

Then open the duplicated object and locate the steps where the default End Date is used.
The step that needs to be customized is 'Insert flow into I$ table'. Edit the Target code to replace '2400-01-01' with '4712-12-31' or whichever value you want to use as End Date.

Once it is done, then you can use this customized IKM in your interfaces for populating SCDs Type 2. 

martedì 22 agosto 2017

[Oracle Tips] Looking for a value in all table columns of a database schema

I have been working on a project where honestly I did not have much knowledge of the data model of the schema I was using. Most of the times I had to figure out in which table (and column) I could find a certain value. Not the best strategy, but Oracle documentation and the project schedule were not not of much help.
The following procedure was extremely useful and has helped me out in many occasions. Depending on the size of the tables that are being scanned, the procedure can take quite a long time to be executed, but it will retrieve all the values that match 'v_search_string' in any column of the datatype and the schema specified as a parameters.

set serveroutput on size 100000;

    v_match_count integer;
    v_counter integer;

    -- The owner of the tables to search through (case-sensitive)

    v_owner varchar2(255) := 'SCHEMA_OWNER';
    -- A string that is part of the data type(s) of the columns to search through (case-insensitive)
    v_data_type varchar2(255) := 'COLUMN_DATATYPE';
    -- The string to be searched for (case-insensitive)
    v_search_string varchar2(4000) := 'VALUE_TO_BE_SEARCHED';

    -- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2 in PL/SQL
    v_sql clob := '';
    for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in
                       (select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' ||  upper(v_data_type) || '%')
                       order by table_name) loop
        v_counter := 0;
        v_sql := '';

        for cur_columns in (select column_name from all_tab_columns where
                            owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
            if v_counter > 0 then
                v_sql := v_sql || ' or ';
            end if;
            v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
            v_counter := v_counter + 1;
        end loop;

        v_sql := 'select count(*) from '  || v_owner || '.' || cur_tables.table_name || ' where ' || v_sql;

        execute immediate v_sql
        into v_match_count;

        if v_match_count > 0 then
            dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
        end if;
    end loop;

        when others then
            dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600));

Here is the output of the procedure if I look for 'CRISTINA' string of 'VARCHAR' type in any table column in the 'A' schema: 

Note: the procedure will work only if you have the right privileges to query all_tables and all_tab_columns and you have at least read privileges to the schema

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>