Kirtan Desai

The following is a list of books. There are three sections. First is the name of the book that I am currently reading. Second is the list of books that I am going to read in near future. And third is a list of books that I have read.

Kirtan Desai

I was reading tom kyte’s blog and found the following link there. I am not trying to copy/steal what he had there but I thought this was extremely important to publish. Not just for others but to keep reminding myself.

I think, for anyone who wants to learn anything, asking right questions is the only way to learn. Questions themselves can be directed to someone else or yourself. What’s important is that you ask questions that lead you in the right way. Check out the article below. It’s GOOD.

http://www.catb.org/~esr/faqs/smart-questions.html

Tom also wrote about this and he had his own link besides the one above on his blog. Since I am sort of “re-blogging” here, it’s only fair to provide the original links. Or just in case you want to read them both.

http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html

Kirtan.

There is an excellent article by Doug Burns here regarding the role of an individual who acts as either Developmentt DBA, Developer/DBA, DBA/Developer etc. The bottom line, as pointed out in the article, is that in a development environment where a DBMS is at the center of the project/application/system, individual(s) with strong knowledge of the DBMS must be utilized.

Leaving the future of an application or a system in the hands of developers who have little knowledge of the DBMS is not a good idea. Many companies nowadays let consulting firms come in and develop what’s needed. Many time these incoming consultants come with *consulting* experience and not with product or technology specific experience.

Even though the article is listed on SQL-Server specific website, I thought the artice was generic enough, to-the-point and meaningfull. It’s a must-read.

Kirtan

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.