Sample Code

Also visit the scripts/ sample code section at Scripts.

Delete duplicate rows from a table

DELETE table_name
 WHERE rowid IN
  (SELECT LEAD(rowid) OVER (PARTITION BY pk ORDER BY whatever)
     FROM table_name);

DELETE table_name
 WHERE rowid IN
  (SELECT LEAD(rowid) OVER (PARTITION BY pk ORDER BY whatever)
     FROM table_name);

Check if string is numeric

FUNCTION IsNum(str  VARCHAR2) RETURN BOOLEAN IS
  num NUMBER;
BEGIN
  num := ToNumber(str);
  RETURN True;
EXCEPTION
  WHEN Number_Or_Value_Error THEN
    RETURN False;
END;

FUNCTION IsNum(str  VARCHAR2) RETURN BOOLEAN IS
  num NUMBER;
BEGIN
  num := ToNumber(str);
  RETURN True;
EXCEPTION
  WHEN Number_Or_Value_Error THEN
    RETURN False;
END;

Converting numbers to words

SELECT TO_CHAR(TO_DATE(123,'J'),'JSP') to_words
FROM   dual;

TO_WORDS
------------------------
ONE HUNDRED TWENTY-THREE

Convert a CSV file to XLS

Dim myXL
Dim strfileName As String
 
strfileName = "c:\ACS\" & (Dir("c:\ACS\*.csv", vbNormal))
Set myXL = CreateObject("excel.application")
myXL.Workbooks.Open strfileName
myXL.DisplayAlerts = False
myXL.ActiveWorkbook.SaveAs "c:\ACS\Dailyalert.xls", -4143
myXL.DisplayAlerts = True
myXL.ActiveWorkbook.Close False
 
Set myXL = Nothing