Oracle 10gR2 Heterogenous Services Setup for Warehouse Builder

Heterogenous services configs are at ORACLE_HOME\hs\admin

To create a new connection to MS SQL 2008 server. Create new HS config file. File name must be “iniths*.ora”. Let’s say “inithsmssql.ora”.

Create ODBC 32bit System DSN.

Add New Listener



Change port to other then 1521. Let’s say to 1522.


Disable logging



Set Up to Use Heterogenous Service
























XML Parsing Error on Oracle 10g

We were getting many of these errors randomly, without any obvious pattern. On version there didn’t exist. Finally we found a Oracle patch. After applying it, these annoying errors has gone. Patch number is

Oracle support articles [ID 9871430.8] Bug 9871430 – ORA-31011 / LPX-230 converting CLOB to XMLTYPE and Loading XML Content Fails with LPX-00230 After Upgrading the Database to [ID 1167777.1]

Solution: Apply the patch for bug 6151936

-31011 ORA-31011: XML parsing failed

ORA-19202: Error occurred in XML processing

LPX-00230: invalid character 0 (U+0000) found in a Name or Nmtoken

Error at line 1.

-31011 ORA-31011: XML parsing failed

ORA-19202: Error occurred in XML processing

LPX-00225: end-element tag "Resolution" does not match start-element tag "ὖexecutors"

Error at line 1.

Select Other User Job Status Without SYS

I needed to select other user’s jobs and check their job status. By default user_scheduler_job or all_scheduler_job selects jobs for which user has alter job privilege. Basically user sees only jobs which he created.

Let say user_guest wants to see user_owner’s job status. First create a procedure in user_owner schema which selects from current user job list.

create or replace function get_job_status(p_job_name varchar2) return varchar2
cursor cur_job_status(p_job_name varchar2) is
where t.JOB_NAME = p_job_name;

l_job_status cur_job_status%rowtype;
Aprašymas: Get status of TPZ job.
Sukūrė: ZBU 6/14/2012 2:25:23 PM
open cur_job_status(p_job_name);
fetch cur_job_status into l_job_status;
close cur_job_status;

end get_job_status;


Then create a view in user_owner schema, which selects from procedure and returns job status as query result.

create or replace view tpz_adm_rcws_job_status as
select get_job_status('JOB_NAME') job_status
from dual;

Finally grant select to user_guest from user_owner on that view.

 grant select on tpz_adm_rcws_job_status to user_quest;

JAVA_HOME environment variable is defined correctly in OBIEE 10g

After removing several JAVA installations on my Windows XP Prof. SP3 machine and leaving the newest version,  I can not start OBIEE 10g.

It looks like, that.

First starting OC4J you get error ” JAVA_HOME environment variable is defined correctly.”  Adding it to Windows system or user environment variables won’t solve.



Then you need to analyse more batch file which is used by OC4J startup link. There I found that JAVA version, which was used by OC4J, was removed by my self. I removed it, because it was older than later JAVA installations. This caused OC4J not to start, because it simply was missing folder in Program Files directory.

What I did, I created the required folder and copied newer version Java files to that directory. I’m not sure if that good solution, because now I get some encoding problems in exported PDFs for Lithuanian language in OBIEE 10g dashboards.

So, general advice is don’t delete older Java versions from PC where OBIEE 10g runs, or just don’t upgrade Java on that computer.

OBIEE 10g and OBIEE 11g running on same computer

Today in a hurry I installed Oracle Business Intelligence EE 11g on the same machine as Oracle Business Intelligence 10g. OBIEE11 started up and I could login. But the OBIEE10 I could not access, because default path to access dashboard was overridden by 11g version. Possible and other paths were overridden.

After restarting my PC, both version did not work 🙂

My advice is to make separate virtual PCs.

Update 2010.09.21

I uninstalled OBIEE 11g and successfully restarted OBIEE 10g services. OBIEE 10g installation hadn’t broke.

PL/SQL Developper Tools


PL/SQL developer – tool, that currently I’m working with. Fast, but interface is from stone age :).

Toad – king of pl/sql and oracle developer tools? Maybe 🙂 Haven’t tried fully, just remember a lot of buttons.

OraDeveloper Studio – looks like it’s built on visual studio kernel. It should be quite fast. Other thing, that it supports multiple connections at the same time. While PL/SQL developer – don’t.


Tora – open-source multi-platform database management GUI that supports accessing most of the common database platforms in use, including Oracle, MySQL, and Postgres, as well as limited support for any target that can be accessed through Qt’s ODBC support. TOra has been built for various Linux distributions, Mac OS X, MS Windows, and UNIX platforms.

SQL Developer – from Oracle, portable, built on Java.

Toby’s PL/SQL Editor – PL/SQL Editor Plugin for Eclipse using file and database access for auto completion and many standard source editing capabilities of eclipse. Designed to support editing the Oracle based PL/SQL language. Written in java, supported on all platforms.