Oracle Magazine - January/February 2012

3. Starting with the last day of January, move ahead one month. Starting from a different date, go back one month. Starting with the last day of February, go back one month. Listing 3 shows three different calls to the ADD_MONTHS function along with the results.

You might be surprised at the third date in Listing 3. The first date ( 28 February) makes perfect sense. There is no 31st day in February, so Oracle Database returns the last day of the month. The second call to ADD_MONTHS moves the date from 27 February to 27 January: exactly one month’s change. But in the third call to ADD_MONTHS, Oracle Database notices that 28 February is the last day of the month, so it returns the last day of the month specified by the second argument.

4. Find the next Saturday after today’s date:

l_date := NEXT_DAY (SYSDATE, 'SAT');
-- or
l_date := NEXT_DAY (SYSDATE,
'SATURDAY');

The second argument must be a day of the week in the date language of your session (specified by NLS_DATE_LANGUAGE), provided as either the full name or the abbreviation. The returned date has the same time component as the date.

BAD THINGS HAPPEN—EVEN IN
GOOD PROGRAMS
Now that you have a solid foundation in
working with key datatypes such as strings,
numbers, and dates, I will switch focus in
the next article of this series to an in-depth
introduction to exceptions: how they can be
raised and how you can handle them. T

Steven Feuerstein
(steven.feuerstein@
quest.com) is Quest
Software’s PL/SQL
evangelist. He has
published 10 books on
Oracle PL/SQL (O’Reilly Media) and is an Oracle
ACE Director. More information is available at
stevenfeuerstein.com.

LEARN more about PL/SQL datatypes

bit.ly/nrpGAw

functions

bit.ly/qbrR7I

TES T your PL/SQL knowledge

plsqlchallenge.com

READ PL/SQL 101, Parts 1–4

bit.ly/fc0uoJ

We can’t begin to tell you what winning Oracle’s highest honors means.

(Thankfully, our clients can.)

“[The
“...expertise, thoroughness
and dedication to getting
things right.” “…an excellent
blend of systems
design, support
and in-depth
knowledge...” “…flexi ry
knowl

“...a key partner in helping us architect and deliver a robust solution to our clients.”

“...a source base servi

“Cintra exceeded my expectations…”
“[Cintra]
helps me to
sleep better
at night.”

“Cintra makes the whole process seamless...”

Oracle’s 2011 Specialized
Global Partner of the Year
for Database
2011 Oracle Titan Award for
Database and Clustering
Experts in the rapid
deployment of Oracle’s
Engineered Systems, including
Oracle Database Appliance
and Exadata
To learn more about our
commitment to specialization
and what it means to your
business, visit cintra.com
“Cintra’s service has
been impeccable…”