Dec
18
Detect Duplicates in a Table (Oracle)
December 18, 2006 | 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 B
———- ———-
1 1
1 2
1 1
1 3
[…]
Dec
10
Numbers to words!!!
December 10, 2006 | Leave a Comment
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” from dual
NUMBER TO TEXT
————————
Three Hundred […]
Dec
10
Audit DDL Changes
December 10, 2006 | Leave a 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))
/
CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER create OR drop OR alter
ON KIRTAN.SCHEMA
BEGIN
INSERT INTO […]