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

ORACLE to_number error 9I

Ran into a strange problem today. Somehow the exectution plan on a 9I box was different then before and it started to trow ORA-01722: invalid number errors.... This was caused by the fact that a to_number conversion was done after a the where instead of before. The workaround is to force the database to do the conversion first. This can be done by using a DECODE statement:

DECODE ((REPLACE (TRANSLATE (TRIM (number_as_string), '0123456789', '0000000000'
), '0', NULL ) ), NULL, TO_NUMBER (TRIM (number_as_string)) ) string_as_number.

Till Next Time

ORACLE Collect Function

Up to 9i you had to use Tom Kyte's Stragg function to get the children of the level to a single string. From 10GR1 you can use the collection function. Based on this article: http://www.oracle-developer.net/display.php?id=306 I played around with this function.

First make our own table type:

CREATE OR REPLACE TYPE collect_table AS TABLE OF VARCHAR2 (4000);

SELECT deptno, CAST (COLLECT (distinct ename) AS collect_table) AS emps
FROM emp
GROUP BY deptno;

Now make a collection to string conversion function:

CREATE OR REPLACE FUNCTION collect_to_string (
nt_in IN collect_table,
delimiter_in IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2
IS
v_idx PLS_INTEGER;
v_str VARCHAR2 (32767);
v_dlm VARCHAR2 (10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL
LOOP
v_str := v_str v_dlm nt_in (v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT (v_idx);
END LOOP;
RETURN v_str;
END collect_to_string;

SELECT deptno,
collect_to_string (CAST (COLLECT (distinct ename order by ename desc)
AS collect_table) ,',')AS emps
FROM emp
GROUP BY deptno;

Till Next Time