ORACLE Drop Table If Exists

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE mytable';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;


Till Next Time

ORACLE Available Tables

SELECT
  OWNER,
  TABLE_NAME
FROM ALL_TABLES
ORDER BY TABLE_NAME;


SELECT
  OWNER,
  TABLE_NAME
FROM ALL_TABLES@DB_LINK
ORDER BY TABLE_NAME;


Till Next Time

ORACLE All Avialable Database Links

SELECT

  OWNER,
  DB_LINK
FROM
  ALL_DB_LINKS;

Till Next Time

ORACLE Same weekday last year

How to get the same weekday last year:

select
case when
to_number(to_char(to_date('28-FEB-2009'),'d') )
-
to_number(to_char(add_months(to_date('28-FEB-2009'),-12),'d') )= 0
then
add_months(to_date('28-FEB-2009'),-12)
else
next_day(add_months(to_date('28-FEB-2009'),-12), to_number(to_char(to_date('28-FEB-2009'),'d') ))
end as DAY_LAST_YEAR_SAME_WEEK_DAY
from
dual;

Till Next Time

ORACLE Forcing the listener to listen

show parameter local listener
-- if the above is empty
alter system set local_listener='(address=(protocol=tcp)(host=localhost)(port=1521))';
alter system register;

Till Next Time

ORACLE First / last of Month / quarter

select
trunc(sysdate,'MM') as FIRST_OF_MONTH
from dual;

select
trunc(LAST_DAY(sysdate)) as LAST_OF_MONTH
from dual;

select
trunc(sysdate,'Q') as FIRST_OF_QUARTER
from dual;

select
add_months( trunc(sysdate,'Q'),3) - 1 as LAST_OF_QUARTER
from dual;

Till Next Time

ORACLE right function

A good alternative:

substr(COLUMN_NAME, - integer)

Till Next Time