SQL

Get Current Time in a Different Time Zone

It is relatively easy to select current_timestamp from dual and get current date time in your time zone. But things get a little more complicated when you want to get date and time in a different time zone. Here is how you can accomplish that:

-- Get current date and time as well as current date and time in a different time zone (using eastern time zone).
SELECT CURRENT_TIMESTAMP currt_dt,
 CURRENT_TIMESTAMP AT TIME ZONE TZ_OFFSET ('EST') different_dt
  FROM DUAL;

CURRT_DT                            DIFFERENT_DT                      

Oracle SUBSTR Function

The SUBSTR function is used to extract characters from a word or sentence.

For Example, I have run into many situations were I need to apply different programming logic based on characters in a string.

Suppose I have an employee table with an employee_id and employee_name (First, Last, middle) and I want to store the middle name in my database as middle initial only. So I only need the first character of the middle name.

I can simply do this by using a SUBSTR on the middle name field.

SELECT SUBSTR(middle_name,0,1) middle_initial FROM <employee>

Oracle Minus Operator

I find the MINUS operator very handy and use it a lot to compare tables and find out missing things.

For example suppose you want to compare Table1 with Table2.

If the two tables have the same layouts (same column names and data content), you can simply do this

SELECT * FROM <Table 1>
MINUS
SELECT * FROM <Table 2>

This query will compare each record in Table 1 to a record in table 2.
The result returned will be records in table 1 that are not in table 2.
you can reverse the MINUS order to get records in table 2 that are not in table 1

Grant and Revoke privileges

Typically in a database you have many users.

The owner shcema that owns the objects, usually decides the privileges given to other users on the objects it owns.

Lets call the schema owner "OWNER" and suppose we have another user in the database called "READER".

For the user "READER" to be able to view the data in a table owned by "OWNER", the owner has to grant the reader select priviliges.

GRANT SELECT ON <table name> TO READER;

List Year Month for Current and Previous Month

The query will come handy if you would like to pull transactions that have taken place in the current month as well as the previous one.

SELECT TO_CHAR (SYSDATE, 'YYYY'), TO_CHAR (SYSDATE, 'MM')
  FROM DUAL
UNION ALL
SELECT TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYY'),
       TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'MM')
  FROM DUAL;

TO_C TO
---- --
2008 07
2008 06

2 rows selected.

What if the current system date is '01/15/2008', would the above SQL pull last year 2007 and month 12? Well. let do a quick test.

 

Get Total Number of Lines in Functions and Procedures

To get the total number of lines inside a function, trigger, procedure, ect. We need to run our query against the
ALL_SOURCE table (describes the text source of the stored objects accessible to the current user). Or against the
USER_SOURCE table (describes the text source of the stored objects owned by the current user. Its columns (except for OWNER) are the same as those in ALL_SOURCE.

Find Number of Days in a Year

Number of days in a year can be calculated by finding the 1st day of the next year and subtract that from the 1st day of the current year.

Find first day of next year

 SELECT add_months(TRUNC(sysdate,'y'),12)
  FROM dual;

ADD_MONTHS(TRUNC(SYSDATE,'Y'),12)
-------------------------
01-JAN-09                

1 rows selected

Find first day of current year

SELECT TRUNC(sysdate,'y')
 FROM dual;

TRUNC(SYSDATE,'Y')        
-------------------------

Get Table Columns

Oracle's all_tab_columns table stores meta data information related to table columns. The SQL below will return all columns as well as column order Id for a specific table and owner. In our example, we will be looking at table EMLOYEES and owner HR.

 SELECT column_name,
  data_type        ,
  column_id
   FROM all_tab_columns
  WHERE owner  = 'HR'
AND table_name = 'EMPLOYEES';

Results:

COLUMN_NAME      DATA_TYPE   COLUMN_ID
---------------- ----------  ---------
EMPLOYEE_ID      NUMBER      1        

Change Current Schema

A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users to contain, or reference, their data. Schema objects include structures like tables, views, and indexes. To access tables not on your schema, you need to issue the "Schema Alter" command.

Lets go through an example and try to access a table in the HR schema from a different schema and see what happens.
table not found different schema

List Available Tables in a Schema

Very straight forward query to list all available tables under a specific schema. Our schema name in this example is going to be 'HR'.

SELECT table_name
FROM all_tables
WHERE owner = 'HR';

Results:

TABLE_NAME                    
------------------------------
REGIONS                        
LOCATIONS                      
DEPARTMENTS                    
JOBS                          
EMPLOYEES                      
JOB_HISTORY                    
COUNTRIES                      

7 rows selected