SQL Developer 4: JDBC Errors?

Yesterday I installed SQL Developer 4 on a Virtualbox VM that also contains Oracle 11g R2 and SQL Developer 3.2.20. I had no issues installing it; but as soon as I started clicking on anything that required a database connection (which is pretty much everything), the following error showed up.

sqldev4_jdbc_error

I went to the preferences and under the Database/Advanced section I found the option where you can choose between the OCI Thick driver or the JDBC Thin driver. My configuration after the initial install was set to use the OCI Thick driver as you can see.

sqldev4_oci_thick

So I cleared the checkbox and everything went back to normal. I know the JDBC Thin driver is the default option, so now I was wondering how come I ended up with the OCI Thick driver instead.

After a few minutes, I realized that my other SQL Developer installation (3.2.20) is using the OCI Thick driver and every time you install a new version of SQL Developer a pop-up window displays asking if you want to copy your existing preferences (which I did). That explained part of the puzzle.

Now I still have to figure out why the OCI Thick driver is working with version 3.2.20 but not working with version 4. Hopefully That Jeff Smith will give me a clue about what’s going on 🙂

Update 07/15/2013: As Jeff suggested in the comments section, a recent version of ocijdbc11.dll (instant client 11.2.0.3) placed into the /sqldeveloper/bin directory fixed the issue. This is a temporary workaround until this is corrected in the GA version.


An Attempt to Validate Email Addresses with a Regular Expression

After my Kscope13 presentation, one of the attendees approached me and asked if I had used regular expressions to try to validate email addresses. He didn’t know about regular expressions before the presentation and he was writing a PL/SQL function (which was getting very complex…) to accomplish this particular task. I offered to give it a try and blog about it.

After some testing, this is the regular expression that I came up with: ^[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{2,3}$

So basically, I consider an email address to be valid (that’s my definition for this example) if it begins with a sequence of characters that may include any letters, any digits, the dot, the underscore or the dash followed by the @ and then followed by a sequence of characters that may include any letters, any digits, the dot or the dash followed by a dot and ending with a sequence of characters that contains a minimum of 2 and a maximum of 3 letters.

I know there’re more valid characters that can be used to define an email address but I chose the ones that are used most of the time. Another thing to keep in mind is that this regular expression is case sensitive, so in order to match email addresses that contain capital letters I’ll rely on the parameter that can be passed to the regexp SQL functions to make the match case insensitive.

So lets take a look at some sample data stored in the clients table:

SQL> select * from clients;

NAME_CLIENT               PHONE                EMAIL
------------------------- -------------------- ------------------------------
FOUNTAIN,VICKIE L                              Vickie.L.Fountain@mail.com
ORTIZ,GENOVEVA M                               123ortiz.gmail.com
MALDONADO,TERESA                               teresa-maldonado@amazon.store
BARAJAS-RAMIREZ,AMY
SERRANO,GRACE
BALDA GALO                (512) 254-1694       galo@email.mydomain.net

6 rows selected.

As you can see, there’re email addresses that match the pattern (1 and 6) and other that don’t match the pattern (2,3).

Lets find the records that match the pattern first:

SQL> select *
  2 from clients
  3 where regexp_like (email,'^[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{2,3}$','i');

NAME_CLIENT               PHONE                EMAIL
------------------------- -------------------- ------------------------------
FOUNTAIN,VICKIE L                              Vickie.L.Fountain@mail.com
BALDA GALO               (512) 254-1694        galo@email.mydomain.net

2 rows selected.

Note how the first email matches the pattern even thought I’m not defining a range for capital letters. This is happening because I used the ‘i’ parameter that tells the function to perform a case insensitive search.

Now lets find the records that don’t match the pattern:

SQL> select *
  2 from clients
  3 where not regexp_like (email,'^[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{2,3}$','i');

NAME_CLIENT               PHONE                EMAIL
------------------------- -------------------- ------------------------------
ORTIZ,GENOVEVA M                               123ortiz.gmail.com
MALDONADO,TERESA                               teresa-maldonado@amazon.store

2 rows selected.

It’s obvious that the first email doesn’t match the pattern because the @ is missing. The second email is incorrect because I specified that the top level domain is only valid if it’s a sequence of 2 or 3 letters at the most and store has four.

So, this is an example showing very basic validations on email addresses. There’s room for improvement but it will always depend on your use case and how complex you want to make it.

I hope you find it useful.


Kscope13 Thoughts

It has been a week since I returned from Kscope13 in New Orleans and just now I’ve found some time to write about it.

This was my first visit to this city and I really enjoyed running errands to see historic locations and looking for places to try the amazing food they have. It was also nice to see again friends that I got to meet last year in San Antonio and to meet new ones like Amy Caldwell and Bobby Curtis.

The conference had great content as usual and I really enjoyed the presentations that I attended. I came back with a nice list of things to test and see if they are a good fit for the applications I develop/manage.

This year I had the privilege to present for the first time and it was a very positive experience. Surprisingly, my “Introduction to Regular Expressions in Oracle” session was very well attended and I had a good interaction with the audience.

Presentation

I wasn’t expecting too many people because of the day (Thursday, after a bigggg party the night before…) and the time (last session); but it was encouraging to see them trying to make the most out of the conference until the very end. Some of the attendees were very kind to tweet about the presentation (thanks for the picture Amy Caldwell!).  In the end I had a few interesting questions that I’m planning to turn into blog posts.

tweets_presentation

tweet_kscope_richard

Once again ODTUG and YCC did a great job to put together a wonderful event. I’m already looking forward to next year.

The registration and abstract submission for Kscope14 are open. Make sure you get to Seattle to be part of it!