GET THE TIME DIFFERENCE BETWEEN TWO DATE COLUMNS

/*
GET THE TIME DIFFERENCE BETWEEN TWO DATE COLUMNS
*/

--Solution 1
SELECT floor(((date1-date2)*24*60*60)/3600)
       || ' HOURS ' ||
       floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       round((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600 -
       (floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
       || ' SECS ' time_difference
FROM dates;

--Solution 2
SELECT to_number( to_char(to_date('1','J') +
       (date1 - date2), 'J') - 1)  days,
       to_char(to_date('00:00:00','HH24:MI:SS') +
       (date1 - date2), 'HH24:MI:SS') time
FROM dates;

--Solution 3: use numtodsinterval() function
/*
NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are 'DAY', 'HOUR', 'MINUTE' and 'SECOND'.
This function converts the number x into an INTERVAL DAY TO SECOND datatype.
*/
select numtodsinterval(date1-date2,'day') time_difference from dates;

Source: http://www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

SELECT subject, count(subject) 
FROM student_details 
WHERE subject != 'Science' 
AND subject != 'Maths' 
GROUP BY subject;

Instead of:

SELECT subject, count(subject) 
FROM student_details 
GROUP BY subject 
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as

SELECT name 
FROM employee 
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) 
FROM employee_details) 
AND dept = 'Electronics'; 

Instead of:

SELECT name 
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details) 
AND age = (SELECT MAX(age) FROM employee_details) 
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as

Select * from product p 
where EXISTS (select * from order_items o 
where o.product_id = p.product_id)

Instead of:

Select * from product p 
where product_id IN 
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

SELECT d.dept_id, d.dept 
FROM dept d 
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept 
FROM dept d,employee e 
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as

SELECT id, first_name 
FROM student_details_class10 
UNION ALL 
SELECT id, first_name 
FROM sports_team;

Instead of:

SELECT id, first_name, subject 
FROM student_details_class10 
UNION 
SELECT id, first_name 
FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age 
FROM student_details 
WHERE first_name LIKE 'Chan%';

Instead of:

SELECT id, first_name, age 
FROM student_details 
WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as

SELECT id, first_name, age 
FROM student_details 
WHERE first_name LIKE NVL ( :name, '%');

Instead of:

SELECT id, first_name, age 
FROM student_details 
WHERE first_name = NVL ( :name, first_name);

Write the query as

SELECT product_id, product_name 
FROM product 
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name 
FROM product 
WHERE unit_price >= MAX(unit_price) 
and unit_price <= MIN(unit_price)

Write the query as

SELECT id, name, salary 
FROM employee 
WHERE dept = 'Electronics' 
AND location = 'Bangalore';

Instead of:

SELECT id, name, salary 
FROM employee 
WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary 
FROM employee 
WHERE salary < 25000;

Instead of:

SELECT id, name, salary 
FROM employee 
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age 
FROM student_details 
WHERE age > 10;

Instead of:

SELECT id, first_name, age 
FROM student_details 
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

SELECT id FROM employee 
WHERE name LIKE 'Ramesh%' 
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee 
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb

Source

Reading a xlsx file in java

You may need to include some of the jar files like

• xmlbeans-2.3.0.jar
• dom4j-1.1.jar
• poi-ooxml-schemas-3.6-20091214.jar
• poi-ooxml-3.6-20091214.jar
• poi-3.6-20091214.jar

You can google it and download and include it along with the below code.

You have to include a simple xlsx file with some values in it (string values) and run the code.  This code will fetch the values and print it on the screen.

import org.apache.poi.xssf.usermodel.XSSFWorkbook; 

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import java.util.Iterator;

import java.io.*;

public class Main {

    public void ReadSheet() throws Exception

    {

        String filename = "book1.xlsx";

        FileInputStream fis = null;

        try {

            fis = new FileInputStream(filename);

            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            XSSFSheet sheet = workbook.getSheetAt(0);

            Iterator rows = sheet.rowIterator();

            int number=sheet.getLastRowNum();

            System.out.println(" number of rows"+ number);

            while (rows.hasNext())

            {

                XSSFRow row = ((XSSFRow) rows.next());

                Iterator cells = row.cellIterator();

                while(cells.hasNext())

                {

                    XSSFCell cell = (XSSFCell) cells.next();

                    String Value=cell.getStringCellValue();

                    System.out.println(Value);

                }

             }

        } catch (IOException e) {

            e.printStackTrace();

        } finally {

            if (fis != null) {

                fis.close();

            }

        }

    }

 /////////////////////////////////////////////////////////////////////////////////////

    public static void main(String[] args) {

        Main object=new Main();

        try{

        object.ReadSheet();

        }catch(Exception e)

        {

            e.printStackTrace();

        }

}

}

Source

ORACLE - Determine locked session and kill it

When you execute a SQL statement and it is nung on but you don't know why. So you need to kill that session for server's performance.

• Determine which session is locked

SELECT object_name, machine, os_user_name,sid,serial#,'alter system kill session ''' || sid || ',' || serial# || ''';'
FROM v$locked_object a, v$session b, user_objects c
WHERE a.object_id = c.object_id AND b.sid = a.session_id;

• Kill that session

alter system kill session 'sid,serial#' immediate;

Oracle/PLSQL: Oracle System Tables

Below is an alphabetical listing of the Oracle system tables that are commonly used.

System Table	Description
ALL_ARGUMENTS	Arguments in object accessible to the user
ALL_CATALOG	All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS	Comments on columns of accessible tables and views
ALL_CONSTRAINTS	Constraint definitions on accessible tables
ALL_CONS_COLUMNS	Information about accessible columns in constraint definitions
ALL_DB_LINKS	Database links accessible to the user
ALL_ERRORS	Current errors on stored objects that user is allowed to create
ALL_INDEXES	Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS	COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS	Description of LOBs contained in tables accessible to the user
ALL_OBJECTS	Objects accessible to the user
ALL_OBJECT_TABLES	Description of all object tables accessible to the user
ALL_SEQUENCES	Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS	Snapshots the user can access
ALL_SOURCE	Current source on stored objects that user is allowed to create
ALL_SYNONYMS	All synonyms accessible to the user
ALL_TABLES	Description of relational tables accessible to the user
ALL_TAB_COLUMNS	Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS	Columns of user's tables, views and clusters
ALL_TAB_COMMENTS	Comments on tables and views accessible to the user
ALL_TRIGGERS	Triggers accessible to the current user
ALL_TRIGGER_COLS	Column usage in user's triggers or in triggers on user's tables
ALL_TYPES	Description of types accessible to the user
ALL_UPDATABLE_COLUMNS	Description of all updatable columns
ALL_USERS	Information about all users of the database
ALL_VIEWS	Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL	Database compatible parameter set via init.ora
DBA_DB_LINKS	All database links in the database
DBA_ERRORS	Current errors on all stored objects in the database
DBA_OBJECTS	All objects in the database
DBA_ROLES	All Roles which exist in the database
DBA_ROLE_PRIVS	Roles granted to users and roles
DBA_SOURCE	Source of all stored objects in the database
DBA_TABLESPACES	Description of all tablespaces
DBA_TAB_PRIVS	All grants on objects in the database
DBA_TRIGGERS	All triggers in the database
DBA_TS_QUOTAS	Tablespace quotas for all users
DBA_USERS	Information about all users of the database
DBA_VIEWS	Description of all views in the database
DICTIONARY	Description of data dictionary tables and views
DICT_COLUMNS	Description of columns in data dictionary tables and views
GLOBAL_NAME	global database name
NLS_DATABASE_PARAMETERS	Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS	NLS parameters of the instance
NLS_SESSION_PARAMETERS	NLS parameters of the user session
PRODUCT_COMPONENT_VERSION	version and status information for component products
ROLE_TAB_PRIVS	Table privileges granted to roles
SESSION_PRIVS	Privileges which the user currently has set
SESSION_ROLES	Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP	Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES	Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP	Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

Source

Oracle: get column information from a table

Query the tables user_tab_columns or all_tab_columns: 

select column_name, data_type, data_length from user_tab_columns where table_name = 'MY_TABLE';

select * from all_tab_columns where owner = 'THE_SCHEMA_OWNER';

Source

Exporting to excel by using jxls lib with double quote

Needed jar files:

• commons-beanutils-1.8.3
• commons-collections-3.2.1
• commons-digester-2.1
• commons-jexl-2.0.1
• commons-logging-1.1.1
• jxls-core-1.0-RC-2
• jxls-reader-1.0-RC-2
• log4j-1.2.13
• poi-3.7-20101029
• poi-ooxml-3.7-20101029
• postgresql-8.4-701.jdbc4

Code out excel file:

        PostgresDAO dbCon = new PostgresDAO();
        try {
            // connect DB
            dbCon.ConnectDB();
            Map beans = new HashMap();
            ReportManager rm = new ReportManagerImpl(dbCon.con, beans);
            beans.put("rm", rm);
            InputStream is = new BufferedInputStream(new FileInputStream("D:/rptTemplateNew.xls"));
            OutputStream os = new BufferedOutputStream(new FileOutputStream("D:/out.xls"));
            XLSTransformer transformer = new XLSTransformer();
            HSSFWorkbook resultWorkbook = (HSSFWorkbook) transformer.transformXLS(is, beans);
           
            resultWorkbook.write(os);
            os.close();
            is.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            dbCon.Disconnect();
        }

Code in Excel file:

<jx:forEach items="${rm.exec("select distinct objectname from tbdiary where diarytype='X' order by objectname asc")}" var="objectname">                              
${objectname.objectname}                              
<jx:forEach items="${rm.exec("select distinct description from tbdiary where diarytype='X' and objectname='" + objectname.objectname + "' order by description asc")}" var="description">                              
<jx:forEach items="${rm.exec("select to_char(d.documentdate,'dd-mm-yyyy') as documentdate, documentnumber, m.materialid as materialid, materialname as materialname, unit as unit, d.amount as amount, d.price as price, (d.amount*d.price) as totalvalue, d.description as description from tbmaterial m, tbdiary d where objectname='" + objectname.objectname + "' and m.materialid=d.materialid and diarytype='X' and d.description='" + description.description + "' and to_date(to_char(d.documentdate,'DD-MM-YYYY'),'DD-MM-YYYY') >= to_date('01-07-2011','DD-MM-YYYY') and to_date(to_char(d.documentdate,'DD-MM-YYYY'),'DD-MM-YYYY') <= to_date('31-07-2011','DD-MM-YYYY') order by documentdate asc")}" var="supplier">                              
${supplier.documentdate}    ${supplier.documentnumber}    ${supplier.materialid}    ${supplier.materialname}    ${supplier.unit}    ${supplier.amount}    ${supplier.price}    ${supplier.totalvalue}    ${supplier.description}
</jx:forEach>                              
Total1                           $[SUM(H10)]  
</jx:forEach>                              
Total2                            $[SUM(H12)]  
</jx:forEach>                              
Grand total                            $[SUM(H14)]  

Connecting FB

Copy all below to hosts file
125.252.224.88 facebook.com
125.252.224.88 www.facebook.com
69.63.181.12 apps.facebook.com
153.16.15.71 facebook.com
153.16.15.71 www.facebook.com
153.16.15.71 apps.facebook.com
153.16.15.71 login.facebook.com
153.16.15.71 graph.facebook.com
153.16.15.71 static.ak.connect.facebook.com
153.16.15.71 developers.facebook.com
153.16.15.71 error.facebook.com
153.16.15.71 vupload.facebook.com
153.16.15.71 upload.facebook.com
153.16.15.71 secure.facebook.com
153.16.15.71 connect.facebook.com
153.16.15.71 channel.facebook.com
Change DNS
208.67.222.222
208.67.220.220

Useful operations with sp_MSforeachtable Stored Procedure

Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase:
---------------------
--Delete all data in the database
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

EXEC sp_MSForEachTable
'BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
DELETE FROM ?
END CATCH;'
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
---------------------
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable 'DELETE ?'
---------------------
--Drop all Tables
exec sp_MSforeachtable 'DROP TABLE ?'
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
---------------------










Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase with printed message in Messages window of SQL Server Management Studio:
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'"
exec sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL PRINT'? trigger altered'"
---------------------
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable "DELETE ? PRINT'? deleted'"
--Delete all data in the database
EXEC sp_MSForEachTable "DELETE FROM ? PRINT'? deleted'"
EXEC sp_MSForEachTable "TRUNCATE TABLE ? PRINT'? truncated'"
EXEC sp_MSForEachTable
"BEGIN TRY
TRUNCATE TABLE ? PRINT'? truncated'
END TRY
BEGIN CATCH
DELETE FROM ? PRINT'? deleted'
END CATCH;"
---------------------
--Drop all Tables
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped'"
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL PRINT'? constraint altered'"
exec sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL PRINT'? trigger altered'"
---------------------








Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase with printed message in Messages window of SQL Server Management Studio and for a particular schema:
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? DISABLE TRIGGER ALL PRINT'? trigger altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------
--Drop table of particular shcemaID/shemaName
Exec sp_MSforeachtable
@command1 = "DROP TABLE ? PRINT '? dropped'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? CHECK CONSTRAINT ALL PRINT'? constraint altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? ENABLE TRIGGER ALL PRINT'? trigger altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------








Following displays how to execute Drop operation on a DataBase for a particular schema and with Like condition:
---------------------
--Drop table of particular shcemaID/shemaName and with name starting with 'Temp_'
Exec sp_MSforeachtable
@command1 = "DROP TABLE ? PRINT '? dropped'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')
and o.name LIKE 'Temp_%'"
---------------------




Source: http://avinashkt.blogspot.com/2008/05/useful-operations-with-spmsforeachtable.html