More fun with dates

Last week I had a post reminding us to consider the time component of our date data types.

Here I'm just listing some fun with dates, either from single row sql expressions or cool little queries.

select 
  sysdate today
 ,trunc(sysdate) midnight_today
 ,trunc(sysdate)+1/86400 one_second
 ,trunc(sysdate)+2/24 two_hours
 ,trunc(sysdate)+30/60/24 thirty_minutes
 ,trunc(sysdate) + interval '2:30' hour to minute two_hour_thirty
 ,trunc(sysdate,'mm') start_of_month
 ,trunc(sysdate,'yy') start_of_year
 ,add_months(trunc(sysdate,'yy'),6) financial_year
 ,extract(year from sysdate) year_as_number
 ,to_char(sysdate,'yyyy') year_as_varchar2
 ,(sysdate - add_months(sysdate,6)) year to month  interval_months
 ,(Sysdate-(sysdate+2+30/60/24)) day(9) to second interval_daysec
 ,to_date('2012','yyyy') start_of_month
 ,last_day(sysdate) end_of_month
 ,date '2012-02-28' + 1 leap_year
 ,date '2011-02-28' + 1 non_leap_year
 ,date '2012-02-28' + interval '1' day only_during_leap_year
 ,date '1582-10-4' + 1 gregorian_changeover
 ,to_date('01-01-4712bc','dd-mm-yyyybc') scaliger_start
 ,to_date(1,'j') easier_scaliger_start
 ,to_date('31-12-9999','dd-mm-yyyy') end_of_time
 ,to_date('01-01-98','dd-mm-yy') legacy_wrong_century
 ,to_date('01-01-98','dd-mm-rr') legacy_better_format
from dual;

-- Generate first day of each month
select add_months(trunc(sysdate,'yy'),rownum-1) months
from dual connect by level <= 12;

MONTHS
---------------------
01/01/2012 00:00:00
01/02/2012 00:00:00
01/03/2012 00:00:00
01/04/2012 00:00:00
01/05/2012 00:00:00
01/06/2012 00:00:00
01/07/2012 00:00:00
01/08/2012 00:00:00
01/09/2012 00:00:00
01/10/2012 00:00:00
01/11/2012 00:00:00
01/12/2012 00:00:00

12 rows selected

-- Generate list of this year's weekends
select dt from (
  select trunc(sysdate,'yy')+rownum-1 dt
  from dual connect by level <= 365)
where to_char(dt,'dy') in ('sat','sun');

DT                  
---------------------
01/01/2012 00:00:00   
07/01/2012 00:00:00   
08/01/2012 00:00:00   
14/01/2012 00:00:00   
...
23/12/2012 00:00:00   
29/12/2012 00:00:00   
30/12/2012 00:00:00   

105 rows selected 

select sysdate@! wtf_is_this from dual;

Further reading on SQL injection with dates

http://tkyte.blogspot.com.au/2012/02/all-about-security-sql-injection.html

Remember date contains time

This is one thing I regularly mention to trainees, and recently I fell for the problem myself!

Dates may contain a time component.

Simple example, some might expect this statement to return a result (as I write this being 1st March) - it does not.
select * from dual where sysdate <= date '2012-03-01';

For the pure reason that sysdate returns century, year, month, hour, minute, second. And so may some of the dates stored in your database.

SQL> select to_char(sysdate,'dd-Mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')
------------------------------------------------------------
01-Mar-2012 20:49:28

Here is a simple test case where ideally I would get two rows -"now" & "midnight today", and the build-up to this scenario:

create table my_dates(descr varchar2(50), dt date);
insert into my_dates values ('yesterday', sysdate-1);
insert into my_dates values ('now', sysdate);
insert into my_dates values ('midnight today', trunc(sysdate));
insert into my_dates values ('midnight tomorrow', trunc(sysdate)+1);
insert into my_dates values ('tomorrow', sysdate+1);

-- all dates
select * from my_dates;

DESCR                                              DT                  
-------------------------------------------------- ---------------------
yesterday                                          29/02/2012 19:40:12   
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   
midnight tomorrow                                  02/03/2012 00:00:00   
tomorrow                                           02/03/2012 19:40:12   

-- equal to today
select * from my_dates
where dt = date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
midnight today                                     01/03/2012 00:00:00   

-- still equal to today
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
midnight today                                     01/03/2012 00:00:00   

-- better, but includes midnight tomorrow
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01'+1;

-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   
midnight tomorrow                                  02/03/2012 00:00:00   

-- up to one second before midnight
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01'+1-1/86400;
DESCR                                              DT                  
-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   

-- excluding the ability to use an index on dt
select * from my_dates
where trunc(dt) = date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   

Here are other date related posts:

• Date format tolerance
• Interval issues
• Data densification - filling dates

Scott

Oracle costed features

Oracle is expensive. Other times, however, Oracle is very much value for money.

For example, the cost to host an OracleXE instance in the cloud running Oracle Application Express? well that only depends on who you're paying for your cloud service.

For interest sake, you can check out Oracle's price lists here.

You can also query the database view v$option that enables you to see which costed options have been installed on your database. Most of these require separate licences for production databases.

Apparently Mogens has a great presentation talking Oracle licencing, but due to webinar difficulties we missed out at a Perth conference.

I always remember an (unverified) story about some people who used partitioning on their DB to eke wonderful benefits to their environment, then did a presentation on it at a local conference. Ergo, the Oracle Sales rep had a little chat with them to sort out their licences ;-)

It's all well and good to have a play around with these features in development, or your machine at home - but don't forget to pay your licences... Larry needs another yacht.

2019 Update: There is an APEX app for this, of course.

And with small businesses could use the #OracleRAD stack for free.

Wrong number or types of arguments to what?!

Have you ever received the following error?

SQL> select x.abc from dual x;
select x.abc from dual x
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

If you have, the reason is the planets aligned in the Oracle world just to cause confusion.

• you're pre 11g
• you've mis-typed a column
• you're using a simple alias

Essentially, prior to 11g, there are two synonyms defined on the database who's name share some commonly used aliases.

SQL> select synonym_name, table_owner, table_name from all_synonyms where synonym_name in ('X','Y');

SYNONYM_NAME         TABLE_OWNER          TABLE_NAME
-------------------- -------------------- --------------------
X                    MDSYS                OGC_X
Y                    MDSYS                OGC_Y

So if you've mis-typed a column, Oracle tries to work out what your identifier is mapping to and if it finds some random match, it will report a seemingly random error.

These days the public synonyms are more appropriately called OGC_X and OGC_Y, but that doesn't mean you might not have any functions or synonyms defined in your own database that might also be used as table aliases. Heck, when I wrote this post I confused myself again because I had a dummy function in my database called XY.

So there are ways to circumvent things like this happening

1. Use a standard aliases for your tables - this seems pedantic, but it's worthwhile. They're not hard to conjure - first three letters (organisations org), first letter of each word (resource_types rt) - but keep it consistent. This will make life easier for developers that need to read SQL, and even Oracle likes you to use aliases for performance reasons.
   At one site, our code didn't pass muster if our table aliases didn't match the prescribed list.
2. Make your functions/synonyms descriptive - nobody wants to find a function called X or ABC and have to chase up what it does. Use some standard naming conventions.
3. Don't make mistakes in your SQL - but if when you do, learn to recognise the reported errors to help nut out the typo you've made - don't just ignore the error to sit & stare. While the error message doesn't always report the exact line/position of the actual problem, the hints it provides are typically consistent in some form - computers are dumb.
4. Upgrade your database - unrelated, just typically a good move ;-)

Scott

Finding stuff in ALL_VIEWS using XML

Quite some time ago when I first started blogging I wrote a post regarding limitations on searching the ALL_VIEWS dictionary view.

Simply put, if you would like to run a query such as this to find those views that contain the text "booking_no", you're not going to get very far.

SQL> select count(*)
2  from user_views
3  where text like '%booking_no%';
where text like '%booking_no%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I can't remember where I found this, but there is a way to do perform this search without pumping your data into a temporary table.

select count(*)
from user_views 
where upper(dbms_xmlgen.getxml('select text from user_views where view_name = '''||view_name||'''')) 
  like upper('%booking_no%')

It's essentially looking for "booking_no" within the result of a query that has been converted into canonical XML format.

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TEXT>SELECT r.code,b.booking_no,e.start_date,e.end_date,o.org_id,o.name
FROM   resources r,bookings b,events e,organisations o
WHERE  r.code = b.resource_code
AND    b.event_no = e.event_no
AND    o.org_id = e.org_id</TEXT>
 </ROW>
</ROWSET>

A word of warning, it is CPU (and probably memory) intensive, so you might like to limit to a particular set of schemas instead of just searching ALL_VIEWS.

Turns out XML has some nifty uses after all ;-)

Beyond NVL

If there's one thing I've ever learned while developing in Oracle, there is always more than one way to solve a problem.

The question is - have you done it in the simplest, most intuitive manner?

That's the bar to set, because it may be you the revisits some code 2, 6, 12 months later...

Recently, I came across some code like this:

CASE
    WHEN NVL(custom_value, normal_value) = normal_value THEN
      NULL
    ELSE
      normal_value
    END AS alt_value

I had a fair idea what it was trying to resolve, but I wasn't sure and I still had to run some scenarios to be sure.
Basically, this was the 2nd of two fields that showed a normal value and a custom value. The first field was simply
NVL(custom_value, normal_value)

The second field should be:
If there is a custom value, show the normal value, otherwise show nothing.

This case statement does this, but more like:
If there isn't a custom value, compare the normal value with itself - if it is the same, show nothing, otherwise show the normal value.

Que?

I don't know if it's lack of familiarity with the NULL-related functions, or some people don't immediately consider them.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions002.htm#CJAFHIFF

While mentally trying to convert it to something simpler, I simplified to
NULLIF(normal_value, NVL(custom_value, normal_value))

Which translates to:
If normal value is the same as itself if the custom value is not present, then use the normal value, otherwise nothing.

Then I recognised it could be simplified further, just like resolving a fraction ;-)
NVL2(custom_value, normal_value, NULL)

If custom value is not null, use normal value, otherwise show nothing.

Which reads just like my requirement - using one function, referencing each field once only.

Super.

Scott.

Added 11 April 2012 - in response to comments

with data as 
 (select 'Mouse' widget, 10 normal_value, null custom_value from dual union all
  select 'Laptop', 1000, 800 from dual union all
  select 'Mixer', 30, 30 from dual union all
  select 'Lost sock', null, 1 from dual)
select 
   widget
  ,normal_value
  ,custom_value
  ,nvl(custom_value, normal_value) first_field
  ,case when nvl(custom_value, normal_value) = normal_value then
    null
   else
    normal_value
  end as alt_value_orig
 ,nvl2(custom_value, normal_value, null) alt_value_scott
 ,case when custom_value <> normal_value then normal_value end alt_value_anon
 ,coalesce(custom_value, normal_value) alt_value_hayland
from data; 

Cheers

Updating key-preserved inline views

I learnt something today - there is a scenario where you need update privileges on a table you aren't updating.

Here is my example - I have a table TRAIN_APEX.RESOURCES that I would like to update based on TRAIN.RESOURCE_RATES. So the key factor here is my source table is in a different schema to my destination table - and I only have select privileges on resource_rates.

TRAIN_APEX> SELECT privilege, grantee, table_name FROM all_tab_privs where table_name = 'RESOURCE_RATES';

PRIVILEGE            GRANTEE              TABLE_NAME
-------------------- -------------------- --------------------------------------------------------
SELECT               TRAIN_APEX           RESOURCE_RATES

1 row selected.

This means when I attempt to run an update that uses an elegant key-preserved in-line view, I can't do it!

TRAIN_APEX> UPDATE
  2    (SELECT r.type_code
  3           ,a.standard_rate
  4           ,r.code
  5           ,r.daily_rate
  6     FROM train_apex.resources r
  7         ,train.resource_rates a
  8     WHERE a.type_code = r.type_code)
  9  SET daily_rate = standard_rate;
       ,train.resource_rates a
              *
ERROR at line 7:
ORA-01031: insufficient privileges

I've had a look through the documentation for views and update statements, and I can't see it as a pre-requisite.

Out of curiosity I defined the statement as an actual view to confirm the base table is key-preserved. I knew this already because I've successfully run this statement when the tables are in the same schema, or in scenarios where my account has UPDATE ANY TABLE.

TRAIN_APEX> CREATE VIEW resources_vw AS
  2  SELECT r.type_code
  3        ,a.standard_rate
  4        ,r.code
  5        ,r.daily_rate
  6  FROM train_apex.resources r
  7      ,train.resource_rates a
  8  WHERE a.type_code = r.type_code;

View created.

Elapsed: 00:00:00.05
SQL>
SQL> @view_dml resources_vw

COLUMN_NAME          INSERTABL UPDATABLE DELETABLE
-------------------- --------- --------- ---------
TYPE_CODE            YES       YES       YES
STANDARD_RATE        NO        NO        NO
CODE                 YES       YES       YES
DAILY_RATE           YES       YES       YES

4 rows selected.

So instead, I have to run a "normal" update with a condition to check for presence of rows in my source table, which also means my update isn't as efficient.

UPDATE train_apex.resources r
SET daily_rate =
  (SELECT standard_rate
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)
WHERE EXISTS
  (SELECT NULL
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)

Do you think this is a bug or expected behaviour?

Scott