Ora Analytic Func

Wednesday, July 2, 2008

NULLs in ANSI SQL standard

Hi
I just happened to come across a material about the ANSI SQL standards. I read the following about NULLs - an interesting phenomenon in Data bases.
The null value
Every data type includes a special value, called the null value, sometimes denoted by the keyword
NULL. This value differs from other values in the following respects:
— Since the null value is in every data type, the data type of the null value implied by the
keyword NULL cannot be inferred; hence NULL can be used to denote the null value only in
certain contexts, rather than everywhere that a literal is permitted.
— Although the null value is neither equal to any other value nor not equal to any other value —
it is unknown whether or not it is equal to any given value — in some contexts, multiple null
values are treated together; for example, the treats all null values together.
Thanks,
Soma

Friday, March 7, 2008

Monitoring OWB mappings / jobs

Hi all,

Its been a long time since i entered into this arena of OWB and oracle. Now i am back again with some quiver full of informations to share. I will share it one by one. Previously i heard about the OWB Monitoring thru Repository Audit Browser - for which we have to start the browser listner.

I have come across the following link. which is used to check the status of the running mappings.

http://kleinoracledenkraam.wordpress.com/2007/05/24/owb-monitoring/
SELECT
e.execution_name,
e.return_result,
e.creation_date,
e.last_update_date,
e.elapse,
TO_CHAR (TRUNC (SYSDATE, ‘DD’) + e.elapse / (24 * 3600), ‘HH24:MI:SS’) AS elapse_format,
wb_rt_constants.to_string (e.audit_status) AS audit_status_symbol, e.audit_status,
DECODE (x.err, NULL, 0, x.err) AS error#,
DECODE (x.sel, NULL, 0, x.sel) AS selected,
DECODE (x.ins, NULL, 0, x.ins) AS inserted,
DECODE (x.upd, NULL, 0, x.upd) AS updated,
DECODE (x.del, NULL, 0, x.del) AS deleted,
DECODE (x.dis, NULL, 0, x.dis) AS discarded,
DECODE (x.mer, NULL, 0, x.mer) AS merged,
DECODE (x.cor, NULL, 0, x.cor) AS corrected,
wb_rt_constants.to_string (m.severity) AS severity_symbol,
m.severity,
l.plain_text,
p.parameter_name,
p.value,
m.creation_date,
e.task_object_name,
e.task_object_store_name,
e.task_name,
e.audit_status,
e.top_level_audit_execution_id,
e.audit_execution_id,
e.parent_audit_execution_id
FROM
wb_rtv_audit_executions e
LEFT JOIN wb_rtv_audit_messages m
ON e.audit_execution_id = m.audit_execution_id
LEFT JOIN wb_rtv_audit_message_lines l
ON l.audit_message_id = m.audit_message_id
LEFT JOIN
(SELECT e.audit_execution_id, SUM (a.rta_errors) AS err,
SUM (a.rta_select) AS sel, SUM (a.rta_insert) AS ins,
SUM (a.rta_update) AS upd, SUM (a.rta_delete) AS del,
SUM (a.rta_discarded) AS dis, SUM (a.rta_merge) AS mer,
SUM (a.rta_corrections) AS cor
FROM wb_rt_audit_executions e
LEFT JOIN wb_rt_audit a
ON e.audit_execution_id = a.rte_id(+)
GROUP BY e.audit_execution_id) x
ON e.audit_execution_id = x.audit_execution_id
ORDER BY last_update_date DESC;

Thanks,
Soma

Monday, December 24, 2007

OWB Workflow

Today as usual i have browsed some of the links for OWB. I have come across this link in google.
http://cali97.blogspot.com/search/label/OWB
I am sure, out of 4 blogs, i dont have knowledge on 3. Then the blogger have blogged the following about a client interview question which explains about running the process flow from outside.

"I recently was contacted by one of my former clients that had a pretty good question. I actually had this questions asked a few times by various parties, so here is a summery and an answer!"Quick OWB question for you: Is it possible to execute process flows from outside of OWB once they have been created. Wondering if there is a template provided by oracle(like “Sql_exec_template for OWB mappings) to execute Process flows…"Well, OWB does contain a pl/sql package called WB_RT_API_EXEC with a function inside it called RUN_TASK that submits execution requests to the OWB runtime engine. The requests could be for mappings, process flows or scheduled jobs. The function spec looks like:

function run_task
(
p_location_name in varchar2,
p_task_type in varchar2,
p_task_name in varchar2,
p_custom_params in varchar2 default null,
p_system_params in varchar2 default null,
p_oem_friendly in number default 0,
p_background in number default 0)
return number;

This is actually the same function used by the sqlplus_exec_template.sql script in OWB 10gR2 that you mentioned in your email. It can be called using (assuming a repository owner connection exists):

(this is an example…)
declare
result_num number;
begin
result_num:= wb_rt_api_exec.run_task('PFMOD_LOCATION1', 'PROCESS', 'MY_PKG/MY_PF', ',', ',', 0 ,0);
end;

The full documentation of the function can be found in /owb/rtasst/wb_rt_api_exec.pl"

I have to go thru this documentation to update my knowledge. Will update you all on this.
Thanks,
Soma

Musings - OWB Workflow

I already know how to check whether the Control Center (CC) service is available or not using the template sql provided along with the OWB client installation.

I have come across this following thread in Oracle Warehouse Builder forum which explains how we can check the Workflow installation.

http://forums.oracle.com/forums/thread.jspa?threadID=597761&tstart=30

One guy has posted the following query to check the workflow installation.

SELECT
TEXT VERSION
FROM
.WF_RESOURCES
WHERE
TYPE = 'WFTKN'
AND NAME = 'WF_VERSION'".( - default owf_mgr)

I have checked with my repository installation. When I run the query I got table doesn’t exist. In that server we have installed Oracle Workflow (no idea on version).

OWB Useful links

Sunday, November 18, 2007

Creating Materialized View

You need sufficient privileges to create a materialized view in the particular DB.

At first I tried with an user name and password to create a materialized view. The particular User name doesn’t have privileges to create a materialized view.

CREATE MATERIALIZED MV_TEST

SELECT * FROM EMPLOYEE;

I got the following error when I try to create a materialized view.

ORA-01031: insufficient privileges

So I need a Role to be granted to create materialized view.

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

GRANT SUCEEDED.

Then I tried with the above create statement with the corresponding user name. I can able to create Materialized view

Materialized View Refresh Methods

Today I went through some of the articles about the materialized view. In that article they mentioned that there are 3 types of MV Refresh Methods.

1) Force Refresh

2) Fast Refresh

3) Complete Refresh

These 3 types which are used while scheduling the Materialized view refesh.

Other that there are another method of refreshing which will be helpful when you are constantly changing the query of materialized view

On Demand Refresh – There is a oracle package which will help you to refresh the materialized view (DBMS_MVIEW.REFRSH)

We will explore about this package in upcoming blogs.