TO_DATE or not TO_DATE

Like a middle aged man visiting the gym for the first time in 10 years, I am merely going to flex my blogging muscles here…

One of the recurring issues that annoys me on the OTN SQL & PL/SQL forum, and also the APEX forum is the misunderstanding of what the TO_DATE function actually does. I think many people that are new to database development and Oracle see TO_DATE and it’s format mask and think – great I can reformat a date!

Well, no. That’s not what it does and is not what its for. Let’s get back to basics.

A date is a date. It’s not ’14-03-2013′, ’03-14-2013′, ’14 Mar 2013′ or any other representation you can think of. It’s a date, and in Oracle’s world, a time too. The moment that you are reading this is a date.

A string (or char or literal or whatever you want to call it – this post will use string from now on) is a series of characters. ‘Ben’ is a string, in the same way that ’14 March 2013′ is a string, they mean very little to the database unless they’re relating to something else.

So, to the purpose of TO_DATE()!

TO_DATE() is a system function that allows you to describe to the database a date that you are passing in as a string. The conversation would go something like this…

Developer: Yo DB! Got an input here, it’s ’14-MAR-2013′ – knock yourself out.

DB: Cool! I like strings.

Developer: But it’s a date?

DB: Oh. You didn’t mention that. What date is it?

Developer: I have to explain everything? You just can’t get the staff these days! It’s in DD-MON-YYYY format obviously…

DB: You should have just said, no need to be abusive.

In code, that would be TO_DATE(’14-MAR-2013′,’DD-MON-YYYY’). The database now knows it’s a date and can treat it as such. The Oracle doc’s and a million blog posts will tell you the many forms in which you can ‘describe’ the date to the database. So, that is TO_DATE(), and that’s what it does.

So why do people get confused? Well, mostly because they see the term ‘format mask’ and they’ve heard of TO_CHAR(). Then think the two functions can be used interchangeably because they both have ‘format masks’.

TO_CHAR() is a system function that allows you to describe to the database how to return something as a string. The conversation would go something like this…

Developer: Yo DB! Got a date here, just pop it back to me as a string so the user can see it please.

DB: Cool! Here you go – ’04-03-2013′

Developer: The user’s American?

DB: Oh, you didn’t mention that. How do they want it?

Developer: I have to explain everything? You just can’t get the staff these days! It’s in MM-DD-YYYY format obviously…

DB: You should have just said, no need to be abusive.

In code, that would be TO_CHAR(the_date,’MM-DD-YYYY’).

By now you probably wondering why the Developer keeps saying ‘Yo’ and the DB keeps replying with ‘Cool!’. That there is implicit conversion. The DB *knows* that the developer is a 1980’s pop rap singer, so it knows how to respond. This is all in your NLS parameters, there you can set the default date format for the database. This is why many people come across the situation of “this always works but suddenly the results are wrong!” when dealing with a predicate like…

my_date_column = :p_myDate

Now, let’s say the NLS parameters are set to ‘DD-MM-YYYY’. All the users input dates in that format. Then they employ an American and they input a date as ’03-04-2013′. The DB thinks that this is 3rd April 2013, but the user meant 4th March 2013.

You should never rely on implicit conversion (the same applies to numbers here), you should ALWAYS describe to the database explicitly what you are giving it.

Note to APEX developer’s here – every item that you have is returned as a string, so all of your reports etc. that refer to items as bind variables should be explicitly converted if they are supposed to be numbers or dates – saying “but it works at the moment” is not good enough!. APEX does do some clever things around this but I would always convert explicitly in my code.

Why does this all annoy me so much? Well, I see people doing nested TO_CHAR(TO_DATE(TO_CHAR())) all the time in the forums and wondering why their code isn’t working. Just remember – TO_DATE() describes to the database that you are giving it a date, TO_CHAR() can allow you to return a date as a string in a specified format. Stop being abusive to your database!

Okay, so that’s the blogging hiatus finished – I’ll be back soon!

OUG Ireland 2012

Hola!

Long time no speaky!

Well, on 21st March 2012, I spoke at the OUG Ireland conference. The title of the presentation was “to iterate is human, to recurse divine” – a touch pretentious, I know. The event started for me the night before, meeting Roel Hartman, Jan Navratil and Frits Hoogland for some beers and food (followed by a few more beers and some pool).

It was even more interesting with it being my future wife’s birthday on the day of the conference. The presentation did not go too badly but was massively under time. This was partly by design but not to *that* extent – lesson learned for next time I present this. Afterwards I believe I did my lady proud and got her so tipsy that she forgot me getting the restaurant to sing her Happy Birthday by the next morning!

This year promises to be rather busy on the old conference front for me and OUG Ireland was a great way to get started! I even got chance to speak to Debra Lilley to float the idea of an Isle of Man based event, who promptly walked me over to Karen McCormack so that she could look into the viability of the idea (once I’ve given here some details). These kind of networking opportunities just don’t happen unless you go to conferences and Special Interest Group meetings, so I encourage all of you to support your local ones as much as you can this year. 

I’m going to try and blog regularly from now on, but I’ve said that before and failed.

No promises, but I WILL TRY!

HAI CAN HAS TABULAR FORM CHECKBOX KTHXBYE

Ok, here’s my first blog post in a long while.

It’s well overdue, I know. I spurred myself into doing it by proclaiming my intent over twitter, dangerous – no choice now!

So I’ll start myself off with a short and simple that I came across the other day, hopefully it will help someone.

The PROBLEM… Created a new tabular form in the new theme/framework that I’d been working on. All looked well, my CSS and jQuery theming was looking gorgeous. I was patting myself on the back and considering buying a ‘special’ coffee to celebrate. Then something happened… Or rather, something didn’t. I clicked on the checkbox at the top of the row selector column and I got the first row checkbox selected and A JAVASCRIPT ERROR! What the hell had I done to break such a fundamental function in APEX?

Here’s the exact error from Firebug;

gRowActive[a] is undefined

So, I Google and I hit this link… https://forums.oracle.com/forums/thread.jspa?threadID=2308963 Fantabulous! It’s happened before – a fellow APEX developer has met the same plight and, by golly, they’ve bloody fixed it! What they forgot to do was furnish the rest of us unfortunate souls with their ‘fix’.

The SOLUTION…

Now here we go. It’s not a fix, it’s a hack. because the underlying javascript code from APEX is wrong (sorry – ‘sub optimal’)  – 😉

PUT SOMETHING IN THE… ‘Background color for checked row’

AND IN THE… ‘Background color for current row’

 

Hmm… why should that break it?

APEX SIG London November 2011

Okay, nearly 1 year since my last post…

I’m presenting at the APEX SIG on 3rd November…

I would love for every attendee to bring one thing that will fit into a hat!

It could be a piece of code or comment on a piece of paper, a memory stick with something on, a photograph, a piece of candy, a used bus ticket – I don’t care – use your imagination. Every single one will be discussed within the context of the presentation.

Please retweet/facebook/e-mail/phone/whatever!

Ben/Munky

Handling aggregated strings in SQL reports

Well, it’s been a long time without a new post from me so I thought I’d get the ball rolling again!

Having been surfing about and happened upon Adrian Billington’s site again, I spotted something pretty nifty. With a bit of rewriting, I started to see a possible benefit to the APEX community – mostly for the handling of multiselect list values in reports.

A common approach to dealing with them is to concatenate colons to the multiselect list value returned in the bind variable and then concatenating colons to the column being searching and using INSTR. Whilst this is okay for small sets of data, it can become problematic for large ones because it will negate the index on the searched column.

Consider this example of the that approach…

SELECT *
FROM  emp
WHERE INSTR(':'||:LIST_ITEM||':',':'||empno||':')>0

Here, If you have an index on empno, this won’t be used and a full table scan will occur.

Now consider this (and this is seriously quick)…

WITH t_str AS
(
 SELECT  :LIST_ITEM||':'                                              AS str,
 (LENGTH(:LIST_ITEM) - LENGTH(REPLACE(:LIST_ITEM,':'))) + 1 AS no_of_elements
 FROM    dual
),   t_n_rows AS
(
 SELECT  LEVEL AS i
 FROM    dual
 CONNECT BY LEVEL <= (SELECT SUM(no_of_elements) FROM t_str)
),   t_in_list AS
(
 SELECT SUBSTR(str,start_pos,(next_pos - start_pos)) AS element_value
 FROM   (
 SELECT t_str.str,
 nt.i AS element_no,
 INSTR(t_str.str,':',DECODE(nt.i,1,0,1),
 DECODE(nt.i,1,1,nt.i - 1)) + 1 AS start_pos,
 INSTR(t_str.str,':',1,DECODE(nt.i,1,1,nt.i))       AS next_pos
 FROM   t_str JOIN t_n_rows nt
 ON nt.i <= t_str.no_of_elements
 )
)
SELECT *
FROM   emp
WHERE  empno IN (SELECT TO_NUMBER(element_value) FROM t_in_list);

Voila! The index on empno will be used! I know there’s more lines of code here but the performance benefits of it are huge (most of it’s done just using fast dual in the factored subquery). All you need to do is save it in your toolbox and copy & paste when the need arises!

Hope you enjoyed this, more posts coming soon.

 

PS: Sorry – forgot to link to the actual article by Adrian – hit the word ‘nifty’ now!

Creating ExtJS Tree JSON Objects with Custom Authorisation in SQL

Okay, the title to this post may seem a tad specific but the principles are transferable to creating pretty much any Ext Tree JSON object under any circumstances as long as you have the data in the database.

Previously I have be using Mark Lancaster’s example for creating the JSON object for an Ext Tree using the APEX List template. This has been working flawlessly for a good while and I love the ability to rejig the tree hierarchy using the list functionality in APEX.

However, I recently had requirement to implement a custom authorisation for the tree – hiding all entries that the user was not allowed to see. This would have been easy normally – I was using the APEX list functionality so I could just apply conditional display. Unfortunately, I couldn’t use this approach because my target was an URL with a target like:

javascript:myPageFunc(1);

This meant that APEX didn’t know which page the entry was targeting and I therefore couldn’t pass it through to the authorisation function to see if the option was available.

So, I set about creating my own JSON object generating SQL query, I had a google about and many people have done this in PL/SQL but it is normally limited a certain number of levels in the tree and these levels are hard-coded.

Creating the initial object with no authorisation was reasonably straight forward, I had Mark’s example for the  syntax and I created a small list in APEX using that template so that I had a reference point to work to.

To add the authorisation I added the page that I wanted the list entry to be checked against to the ‘User defined attributes’ in APEX. In the apex_application_list_entries table, this is stored as entry_attribute_01.

My problems started where there where multiple levels in the tree and the user was authorised to an entry at the lowest level but not to some of the levels above that in the same path. This was overcome by getting the whole path for each entry, checking if the user was authorised to the lowest level and then walking back up the path to make sure they then were shown all the folders in order to get to that leaf.

My colleague, Kevan Gelling, suggested that a pipelined function would help with this so we created this simple one:

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

CREATE OR REPLACE FUNCTION stringToTable
  (
    p_String                       VARCHAR2,
    p_Delim                        VARCHAR2 DEFAULT ':',
    p_Start                        VARCHAR2 DEFAULT 'N',
    p_End                          VARCHAR2 DEFAULT 'N'
  )
  RETURN  t_string_to_table PIPELINED
  IS
    l_Char                         VARCHAR2(1);
    l_Element                      VARCHAR2(4000);
    l_loopStart                    NUMBER;

  BEGIN

    l_Element := NULL;

    IF p_Start = 'Y' THEN

      l_loopStart := 2;

    ELSE

      l_loopStart := 1;

    END IF;

FOR i IN l_loopStart .. LENGTH(p_String)
    LOOP

l_Char := SUBSTR(p_String,i,1);

      IF l_Char = p_Delim THEN

        PIPE ROW (l_Element);

        l_Element := NULL;

ELSIF p_End = 'N' AND i = LENGTH(p_String) THEN

        l_Element := l_Element||l_Char;
        PIPE ROW (l_Element);

      ELSE

        l_Element := l_Element||l_Char;

      END IF;

    END LOOP;

    RETURN;

  END;
/

Now the SQL for the JSON object is as follows (thanks to Kevan for rationalising the last part a bit):

WITH
menuList AS
    (
       SELECT list_entry_id,
              list_entry_parent_id,
              entry_text,
-- In this example, the target is an URL (like javascript:loadPage(1);)
              entry_target,
-- entry_attribute_01 is used to denote the target page number
              entry_attribute_01,
              display_sequence,
-- Insert your authorisation function here
-- 'Y' is just hardcoded for demonstration
-- purposes for example -
--
-- my_auth.isPageAuthorised(:p_user_name,
--                          :p_app_id,
--                           entry_attribute_01)
--
                                        'Y'
isAuthorised
       FROM   apex_application_list_entries
       WHERE  application_id      = :p_app_id
       AND    list_name           = 'Main Menu'
    ),
menuHier AS
    (
      SELECT  list_entry_id,
              list_entry_parent_id,
              entry_text,
              entry_target,
              entry_attribute_01,
              display_sequence,
isAuthorised,
ROWNUM all_row_num,
              LEVEL                                      the_level,
              CONNECT_BY_ISLEAF                          isleaf,
SYS_CONNECT_BY_PATH( isAuthorised, ':' ) authPath,
SYS_CONNECT_BY_PATH( list_entry_id, ':' ) idPath,
             (
                SELECT  COUNT(*)
                FROM    apex_application_list_entries  l2
                WHERE   l2.list_entry_parent_id = menuList.list_entry_id
              )                                          child_count
FROM menuList
      START WITH list_entry_parent_id IS NULL
      CONNECT BY PRIOR list_entry_id = list_entry_parent_id
      ORDER SIBLINGS BY display_sequence,
                        entry_text
    ),
menuAuth AS
    (
SELECT menuHier.*,
              COUNT(*) OVER ()                                      row_count,
              ROW_NUMBER() OVER (ORDER BY all_row_num)              auth_row_num,
              ROW_NUMBER() OVER (PARTITION BY the_level,
                                              list_entry_parent_id
                                 ORDER     BY all_row_num )         item_row_num,
              LEAD(the_level, 1, 1) OVER(ORDER BY all_row_num)      lead_level
FROM menuHier
      WHERE   list_entry_id IN
                (
                  SELECT  DISTINCT
                          i.COLUMN_VALUE
FROM menuHier,
TABLE(stringToTable(idPath)) i
--                         Last character in path = Y
WHERE SUBSTR(authPath, LENGTH(authPath), 1) = 'Y'
                )
      ORDER BY all_row_num
    )
SELECT
--      START: JSON
        CASE
          WHEN the_level = 1 THEN
             '['
        END
--      ITEM: Indent levels
|| RPAD(' ',(the_level - 1) * 2)
--      ITEM: Comma separator
     || CASE
           WHEN item_row_num != 1 THEN
             ','
        END
--      ITEM: Item details
     || '{'
     ||   '"id":"'   || list_entry_id || '"'
     ||  ',"text":"' || entry_text    || '"'
     ||  ',"href":"' || entry_target  || '"'
--      ITEM: Leaf information */
     || CASE
WHEN isleaf = 0 THEN -- With children
            ',"leaf":false'
         || ',"children":['
WHEN isleaf != 0 THEN -- With no children
            ',"leaf":true'
        END
     || -- END: Item
        CASE
WHEN isleaf != 0 THEN
            '}'
        END
      -- END: Level
     || CASE
WHEN isleaf != 0 AND
               lead_level < the_level     THEN
RPAD(' ', 1 + ((the_level - lead_level) * 2), ']}')
        END
        -- END: JSON
     || CASE
          WHEN auth_row_num = row_count THEN
             ']'
END treeJSON
FROM menuAuth

This can go anywhere, in an application process, an on-load page process etc, etc. The joy of it is, once you have it working, you can still maintain the hierarchy in the APEX list using the GUI but you can affect it by anything you have in data. You could store ExtJS properties etc in your list entries and carry those through to the tree for example.

This hasn’t been fully tested yet but I will update with any bugs/limitations I come across.