Wednesday 30 March 2011

SQL Developer 3.0 is out there

One of the first Oracle related news I noticed this morning was SQL Developer 3.0 has just been released.

Make sure you check out the release notes, while I've downloaded it I think I won't have time to take it out of the box for a little while - so I'll have to be content with the manual :-(

Congratulations to the development team for reaching this significant milestone.

Tuesday 22 March 2011

Off topic: 'A' Week

Some days I decide that since this is my blog, I'll say whatever I like, even if it's not about work! This is my major outlet to the world that is.
Heck, Tim does it all the time ;-)

This week, besides being my birthday week, is 'A' Week. It basically means tell others you’re an atheist and proud of it week. Start some discussions with people about your atheism.
A Week is not about being disrespectful to religion or people who have religious views, it’s about quietly showing that there are more people than may be realised who are ‘Good without God’ and who don’t need religion to influence their lives. Link
Recently I saw Chris asking the question, I was very curious to see who responded. Around the world responses differ when it comes to talk about people's beliefs. This week is about helping break down those barriers as some people even feel bullied for a variety of unfortunate reasons. Of course, at times around the world it goes both ways, so the week is also about understanding that it's not opposing sides of the fence, but a way of life unto it's own.

Many, myself included, would like a world of religious freedom - people can believe anything they want, as long is it does not harm or annoy others. And this goes for many more things beyond religion.

This has been said by others in a much more eloquent manner, but I enjoy the wonders of the universe without looking for something imaginary.

Scott

Tuesday 15 March 2011

Date format tolerance

This is just a little ditty to remind me which date format mask is more tolerant.
SQL> select to_date('21-03-2011','dd-mon-yyyy') from dual;
select to_date('21-03-2011','dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date('21-mar-2011','dd-mm-yyyy') birthday from dual;

BIRTHDAY
-------------------
21-03-2011 00:00:00

1 row selected.
Note that it accepts 'MAR' when using the MM format, but not numerics when using the MON format.

You may like to consider this when defining an application wide default format.
Shared Components -> Globalization Attributes
Scott

Tuesday 8 March 2011

Apex in the Amazon Cloud


Hello Oracle Community,

I'm currently researching Oracle Apex on an Amazon EC2 instance, and at the moment good content out there seems a little thin - or quickly dated.

Either that or my google kung fu has weakened :-)

The basics I have covered -
  • Justin Lokitz has a comprehensive article on setting up Oracle on the cloud;
  • There is also a good Oracle white paper explaining the setup process;
  • Bill Hodak from Oracle has defined a perfect machine image;
  • This is probably the best dialogue I've seen on sending e-mails. I have seen others, but exact solution is  unclear and possibly out-of-date;
  • And Kris has mentioned SEO considerations in regard to Apex generated URLs. There are other decent discussions, but one pertains to EPG setups, and I'm sure things have come along a little more since 3.1.2
My particular interests are -
  1. Sending e-mails from EC2 using UTL_MAIL, preferably with a designated domain (not Amazon)
  2. Prettying up the Apex URLs, and/or other issues to consider with page ranking
No doubt once I start getting my hands dirty, some of theses answers may become more apparent, or I'll have a better idea of how/where to look - but for now if anyone has any notes, tips, experiences, URL suggestions, answer to the life, universe and everything - I would appreciate it!

ScottWe

ps - if you just wanted pictures of pretty clouds, try these - simply stunning.


Update: I've also created an OTN post on this, stay tuned

Friday 4 March 2011

Adelaide One-Day AUSOUG Conference 2011

The South Australian branch of AUSOUG are holding a one-day conference on Monday March 28th at the Mercure Grosvenor Hotel in Adelaide.

I will be presenting there, along with fellow Perth-ite Connor McDonald.

I will be looking forward to seeing some people I haven't seen for quite some time, and meeting some others I see on the schedule I have not yet had the opportunity to meet, such as Craig Shallahamer.

Further details can be found at AUSOUG.

ScottWe

Thursday 3 March 2011

Apex Listener Issues

The configuration for my Apex 4.0 environment on my laptop is basically as you see on the front page for the Apex Listener.


I have Google Chrome on the left; my J2EE Container is Glassfish; and I have Oracle 11gR2 on the right, with Apex4.0 installed.

Sometimes I hate to admit it, but I don't do "networking". My Achilles heal is anything starting with "serv".

So when I rebooted my laptop recently and my browser said I couldn't connect to Apex, I thought - "I wonder what part of that horrible black box in the middle has broken?"


System Unavailable? I don't think so, Mr Laptop.

It wasn't really that bad. I pretended it was a development issue and started eliminating factors.
  • My database was up
  • I was about to get into the GlassFish Server Administration console ok (http://localhost:4848/), there didn't seem to be anything out of place.
  • I restarted the server, the TNS listener, the Apex Listener
  • I read through some documentation to look for troubleshooting ideas
  • my apex-config.xml file was present and ok
Then I found myself looking at the username/password combination for APEX_PUBLIC_USER and thought "I wonder if that password has expired..."

Sure enough, all I needed to do was reset the password.
ALTER USER apex_public_user IDENTIFIED BY my_awesome_password;

Problem solved.

I mention this because it could have quite easily bugged me for an hour, and I like to save some people pain. I know there are some out there learning Apex and their deployment is balancing on guesswork from (barely) following Oracle's installation instructions.

In other news, I find myself reading through Oracle's Apex Listener front page, and I don't notice the big red

APEX Listener Release 1.1 is now available


So Apex developers, the Apex Listener Release 1.1 is now available ;-)

ScottWe

Tuesday 1 March 2011

I quizzed an Oracle community

Once was a time when I read a few questions every morning at AskTom. It only took a few minutes but the amount of hints, tips, tricks and general best practice guidelines I learned became so valuable as my experience grew.

I still find it a valuable resource, but these days as a seasoned developer, I found myself a solution with a different style of learning - keeps my mind active and I learn the odd thing occasionally, other times it helps affirm my current understanding of Oracle behaviour.

Steven Feuerstein and his friends have created a (business) daily PL/SQL Challenge. You may have heard about it and wondered if you should give it a go - for most probably a variety of reasons.
Well, you don't even have to compete for the frequent prizes - you can have a private profile; or you may see people providing a link to their public profiles on their blog; or as a resume item.

The main thing is the occasional "test your knowledge" - typically a little snippet of code to have you thinking for a couple of minutes.

Steven has provided players an opportunity to submit their own quiz questions, so if you're curious of the sort of questions that get asked, here's mine from 28th February 2011 - it's a lesson on Short Circuit Evaluation:



I create and populate a table as follows:

CREATE TABLE plch_parts
(
   partnum    INTEGER
 , partname   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_parts
        VALUES (1, 'Mouse');

   INSERT INTO plch_parts
        VALUES (100, 'Keyboard');
   COMMIT;
END;
/

Then I define the following function:

CREATE OR REPLACE FUNCTION part_exists (pn_partnum  IN  plch_parts.partnum%TYPE)
RETURN BOOLEAN IS
  ln_partname  plch_parts.partname%TYPE;
BEGIN
  SELECT partname
  INTO   ln_partname
  FROM   plch_parts
  WHERE  partnum = pn_partnum;

  DBMS_OUTPUT.PUT_LINE(ln_partname);

  RETURN TRUE;
EXCEPTION WHEN NO_DATA_FOUND THEN
  RETURN FALSE;
END part_exists;
/

Which of the choices, when used in place of the /*IF CONDITION*/ comment in the following block,
will result the subsequent output being displayed on the screen after the block is executed?

BEGIN
  /*IF CONDITION*/
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

Mouse
Keyboard
TRUE




A
IF part_exists(1)
OR part_exists(100) THEN
B
IF NVL(part_exists(1)
      ,part_exists(100)) THEN
C
IF COALESCE(part_exists(1)
           ,part_exists(100)) THEN
D
IF part_exists(1)
AND part_exists(100) THEN
E
IF CASE WHEN part_exists(1)
OR part_exists(100) THEN TRUE END THEN



The rules are well written, the idea is well presented (unlike my hand written html table representation here), and the user feedback is fantastic. Steven also has an associated blog for constructive discussions on the occasional contentious question, or question of general interest or quirkiness.

My question was well received by some friends, and Steven now provides users with simple, key survey results from each question - and I was happy with mine.

The answer is B & D - "NVL" & "AND"
I provided the following verification code to assist explanation of the answer:
BEGIN
  -- As soon as the first expression returns true, PL/SQL no longer needs to evaluate the second. This is perfect for situations where you can put more efficient calculations first.
  IF part_exists(1)
  OR part_exists(100) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- Ideally this would behave as per COALESCE, but unfortunately NVL does not support short circuit evaluation.
  IF NVL(part_exists(1)
        ,part_exists(100)) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- COALESCE is ANSI-defined shorthand for similarly written CASE functions, thereby supporting short circuit evaluation. COALESCE is generally preferred to NVL.
  IF COALESCE(part_exists(1)
             ,part_exists(100)) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- Since this uses the AND operator, both expressions need to be evaluated to determine if the entire boolean equation is true. Oracle would use short-circuit evaluation if the situation permits, for instance, if the first expression returned false the second would not be evaluated.
  IF part_exists(1)
  AND part_exists(100) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- The Oracle database will use short circuit evaluation for CASE expressions.
  IF CASE WHEN part_exists(1)
  OR part_exists(100) THEN TRUE END THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

There are currently over 1000 regular players, but only about two dozen regulars are registered as Australian - I've seen more than that at our AUSOUG branch meetings! Come on Aussies - "represent!"

It's a great learning & affirming tool - I'll be on the lookout for inspiration for submitting more questions. :-)

ScottWe

ps - no, you can't cut & paste the code while a question is active

Advert: SAGE Computing Services Training - 2011

This April SAGE Computing Services are holding a training event that may interest beginners, business users, testers & developers alike.

April 18th-21st 2011 - Oracle SQL 10/11g Workshop - Perth
The course is designed to provide the student with a basis for developing systems using the Oracle database. The SQL language is covered from simple to complex constructs. Guidelines are provided on writing SQL for optimum performance and ease of maintenance.

Stay tuned as we are also looking to schedule a JDeveloper Workshop in the next few months, and are happy to hear about any requests for Application Express 3.x or 4.0 training.

We also arrange on-site training on-demand around Australia.

Hit up our website for further training enquiries and follow through to the contact us details page. We look forward to hearing from you soon.