December » 2006 » Kirtan Desai

Detect Duplicates in a Table (Oracle)

Posted on December 18, 2006 by Administrator and has 3 comments.

This question is asked more times than one would imagine. Question: How to detect duplicates in a table? Answer: There are multiple ways of doing things. Here are some of them. Use of group by select keys, count(*) from mytable group by keys having count(*) > 1 For Example: SQL> select * from t1; A [...]

Numbers to words!!!

Posted on December 10, 2006 by Administrator and has no comments yet.

A simple way of spelling out numbers to words. This could be handy for printing reciepts, cheques etc. KDE>select to_char( to_date( abs(&num),’J'),’Jsp’) “NUMBER TO TEXT” from dual 2 / Enter value for num: 345 old 1: select to_char( to_date( abs(&num),’J'),’Jsp’) “NUMBER TO TEXT” from dual new 1: select to_char( to_date( abs(345),’J'),’Jsp’) “NUMBER TO TEXT” [...]

Audit DDL Changes

Posted on December 10, 2006 by Administrator and has 1 comment.

Here is a way of keeping track of all the changes made to a schema by DDL statements. DROP TRIGGER DDL_CHANGE_TRACKER / DROP TABLE dll_audit_log / CREATE TABLE ddl_change_tracker ( stamp DATE, username VARCHAR2(30), osuser VARCHAR2(30), machine VARCHAR2(30), terminal VARCHAR2(30), operation VARCHAR2(30), objtype VARCHAR2(30), objname VARCHAR2(30)) / [...]