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

1

2

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

3

Disable logging

4

5

Set Up to Use Heterogenous Service

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

References

  1. http://www.easysoft.com/applications/oracle/hsodbc.html

XML Parsing Error on Oracle 10g 10.2.0.5

We were getting many of these errors randomly, without any obvious pattern. On 10.2.0.4 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 10.2.0.5 [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
is
cursor cur_job_status(p_job_name varchar2) is
select t.STATE from USER_SCHEDULER_JOBS t
where t.JOB_NAME = p_job_name;

l_job_status cur_job_status%rowtype;
begin
/*
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;

return(l_job_status.state);
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;

dbms_xmldom.writetoclob Escapes XML Special Characters Like Ampersand or Double Quotes

dbms_xmldom.writetoclob by default escapes XML special characters like ampersand or double quotes. Here is a test in Oracle 10.2 in PL/SQL.

 -- Created on 2012-03-15 by MINDAUGASL
declare
-- Local variables here
i integer;
p_wrapped_text clob;
l_doc                dbms_xmldom.domdocument;
l_element            dbms_xmldom.domelement;
l_el_paragpraph      dbms_xmldom.domelement;
l_node               dbms_xmldom.domnode;
l_node_doc           dbms_xmldom.domnode;
l_node_paragraph     dbms_xmldom.domnode;
l_node_prgph_prop    dbms_xmldom.domnode;

l_checkinactionvalue dbms_xmldom.domtext;
l_text               varchar2(32676) := 'aaa &%&&%"""';
begin
-- Test statements here
l_doc      := dbms_xmldom.createdocument('uri:w', 'w');
l_node_doc := dbms_xmldom.makenode(l_doc);

l_checkinactionvalue := dbms_xmldom.createtextnode(l_doc, l_text);
l_node               := dbms_xmldom.appendchild(l_node_doc,
dbms_xmldom.makenode(l_checkinactionvalue));

dbms_lob.createtemporary(p_wrapped_text, true);
dbms_lob.open(p_wrapped_text, dbms_lob.lob_readwrite);
dbms_xmldom.writetoclob(l_doc, p_wrapped_text);
dbms_lob.close(p_wrapped_text);

dbms_output.put_line(' ' || p_wrapped_text);
end;
 

Warehouse glossary

Tutorial

Bulding warehouse tutorial – very good tutorial, with a lot of information from basics.

Dimension Hierarchies

Dimension hierarchies introduce formal hierarchies into a business model, allowing Oracle BI Server to calculate useful measures and allowing users to drill down to more detail. In a business model, a dimension hierarchy represents a hierarchical organization of logical columns belonging to a single logical dimension table. Common dimension hierarchies used in a business model are time periods, products, customers, suppliers, and so forth.

Dimension hierarchies are created in the Business Model and Mapping layer and end users do not see them in end user tools such as Oracle BI Answers or Interactive Dashboards. In each dimension hierarchy, you organize dimension attributes into hierarchical levels. These levels represent the organizational rules and reporting needs required by your business. They provide the structure that Oracle BI Server uses to drill into and across dimensions to get more detailed views of the data. Dimension hierarchy levels are used to perform aggregate navigation, configure level-based measure calculations, and determine what attributes appear when Oracle BI users drill down in their data requests.

Logical tables

There are two main categories of logical tables: fact and dimension.

Fact tables

Logical fact tables contain the measures by which an organization gauges its business operations and performance.

Dimension tables

Logical dimension tables contain the data used to qualify the facts.

 

Source: Creating a Repository Using the Oracle Business Intelligence Administration Tool

ORA-12516: TNS:listener could not find available handler with matching protocol stack

Usually this error is got when Oracle express edition is used. It means, that .NET connection pool has many open connections hanging, too many for oracle.

To see how many sessions there are, log in as SYSDBA and execute this statement:

select count(*) from gv$session

Then check these two parameters in oracle:

select name, value from v$parameter where name in (‘processes’,’sessions’);

Then alter these parameters. Oracle professional has value of processes = 300. So you can increase it till this value. Commands to do that:

alter system set processes=300 scope=spfile;
alter system set sessions=300 scope=spfile;

 

This is kind of temporary solution. Usually this means that .NET program has some problems like forgotten to close connections. Or as we had using Subsonic 2.x with custom oracle provider and Subsonic.DatabaseTransaction object.