IKM Oracle Data Integrator SCD Type 2 Bug

Oracle Data Integrator 10.1.3.5
IKM Slowly Changing Dimension Type 2

Problem Statement:

Implement a SCD Type 2 for a table.
Three columns form the Natural Key for the table and all other columns need to be tracked for change.
If the non Natural Key columns were marked as “Overwrite On Change” in the “Slowly Changing Dimensions Behaviour”, the IKM worked fine but if the non Natural Key columns were marked as “Add Row on Change”, the IKM didn’t do anything.

This is a reported bug no. 8312924 and a patch is available. Metalink note 788747.1 describes the problem and workaround if you cannot deploy the patch.

Solution:

Changed the IKM Step 172 and replaced CX_COL_NAME with EXPRESSION courtesy of forums:

http://forums.oracle.com/forums/thread.jspa?messageID=3816462
and
http://forums.oracle.com/forums/thread.jspa?messageID=3631382&#3631382

Windows Oracle Apex upgrade on XE from 2.2 to 3.2

I was tempted to upgrade my Apex installation to 3.2 from default (2.2) that comes along with Oracle XE database.
I followed the instructions on
http://www.oracle.com/technology/products/database/application_express/html/3.2_and_xe.html
and
http://blogs.oracle.com/SanthoshK/2008/09/oracle_xe_apex_3x_installation.html

But finally got struck during the step of loading images. I had kept the APEX install on D:

SQL> @apxldimg.sql D:\Downloads\Oracle_Tools\apex_3.2

PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'D:\Downloads\Oracle_Tools\apex_3.2/apex/images'

Directory created.

declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
Access is denied.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "SYS.XMLTYPE", line 287
ORA-06512: at line 15

I tried several alternatives like changing the apxldimg.sql script and googled atleast 2 hours before trying a simple solution.

I copied the apex installation folder from D: drive to
C:\apex_32 and reran the step

SQL> @apxldimg.sql c:\apex_32

PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'c:\apex_32/apex/images'

Directory created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.

timing for: Load Images
Elapsed: 00:01:01.59

Directory dropped.

Now I can see all the images on http://localhost:8080/apex
Apparently, there are quirks involving any other drive than C: that need to be resolved by Oracle. But for the time this is the quickest way to resolve and enjoy the functionalities of Apex 3.2

Solution for Internet Browser misbehaving after CA Antivirus Install

I switched from Norton to CA that comes with my subscription of TimeWarner Cable.
Everything was fine till I opened my web browser(s). The web-browser will not work as desired.

• No cookie will get stored
• The layer effect of browser did not work anymore.

Yes, this happened to all 3 browsers that I use (Internet Explorer, Firefox, Google Chrome).
So, I was sure that something to do with the install of the new anti-virus suite.

Assuming that some system files were overwritten, I unnecessarily upgraded from XP-SP2 to Service Pack 3.
But to no avail. The browsers worked still the same.

Finally, I started playing with the Firewall settings of the CA Security Suite 2007.
I found out that the Cookie settings were blocking all cookies, so I set it to Medium (Do not store 3rd party cookies).
And also, I changed the setting for Popup/Pop-under ads. I set them to Medium too. This was disabling all browsers from giving a layered effect for several websites.

Use xmlagg in Oracle to concat multiple rows into String

Or in other words, Pivot the values from the results of a query.

The following article contains a lot of information about this problem that we usually face:
http://tkyte.blogspot.com/2007/03/stringing-them-up.html

Read the comments section.

This is the most intuitive solution that I prefer:

SELECT deptno,
       SUBSTR (REPLACE (REPLACE (XMLAGG (XMLELEMENT ("x", ename)
                                 ORDER BY ename),'</x>'),'<x>','|'),2)  as concated_list
FROM emp
    GROUP BY deptno
    ORDER BY deptno;

Step by Step guide to Install Oracle 11g on Ubuntu VM

After downloading the Ubuntu VMWare image, there were few more quirks to settle.
I had to install NFS and SSH on the machine to make it accessible by ssh from Windows.

Following are the commands:
sudo apt-get install openssh-server openssh-client

sudo apt-get install nfs-kernel-server nfs-common portmap

One of the forums that helped were:
http://ubuntuforums.org/archive/index.php/t-492772.html

Now I setup the samba share on this machine

sudo aptitude install samba

https://help.ubuntu.com/community/SettingUpSamba

After installing the samba, create a directory on the FS which you want to share with Windows or other systems.
Then edit /etc/samba/smb.conf and add these lines to the end

[share_name]
        comment = Share to Linux
        path = /home/user/winshare
        guest ok = yes
        writeable = yes

You can use the following commands to restart the services if needed:
/etc/init.d/nfs-common restart
/etc/init.d/samba restart

Following is a suggested helpful link:
http://www.howtogeek.com/howto/ubuntu/create-a-samba-user-on-ubuntu/

Process to install the Oracle on Ubuntu starts

This page is particularly helpful and is worth reading every bit:
http://www.pythian.com/blogs/654/installing-oracle-11g-on-ubuntu-linux-710-gutsy-gibbon

It mentions the packages that need to be updated before Oracle Installation.

apt-get update
apt-get upgrade (This would update the packages from the internet and will take sometime)

Anyways follow step by step the instructions in the above link to complete the installation.

At some point of time, you will encounter insufficient disk space in Linux due to which you cannot install Oracle.
To add more disk space to the Linux VM, follow the link below
http://www.matttopper.com/?p=25

I allocated a 20GB SCSI HD to Linux VM and split it into 3 partitions. The FSTAB entries are:
/dev/sdb1 /u01 ext3 defaults 1 1
/dev/sdb2 /oradata ext3 defaults 1 1
/dev/sdb3 /data ext3 defaults 1 1

Also, the Oracle installer needs a GUI. So, you need an X-Server (like eXceed) to complete the installation.
Simply on command prompt, set DISPLAY

For bash- > export DISPLAY=ExceedHostIP:0.0

And then start Exceed on Windows and connect to the IP address of the VM linux. Try 3-4 times to connect as it may error out.

If you get a timeout error, ignore the message and press nothing. Continue with Installation and complete the post-installation steps in
http://www.pythian.com/blogs/654/installing-oracle-11g-on-ubuntu-linux-710-gutsy-gibbon

Good Luck

Installing APEX on Oracle 11g from Database Troubleshooting

I installed Oracle 11g on Oracle Linux from a VM Image. So I am good with Oracle Installation.

Now my interest is to use APEX on this database.

I followed the instructions on the following page:
http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/db_install.htm#CBHCDBAB

After the installation is complete and I tried to access
http://localhost:8080/apex/apex_admin, I kept getting username/password for xdb account.
I tried unlocking the account xdb and anonymous using
alter user xdb account unlock;
alter user anonymous account unlock;

It didnt work.

If it works for you then, you will be able to access the site. That site requires admin as username.
You will need to change the password for this user in the database by executing the following command:
sql> @apxxepwd.sql <admin123>
This command is found in $ORACLE_HOME/apex

Then I tried executing apex_epg_config script found in
/ora/db/11.1.0/apex

It asks for a parameter. This parameter should be /ora/db/11.1.0
eg.
sql> @apex_epg_config $ORACLE_HOME

It basically loads the apex images to the database. Instructions for executing this script is not well-documented.

But still the problem remains the same. I am not able to access the website.

Now I stop the listener.
$ lsnrctl stop

and then
$lsnrctl start

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Starting /ora/db/11.1.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /ora/db/11.1.0/network/admin/listener.ora
Log messages written to /ora/diag/tnslsnr/oracle2go/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle2go.us.oracle.com)
(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle2go.us.oracle.com)
(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 03-FEB-2009 08:26:18
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ora/db/11.1.0/network/admin/listener.ora
Listener Log File /ora/diag/tnslsnr/oracle2go/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle2go.us.oracle.com)(PORT=1521))
)
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Then waited for 5 minutes. Somehow it didnt work for me the next instant. I think it takes some time to refresh.

Now I can access the
http://localhost:8080/apex website.

The key is to restart the listener after unlocking the xdb account.

There is another page that I hit while searching for the solutions to APEX installation:
http://www.uaex.edu/srea/Application_Express_Installation.htm

Funny Matrix Windows XP Video

I stumbled upon this hilarious Matrix and Windows XP video

BSNL DNS Internet access disrupted yet Skype is functional

Users of BSNL might encounter this issue quite often.
No website can be opened using Internet Explorer, Firefox, Chrome, etc. Yahoo Messenger wont work too.
But Skype works without a problem.

The reason :
The DNS server of BSNL doesn't work for some reason. Each website that you try to access eg. www.neooug.org gets translated to an IP address. This translation is provided by the DNS server. And if the DNS server is unavailable, your computer doesn't get back an IP address and hence you are unable to access the website.
But if you try to access the website using the IP address directly ( On command prompt : ping www.yahoo.com
gives you
H:\>ping www.yahoo.com

Pinging www.yahoo-ht3.akadns.net [69.147.76.15] with 32 bytes of data:

Reply from 69.147.76.15: bytes=32 time=10ms TTL=54
Reply from 69.147.76.15: bytes=32 time=10ms TTL=54
Reply from 69.147.76.15: bytes=32 time=10ms TTL=54
Reply from 69.147.76.15: bytes=32 time=16ms TTL=54

Ping statistics for 69.147.76.15:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 10ms, Maximum = 16ms, Average = 11ms

So, if you put http://69.147.76.15/ in the web-browser, you can access the website. But its just not possible to get the IP address if the DNS server is down.
In case of Skype, this issue doesn't arise because skype connects using the IP address.

Solution:
Open the Network Connection/Wireless connection properties on your computer. Open Properties for TCP/IP and change the "Obtain DNS server address automatically" to "Use the following DNS Server addresses"

208.67.222.222

and 

208.67.220.220

These addresses are provided by http://www.opendns.com

You can also configure your wireless router to use this address so that you don't have to configure each machine on your network to set this DNS information.

More instructions can be found at

https://www.opendns.com/smb/start