Literally speaking

Reading Scott Wesley's blog from a days ago, and he made a remark about being unable to concatenate strings when using the ANSI date construct.

The construct date '1900-01-01' is an example of a literal, in the same way as '01-01' is string literal and 1900 is a numeric literal. We even have use some more exotic numeric literals such as 1e3 and 3d .

Oracle is pretty generous with implicit conversions from strings to numbers and vice versa, so it doesn't object when we assign a numeric literal to a CHAR or VARCHAR2 variable, or a string to a NUMBER variable (as long as the content is appropriate). We are allowed to assign the string literal '1e3' to a number since the content is numeric, albeit in scientific notation.

So there are no problems with executing the following:
declare
  v number := '1e3';
begin
  dbms_output.put_line(v);
end;
/

However while 3d and 4.5f can be used as numeric literals, Oracle will object to converting the strings '3d' or '4.5f' into a number because the 'f' and 'd' relate to the data type (Binary Float and Binary Double) and not to the content.

Similarly, we're not allowed to try to use string expressions (or varchar2/char variables) within a date literal, or the related timestamp literal. It must be the correct sequence of numbers and separators enclosed by single quotes. It doesn't complain if you use the alternative quoting mechanism, such as date q'[1902-05-01]' but I'd recommend against it as being undocumented and superfluous.

Going further, we have interval literals such as interval '15' minute .In these constructs we are not allowed to omit the quotes around the numeric component. And we're not allowed to use scientific notation for the 'number' either (but again the alternative quoting mechanism is permitted). 

I've built an affection for interval literals, which are well suited to flashback queries.

select versions_operation, a.* 
from test versions between timestamp sysdate - interval '1' minute and sysdate a;

Confusingly the TIMESTAMP keyword in the query above is part of the flashback syntax, and you have to repeat the word if you are using a timestamp literal in a flashback query. 

select versions_operation, a.*

from test versions between timestamp timestamp '2014-06-21 12:50:00' 
                   and sysdate a

Apex theme fun

Sometimes you are working with an off-the-shelf product and find something odd, and you're not quite sure whether it is a bug, a feature or whether you've lost the plot.

I use Oracle's Application Express, and was digging into the included theme_18. The templates refer to classes "t18success" and "t18notification"

And then I go looking into the CSS and see hash / ID selectors.

#t18Success{margin:5px auto;font-size:12px;color:#333;background:#DAEED2;width:600px;background-repeat:no-repeat;padding:5px;border:1px #95C682 solid;border-right:none;border-left:none;}

#t18Notification{margin:5px auto;padding:5px;font-size:12px;color:#333;text-align:center;vertical-align:top;border:1px #ffd700 solid;border-right:none;border-left:none;background-color:#ffffcc;width:600px;}

For added confusion, HTML class names are case-sensitive, but CSS selectors are case-insensitive, so the case differences may or may not be relevant.

The application looks nicer if I change the CSS to class selectors, and then I get coloured, dismissable boxes rather than hard to read, unstyled messages. I could probably get the same effect by changing the id="MESSAGE" in the templates, but that seems riskier. At least with the CSS, I am confident that I am just changing the appearance and it shouldn't affect the logic.

Digging deeper, the CSS for more than a dozen of the built-in themes utilise the ID selector "#notification-message" in the CSS. About half a dozen have only a class selector, and another three have both (with the prefix of t followed by the theme number). Finally three just have the ID selector with the theme prefix.

My gut feel is that they switched from the ID to the class selectors stopping in various places on the way. And some of those places aren't very pretty.

I'm not sure I see the benefit in having the theme number embedded in templates and selectors. The template tells it which theme CSS file to get, and as long as the template and CSS are consistent, the use of the theme number just seems to add more place you have to edit when you want to customise a theme.

This was all checked on a fresh Apex 4.0 instance because I just installed the new Windows 64-bit version of Oracle Express Edition. I'll do an upgrade of that default to the latest 4.2 this weekend too.