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.


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.


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 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
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.


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.



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!

ODTUG Kscope13: The Clock is ticking…


So you haven’t decided if you should go to Kscope13. I’ll give you 5 reasons why you should stop procrastinating and register right away!

  1. 4½ days of great technical content for Oracle developers. Plenty of presentations and hands-on training on several areas such as APEX, BI, development tools, SQL, PL/SQL, etc… I guarantee you’ll wish you could clone yourself to attend several sessions at the same time.
  2. Are you the kind of Oracle enthusiast that is always reading books and blogs? If the answer is yes, let me tell that your favorite authors including Tom Kyte, Steven Feuerstein, Cary Millsap, Maria Colgan, Dominic Delmolino, Tim Gorman, etc… will be presenting at Kscope13. Most of them stay with you at the same hotel during the entire conference and they are very approachable people that you can meet and talk to.
  3. It’s a great opportunity to meet and network with fellow developers to learn about new stuff they’re working on. Maybe they already solved a problem you’re facing right now. I’m sure you’ll go back to the office with plenty of ideas/solutions for your own projects.
  4. You get to know the nice people that build and enhance the tools you love to use on a daily basis. You’ll have the opportunity to interact with Oracle product managers and developers that will answer your questions about their products.
  5. Last but not least, you get to visit beautiful New Orleans. I always wanted to travel to this historic city and never had a chance. Well, that’s about to change because now I have a great excuse to be there.

ODTUG really puts a great effort in order to make Kscope not only the best technical conference for Oracle developers but probably one with the best social activities also. Last year I attended for the first time and I really enjoyed it so much that I submitted an abstract for this year and it got selected. I really hope you can attend and if you do, make sure to stop by my session in case you haven’t heard about regular expressions before.

See you in NOLA!

Something I didn’t know: The REVERSE function

I was playing the pl/sql challenge the other day and the question was to identify valid implementations for a “reverse_string” function taking a varchar2 as the input and returning another varchar2 with the characters in inverted order.

One of the possible answers was this:

FUNCTION reverse_string (in_string VARCHAR2)
  l_return   VARCHAR2 (100);
  SELECT REVERSE (in_string) INTO l_return FROM DUAL;

  RETURN l_return;
END reverse_string;

My immediate reaction was to skip this choice because I have never seen REVERSE as a string manipulation function in Oracle; but then I thought…what if this is a tricky question.

So I ran this small query expecting a syntax error but guess what?

SELECT reverse('hello world')
FROM dual;

It worked! The function displayed dlrow olleh.

So REVERSE turned out to be an undocumented function that has been out there for a while. I did find blog posts from 2006 that talk about it. I guess I have been living under a rock all this time or maybe I never had the need for such functionality.

By the way, I’m not suggesting that you should go and write production code around this function, because it’s unsupported and may not even work properly with multi-byte character sets.

RMOUG Training Days 2013

Well… I made it to Denver and this place is really coooold but it’s also really nice. I have this wonderful view from my hotel room.


This is just a quick post to say that I’ll be presenting on Wednesday 2/13. Here are the details of my session:

Introduction to Regular Expressions in Oracle from 09:45 to 10:45 in room 4d

This will be the first time that I present at a conference, so as you can guess  I’m a little bit anxious and nervous but I believe is normal.

I hope to see you at this great conference.

Oracle Open World 2012

Oracle Open World 2012

Oracle Open World 2012

This is the second year in a row that I have the opportunity to attend the Oracle Open World Conference and looks like it’s going to be even better than last year’s.

I’ll be flying to San Francisco on Saturday morning and I’m planning to do some sightseeing at the Bay (a small cruise maybe) and hang out with some other conference attendees.

On Sunday I’ll be on all the ODTUG Database Development sessions.

From Monday to Thursday I’ll focus on sessions about database development, database performance, big data and some Exadata (looks like one of those monsters is coming my way). I will also attend some of the Oaktable World sessions featuring bright members of the Oaktable Network.

The afternoons and nights are going to be really interesting with all the receptions, music festivals and concerts that Oracle has organized including artists like Joss Stone, Macy Gray, Pearl Jam, Kings of Leon, etc..

I’m also looking forward to meet and network with other members of the Oracle community through the Bloggers Meet-up, the OTN Tweet Meet and the different activities taking place at the OTN Lounge.

I’m sure that by Friday I’ll be a zombie and ready to sleep the entire flight on my way back to Austin.

I’ll be tweeting my experience from @galobalda using the #oow hashtag. So if you see me, come by and say hi.

See you there.