Kde…

COALESCE is one of many convenient ways provided by oracle to handle NULL values in the database. The Oracle COALESCE function accepts a varying length list of arguments and returns the first non-NULL value/expr in the list. If all arguments in the list evaluate to a NULL value, then the COALESCE function will return a NULL value. Instead of scanning through all the arguments, oracle takes a short-circuit evaluation approach to processing what’s provided. In other words, it evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL. As soon as it find the first non-NULL value it will stop processing rest of the provided arguments.

There is always more than one way to achieve the same outcome. You can use COALESCE as a variety of the CASE or DECODE expression.

For example,
Using DECODE, you would do something like

SELECT DECODE(mgr, NULL, 9999, mgr) FROM SCOTT.EMP

OR

Using CASE, you would do something like

SELECT
CASE
WHEN MGR IS NULL THEN
9999
ELSE
MGR
END
FROM SCOTT.EMP;

The following demonstrates how to substitute in a default value in a SELECT statement when there are NULL values present using COALESCE function.

SELECT COALESCE(mgr, 9999) FROM SCOTT.EMP;

The statement above looks much cleaner, well at least to everyone who knows what’s going on. In examples above, 9999 is a default value that you would like to assign to any null values in the list.

But a better use of COALESCE would be when more than 1 value is passed to it as input.\
For example,

SQL> select coalesce(null,null, 5, null) from dual;

SQL>

As you can notice COALESCE picks up the first non-null value from a given list.

hope this helps.
Kirtan

There was a question asked on InformaticaDevelopement yahoo group about parsing names. I had a piece of code saved from asktom. I don’t know when I got it from asktom, but I know I got it from asktom. I just wanted to put it out here for reference. To parse names you would have to do the following.

SQL>create table name_test ( a varchar2(15) );
Table created.SQL>insert into name_test values ( 'kirtan' );

hope this helpsKirtan

Flashback features were there in 9i. 10g is providing something new/extra.

### From new features guide  ###
What if you ever need to do a resetlogs operation for recovery purposes, you can take the
database back to a point in time before the resetlogs if you find that you made a
mistake. This provides administrators more flexibility to detect and correct human
error situations.
It is now possible to flashback the primary and standby databases to an SCN or a point in
time prior to the switchover operation. Using flashback in this way on a physical standby
database preserves the standby role. Using flashback in this way on a logical standby
database changes the role of the standby database to what it was at the target SCN/time.
###    ###
Here is how you can use Flashback options.

SQL> create table ksdesai.fstes (a number(2));Table created.

         A
----------
         3
         4
         5
         2
         1
Magical!!! Or is it?

Kirtan

####UPDATES####

Retriving delete records from a table within certain period.


SQL> create table t1 (a number);

######################

SQL> delete from t1 where a=2;

         A
----------
         1
         3
         2

######################

I just wrote this procedure below that does duplicate file (dataset, i should say) check.

Let’s say, you have a system where your clients/users have rights to write straight to your “staging” or “landing” tables. You have a system where multiple users are feeding multiple files everyday. You have a requirement to check whether users are sending duplicate files into your system intentionally or unintentionally. You could probably find a million ways of doing a duplicate file check. I just wrote a simple stored procedure that does that for me. Let’s have a look at it. \


create or replace PROCEDURE clob_to_hash IS
 i number(5);
 mychar VARCHAR2(4000);
 myclob CLOB;
 myraw raw(2000);
 rec_cnt NUMBER(2);
 CURSOR mycur1 IS
  SELECT column_name AS
  field_name
  FROM sys.all_tab_cols
  WHERE owner = 'SCOTT'
  AND TABLE_NAME = 'EMP'
  ORDER BY column_id;
 mycur mycur1%ROWTYPE;
 col_names VARCHAR2(4000);
 mysqlstr varchar2(4000);
BEGIN
 i:=0;
 OPEN mycur1;
 LOOP
  FETCH mycur1 INTO mycur;
  EXIT WHEN mycur1%NOTFOUND;
  IF i = 0 THEN
   col_names := mycur.field_name;
  ELSE
   col_names := col_names || ',' || mycur.field_name;
  end if;
 i:=i+1;
 END LOOP;
 close mycur1;
 mysqlstr := 'SELECT xmlagg(xmlelement("emp",' || col_names || ')).getclobval() FROM scott.emp';
 execute immediate mysqlstr INTO myclob;
 myraw := dbms_crypto.hash(src => myclob, typ => dbms_crypto.hash_md5);
 mychar := rawtohex(myraw);
 SELECT COUNT(*) INTO rec_cnt FROM hashtest WHERE mchar = mychar;
 IF rec_cnt = 0 THEN
  INSERT INTO hashtest VALUES(mychar);
 ELSE
  INSERT INTO hashtest VALUES('duplicate data set');
 END IF;
 COMMIT;
END;


This procedure creates a list of column names of ‘emp’ table from sys.all_tab_cols in order they were created. It builds a clob of xml data out of the emp table and create an MD5 key for that clob. It converts the ‘raw’ value returned by DBMS_CRYPTO.HASH function to a ‘hex’ value. After that it tries to find a row in the “hashtest” table to see if the same ‘hash’ key exists. If it finds duplicate records, it write ‘duplicate data set’ in the hashtest table. otherwise it writes the ‘hex’ value generated from the ‘hash’ value which was returned in ‘raw’ format by DBMS_CRYPTO.HASH function.


SQL> truncate table hashtest;

That’s it!!! With a little modification, you could pretty much use this to compare any two datasets.

Below are some useful scripts that i found in one of my old hard drives. If you want to gather stat reports for the oracle ser sessions you could use the following scripts. Right before posting this i tried them on 8i, 9i, and 10g.

To find total cpu usage by each/all sessions, you could use the following sql statement.


Select   nvl ( sess.username, 'oracle_process' ) username
,        sess_stat.sid session_id, value cpu_usage
From     v$session sess
,        v$sesstat sess_stat
,        v$statname stat_name
Where    sess_stat.statistic# = stat_name.statistic#
And      name like '%cpu used by this session%'
And      sess_stat.sid = sess.sid
And      value > 0
Order by value desc
/

To find total resource usage by each user, you could use the following sql statement.

Select   sess.sid, nvl ( sess.username, 'oracle_process' ) username
,        sess_stat.sid session_id, stat_name.name statistic, value cpu_usage
From     v$session sess
,        v$statname stat_name
,        v$sesstat sess_stat
Where    sess_stat.sid = sess.sid
And      stat_name.statistic# = sess_stat.statistic#
And      value != 0
Order by value desc
,        sess.username
,        sess.sid
,        stat_name.name
/


To find session i/o activity by each user, you could use the following sql statement.

Select   nvl ( sessions.username, 'oracle_process' ) username, osuser os_user
,        process pid, sessions.sid sid, serial#, physical_reads, block_gets
,        consistent_gets, block_changes, consistent_gets, block_changes
,        consistent_changes
From     v$session sessions
,        v$sess_io session_io
Where    sessions.sid = session_io.sid
Order by physical_reads desc
,        sessions.username
/

hope this helps…

Kirtan

Using Oracle function TRANSLATE and REPLACE to remove special characters from a string.

This is the definition of the function.

TRANSLATE(expr, from_string, to_string)

Descrption: [FROM ORACLE DOCUMENTATION]

TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. If expr is a character string, then you must enclose it in single quotation marks. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed from the return value.

You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null.

Let’s see…

SQL> create table t6 (a varchar2(20));

A
--------------
ABC!@#$%^&*XYZ
SQL> SELECT TRANSLATE(a, '!@#$%^&*', ' ') FROM t6;
TRANSLATE(A,'!@#$%^&
---------------------
ABC XYZ

#####
Notice that I removed the $ (DOLLAR) sign from the list of junk.
#####

SQL> SELECT TRANSLATE(a, '!@#%^&*', ' ') FROM t6;
TRANSLATE(A,'!@#%^&*
--------------------
ABC $XYZ
#####
I would create a function that accepts a varchar2 and returns a ‘cleaned’ varchar2 for this purpose.
#####

SQL> create or replace function clean_str(in_string in varchar2)
2 return varchar2
3 is
4 out_string varchar2(4000);
5 remove_char varchar2(100);
6 begin
7 remove_char:='!@#$%^&*æ';
8 select TRANSLATE(in_string,remove_char , ' ') into out_string FROM dual;
9 return (out_string);
10 end;
11 /

CLEAN_STR(A)
------------
ABC XYZ

I kept the last argument ‘ ‘ (a space) because of the NULL rule mentioned above in the description. I just asked Oracle to assign a single space instead.

SQL> insert into t6 values ('ABC!@#$XYZ%^&*XML');

CLEAN_STR(A)
------------
ABC XYZ
ABC XYZXML

Now that I have a string like ‘ABC XYZ’ and ‘ABC XYZXML’, I am going to use Oracle function REPLACE to remove the extra space(s). The advantage of REPLACE is that it replaces all the occurances of the string/character with the provided replacement string. So lets change the function a little bit.

SQL> create or replace function clean_str(in_string in varchar2)
2 return varchar2
3 is
4 out_string varchar2(4000);
5 remove_char varchar2(100);
6 begin
7 remove_char:='!@#$%^&*æ';
8 select
9 REPLACE(TRANSLATE(in_string,remove_char , ' '),' ',NULL)
10 into out_string
11 FROM dual;
12 return (out_string);
13 end;
14 /

CLEAN_STR(A)
------------
ABCXYZ
ABCXYZXML
So now all you have to do is keep a list of unwanted or special characters in the remove_char variable of the function and you are golden.

I hope this helps.

-Kirtan

Updated!!!

I just realized that there is no need to use REPLACE function above. I will keep it as it is just in case if someone wants to use it as it is. Below is an updated(and better IMHO) version of clean_str() function. The idea is to pass the character that you want to remove as the THIRD argument and concatenate the variable remove_char with the same character. It is going to be a space for this example.

create or replace function clean_str(in_string in varchar2)
return varchar2
is
out_string varchar2(4000);
remove_char varchar2(100);
begin
remove_char:=' ' || '!@#''''$%^&*æ';
select
--REPLACE(TRANSLATE(in_string,remove_char , ' '),' ',NULL)
TRANSLATE(in_string,remove_char , ' ')
into out_string
FROM dual;
return (out_string);
end;
/


What you can also do is you can allow the character that you want to be removed as  an input to the function.

create or replace function clean_str1(in_string in varchar2,culprit in char)
return varchar2
is
out_string varchar2(4000);
remove_char varchar2(100);
begin
remove_char:=culprit || '!@#''''$%^&*æ';
select
--REPLACE(TRANSLATE(in_string,remove_char , ' '),' ',NULL)
TRANSLATE(in_string,remove_char , culprit)
into out_string
FROM dual;
return (out_string);
end;
/SQL> select clean_str1('KIR$#@%TAN',' ') from dual;