orapunk.com

How to add days, hours or minutes to a date

orapunk — Wed, 25 Aug 2010 13:27:35 +0000

Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
1 minute from now	SYSDATE + 1/1440
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 10 AM	TRUNC(SYSDATE + 1) + 10/24

download OBIEE 11g NOW!!!

orapunk — Mon, 16 Aug 2010 17:31:41 +0000

Hello hello hello,

See here what we were waiting for, the new OBIEE 11g.

Reset SYSTEM password

orapunk — Sun, 01 Aug 2010 15:45:14 +0000

For the ones who wants to reset the SYSTEM pwd:

operfcfg.exe -U SYSTEM -P password -D tns_entry_name

Time for some humor

orapunk — Fri, 30 Jul 2010 22:30:59 +0000

Q. What if your Dad loses his car keys?
A. ‘Parent keys not found!’

Q. What if your old girl friend spots you with your new one?
A. ‘Duplicate value on index!’

Q. What if the golf ball doesn’t get into the hole at all?
A. ‘Value larger than specified precision!’

Q. What if you try to have fun with somebody else’s girlfriend and get kicked out?
A. ‘Insufficient privileges on the specified object!’

Q. What if you don’t get any response from the girl next door?
A. ‘No data found!’ or ‘ Query caused no rows retrieved!’

Q. What if you get response from the girl next door and her Mom too?
A. ‘SELECT INTO returns too many rows!’

Q. What if you dial a wrong number?
A. ‘Invalid number’ or ‘ Object doesn’t exist!’

Q. What if you try to beat your own trumpet?
A. ‘Object is found mutating!’

Q. What if you are too late to office and the boss catches you?
A. ‘Discrete transaction failed!’

Q. What if you see ‘theatre full’ when you go to a movie?
A. ‘Maximum number of users exceeded!’

Q. What if you don’t get table in the lunch room?
A. ‘System out of tablespace!’

OBIEE 11G new version released

orapunk — Fri, 30 Jul 2010 22:25:49 +0000

Yes finally after 3 years of product tuning, there is finally the new and adult version of OBIEE. Called of course 11G (something else would have been a big surprise).

You can check some more information on the following Oracel website:

http://www.oracle.com/oms/businessintelligence11g/business-insight-075567.html

max(ROWID) => something to remember

orapunk — Thu, 22 Apr 2010 17:19:40 +0000

Sometimes we forget some basic concepts. So do I (sometimes).

To retrieve the latest record of a table it’s not ALWAYS correct to use the max(rowid) of a table.

The rowid is an ID generated based on file/block/slot .

The only method to make sure we take the last record is a having a column with a timestamp or a sequence.

SQL*LOADER control file generator

orapunk — Fri, 26 Mar 2010 09:30:58 +0000

set echo off ver off feed off pages 0
accept tname prompt ‘Enter Name of Table: ‘
accept dformat prompt ‘Enter Format to Use for Date Columns: ‘

spool &tname..ctl

select ‘LOAD DATA’|| chr (10) ||
‘INFILE ”’ || lower (table_name) || ‘.dat”’ || chr (10) ||
‘INTO TABLE ‘|| table_name || chr (10)||
‘FIELDS TERMINATED BY ”,”’||chr (10)||
‘TRAILING NULLCOLS’ || chr (10) || ‘(’
from user_tables
where table_name = upper (’&tname’);

select decode (rownum, 1, ‘   ‘, ‘ , ‘) ||
rpad (column_name, 33, ‘ ‘)      ||
decode (data_type,
‘VARCHAR2′, ‘CHAR NULLIF (’||column_name||’=BLANKS)’,
‘FLOAT’,    ‘DECIMAL EXTERNAL NULLIF(’||column_name||’=BLANKS)’,
‘NUMBER’,   decode (data_precision, 0,
‘INTEGER EXTERNAL NULLIF (’||column_name||
‘=BLANKS)’, decode (data_scale, 0,
‘INTEGER EXTERNAL NULLIF (’||
column_name||’=BLANKS)’,
‘DECIMAL EXTERNAL NULLIF (’||
column_name||’=BLANKS)’)),
‘DATE’,     ‘DATE “&dformat” NULLIF (’||column_name||’=BLANKS)’, null)
from   user_tab_columns
where table_name = upper (’&tname’)
order by column_id;

select ‘)’ from dual;

spool off

new APEX group at LinkedIn

orapunk — Tue, 23 Mar 2010 13:20:17 +0000

http://www.linkedin.com/groups?home=&gid=2784198&trk=anet_ug_hm