LAG & LEAD Analytic function in Oracle

The LAG and LEAD  and  other analytic functions are available in Oracle to  give access to multiple rows within a table, without the need for a self-join. These function will be very handy.
For example: 
To get previous record column value
To calculate difference between column value. 



http://oracle-base.com/articles/misc/lag-lead-analytic-functions.php

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions070.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions074.htm#i83834

http://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php

Using sql count/sum in a case statement in Oracle

Note :
For COUNT : need to use null  value you like to ignore
For SUM  : need to use null /zero value you like to ignore




select count(case
               when retrieve_time is null then
                null
               else
                1
             END) as retrieved
  from TABLE_A
 where CREATED_TIME > gmt_sysdate - 30;


select sum(case
             when retrieve_time is null then
              0
             else
              1
           END) as retrieved
  from TABLE_A
 where CREATED_TIME > gmt_sysdate - 30;

 select sum(case
               when retrieve_time is null then
                null
               else
                1
             END) as retrieved
  from TABLE_A
 where CREATED_TIME > gmt_sysdate - 30;



select to_char(CREATED_TIME, 'YYYY/MM/DD'),
       count(case
               when retrieve_time is null then
                null
               else
                1
             END) as retrieved
  from TABLE_A
 where CREATED_TIME > gmt_sysdate - 30
 group by to_char(CREATED_TIME, 'YYYY/MM/DD');

Oracle Utilities

REF :

1. http://www.orafaq.com/wiki/DBMS_SCHEDULER
2. http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm
3. http://www.oracleutilities.com/OSUtil/tkprof.html
4. http://www.oracleutilities.com/
5. http://www.oracleutilities.com/Packages/TraceAnalyzer_IOUG.pdf
6. http://www.oracleutilities.com/Ora10GUtilities.pdf
7. http://www.comp.nus.edu.sg/~ooibc/courses/sql/

File handling utilities

wget & curl

wget 'FILE_URL'

http://en.wikipedia.org/wiki/Wget

http://daniel.haxx.se/docs/curl-vs-wget.html

SCP

scp  FILE USERNAME@HOST:DESTIONATION_DIR

FTP

Transfer multiple file through FTP mput , mget & mdelete.

#! /bin/sh

REMOTE='host'

USER='username'

PASSWORD='pwd'

FTPLOG='ftpmput.log'

date >> $FTPLOG

ftp -n $REMOTE <<_ftp>>$FTPLOG

quote USER $USER

quote PASS $PASSWORD

bin

cd /support/touat/

mput *.txt

quit

_FTP

lftp 

sftp

Expose local directory images through Tomcat Sever

Option 1
Enable directory listing for specific URL pattern in web.xml

    listImages
    org.apache.catalina.servlets.DefaultServlet
    
        debug
        0
    
    
        listings
        true
    
    100



    listImages
    /listImages/*

Option 2 NOTE: This will affects all folders in the webapp. It is best to enable directory listing only for an individual folder by writing own Servlet to handle the request.

1.Create a Context element in an XML file: Image.xml

2.Copy the Image.xml file to $CATALINA_HOME/conf/Catalina/localhost/.

3.Enable Directory listing in in Tomcat. Edit the default servlet in the {$CATALINA_HOME}/conf/web.xml file.

    
        default
        org.apache.catalina.servlets.DefaultServlet
        
            debug
            0
        
        
            listings
            true
        
        1
    

4.Change the param-value; to true for the param-name listing section.

        
            listings
            true