Calculate date from week of year, or week to date

Useful solution for finding the date of first day of a given week, and year.

select decode(to_Char(to_date('01-JAN-'||&Cur_year),'DY'),'MON', to_date('01-JAN-'||&Cur_year),next_day(to_date('01-JAN-'||&Cur_year),'MON'))+(7*(&cur_week-1)) from dual


Value of Cur_year may be any year and Cur_week must be between 1-52.

Thank you Sandy for this solution.  

10g FAQ

10g Dataguard, Flashback and New Features Q&A

Oracle 10g SQL Statement Tuning Course

A good, informative and broadening course authored by Hasan Tonguç Yılmaz. It lasted for 4 days hands on course. As a brief it consists of following topics, and exercises:

• Oracle SQL hints


• Oracle performance related views, and PLSQL packages


• BULK collect


• SQL bind variables


• Heap, Index Organized, Partitioned, Clustered tables


• Oracle sources regarding SQL statements (SQL Reference: http://tahiti.oracle.com, Package examples: http://psoug.org/library.html, For problems , articles, and solutions: http://metalink.oracle.com/, http://otn.oracle.com, http://asktom.oracle.com/tkyte/)


• Indexes, FTS


• Join methods, join orders

I will add above list later!

Hidden Parameters

To list hidden parameters and their meanings, you can use following sql:

SELECT   x.ksppinm NAME,
DECODE (BITAND (ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') sesmod,
DECODE (BITAND (ksppiflg / 65536, 3),
1, 'IMMEDIATE',
2, 'DEFERRED',
3, 'IMMEDIATE',
'FALSE'
) sysmod,
ksppdesc description
FROM SYS.x_$ksppi x
WHERE x.inst_id = USERENV ('INSTANCE')
AND TRANSLATE (ksppinm, '_', '#') LIKE '#%'
ORDER BY 1;

To list current values of hidden parameters:

SELECT   a.ksppinm "Parameter", b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND SUBSTR (ksppinm, 1, 1) = '_'
ORDER BY a.ksppinm;

Single Quote '

To insert or select single quote you can use chr(39). For example;

SQL> select chr(39) from dual;
'

SQL> CREATE TABLE MYTABLE (F1 VARCHAR2(100));

Table created.

SQL> insert into MYTABLE values('Orange Cote D'||chr(39)||' Ivoire S.A.');

1 row created.

SQL> select * from MYTABLE;
Orange Cote D' Ivoire S.A.

SQL>

hello world from wordpress!

Streams makes lake, many smalls make big! Then share, share, share... Many people even experts still continue to learn from others, learners live! I started this blog because I want to log what I learnt, and share with the Oracle community.

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

checkpoint

excerpt from http://www.adp-gmbh.ch/ora/concepts/checkpoint.html

Oracle Checkpoint
		A checkpoint performs the following three operations: Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk. It's the DBWR that writes all modified databaseblocks back to the datafiles. The latest SCN is written (updated) into the datafile header. The latest SCN is also written to the controlfiles. The update of the datafile headers and the control files is done by the LGWR(CKPT if CKPT is enabled). As of version 8.0, CKPT is enabled by default. Events that trigger a checkpoint The following events trigger a checkpoint. Redo log switch LOG_CHECKPOINT_TIMEOUT has expired LOG_CHECKPOINT_INTERVAL has been reached DBA requires so (alter system checkpoint) Additionally, if a tablespace is hot backuped, a checkpoint for the tablespace in question is taking place. While redo log switches cause a checkpoint, checkpoints don't cause a log switch. Time and SCN of last checkpoint The date and time of the last checkpoint can be retrieved through checkpoint_time in v$datafile_header The SCN of the last checkpoint can be found in v$database.checkpoint_change#. Size of redo log If the size of the redo log is to small, the performance of the checkpoint will not be optimal. This is the case if the alert.log contains messages like Thread .. cannot allocate new log....

---