Oracle Instant Client on Mac OS X

A while back I broke down to the peer pressure in the APEX community (you know who you are ;-) and bought a Mac Book Air. I'm still learning the ropes and one thing that took some time to get working was the Oracle Instant Client. Installing it isn't as straightforward as installing the Instant Client on Windows.

I finally got it working and thought I'd post what I did for others that are new to OS X.

Download Instant Client

First you'll nee to download the Instant Client for OS X. I downloaded the following files for Version 11.2.0.3.0 (64-bit):

- instantclient-basic-macos.x64-11.2.0.3.0.zip
- instantclient-sqlplus-macos.x64-11.2.0.3.0.zip

Unzip both files and put their contents in /oracle/instantclient_11_2/

Setting Paths

You'll need to set the appropriate paths to load by default in Terminal. Open Terminal and run:

vi ~/.bash_profile

Add the following to the file:

#in ~/.bash_profile
DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/oracle/instantclient_11_2
export DYLD_LIBRARY_PATH

PATH=$PATH:/oracle/instantclient_11_2/
export PATH

#Create this directory and put your tnsnames.ora file in it
TNS_ADMIN=/oracle/instantclient_11_2/network/admin/

Running SQL*Plus

Now when you open a new terminal window you should be able to run SQL*Plus

#ex: sqlplus username/password@//server:port/sid
sqlplus system/oracle@//localhost:1521/XE
#or use a connection string leveraging a tnsnames entry

Additional Addons

When using SQL*Plus in Windows you can use the Up arrow to get your previous command. If you do that in Linux you'll get some weird character. The good news is that there's a program to resolve it called rlwrap. CJ Travis has a good post on how to install rlwrap of Mac OS X.

Console Wrapper 1.0.4 - Beta

Recently Dimitri Gielis sent me an email letting me know that there was a bug with Console Wrapper 1.0.3. He also include the following screen shot with the error:

In case  you can't see it, the error is "Object doesn't support property or method 'apply'". It turns out that the issue was caused by a change in IE9 and IE10 on how they handle the console object. The following article describes the issue in detail: http://whattheheadsaid.com/2011/04/internet-explorer-9s-problematic-console-object

I've fixed Console Wrapper to handle the issues presented in IE9 and IE10. Before I officially release 1.0.4 on https://code.google.com/p/js-console-wrapper/ I'd like some people to beta test it. If you're interested in testing 1.0.4 you can download it here. If you find any issues please send me an email (my email address is in the comments section at the top).
 
Update: 6-Apr-2013: 1.0.4 has been officially released and is now available to download on the official project page.  

Thanks in advance.

And the Winner Is...

Two weeks ago I posted a question/contest for Who's Birthday is it in "n" number of days? I received 63 answers (within the time limit) all with some interesting and unique approaches. Here's how I went about picking the winners:  

Leap Year

One thing that most people got wrong was what happened if the hire date was on Feb 29th (during a leap year) while the current year did not have a leap year. I could do a search online to find some recent leap years, instead I decided to wrote a query to find recent leap years.

-- Find past leap years
-- https://forums.oracle.com/forums/thread.jspa?threadID=1128019
select yr, 
  case 
    when mod(yr,400) = 0 or mod(yr,4) = 0then 'Leap Year'
    else null 
  end leap
from (
  select extract (year from add_months(sysdate, -8*12)) + level - 1 yr
  from dual
  connect by level <= 8
);

Test Data

Instead of testing against the main sample EMP table I recreated the EMP table with all the dates in a given leap year (2008). I kept the same structure as the EMP table so that it would be easy to test your solutions against.

drop table emp;

-- create mock emp table with all the dates (including leap year
create table emp as
select level empno, level ename, level job, level mgr, to_date('01-01-2008', 'DD-MM-YYYY') + level - 1 hiredate, level sal, level comm, level deptno
from dual
connect by level <= 366;

Picking the winner and testing solution:

Since we had 63 entries I wasn't going to test them all. Instead I decided to randomly pick solutions (based on the entry number) and validate their solution. If they were correct I'd mark them as a winner. If not, I'd move on to the next entry until I found 2 winners.

Here's how I picked the random entries:

-- Chose random winners
select trunc(dbms_random.value(1,63))
from dual;

To test the solutions I set my current SYSDATE to 1-Feb-2013 so that it would have to encounter people that were hired on 29-Feb (even though 29-Feb doesn't exist in 2013).

ALTER SYSTEM SET fixed_date='2013-02-01-14:10:00';

Winners

The winners are Erik van Roon (aka "Belly") and Iudith Mentzel. Please email me (my email address is top right corner of blog) and I will forward your information to Red Gate so that you can claim your prize.

Thanks for all the entries and thanks again to Red Gate for offering up the prizes (licenses to their new product: Source Control for Oracle). If you haven't already seen what Source Control for Oracle is or want to download a free trial I encourage you to check it out.

Kscope 13

Taking a note out of Simon Sinek's famous TED talk, I won't focus on the How or What for Kscope 13, rather the Why.

So why does Kscope exist? It's goal is to bring together Oracle developers from around the world and offer them a unique learning and networking experience unlike any other conference.

I've been to many other conferences, Oracle related or otherwise, and none offer the experience that you will get at Kscope. That being said, they're still 12 days to take advantage of the Early Bird rate and if you haven't already signed up I encourage you to do so!

For those that prefer more of the How and What about Kscope 13, you to check out the website: kscope13.com

Who's Birthday is it in "n" number of days?

A friend of mine recently asked me the following question: "I have a table with names and birthdays. How do I find who's birthday is coming up in the next 15 days". Initially this it appears to be a simple question but it's a bit more complex than I originally thought.

I was going to post my solution here, then James Murtagh, from Red Gate, offered me two five-user licenses for their new product called Source Control for Oracle to give away to the readers of this blog. Instead, I'm going to run an informal contest to give away these sets of licenses (each valued at $1475). As with all contests, please read the terms and conditions from Redgate.

So here's the question (similar to my friend's question about birthday's but on the common "EMP" table): Suppose that I'm the HR manager and am planning to recognize the anniversary date that each employee was hired on. I'd like to know all the employees who's anniversary hire date is in the next 30 days.

Please post your solution in the comments section below. Every answer with a correct solution will have their name entered into the draw. Answers must be submitted by end of day on Friday March 15th. I'll announce the winners next week.

Notes:
- Use SYSDATE for today's date. I'll just alter the FIXED_DATE setting in oracle to set the SYDATE value for my testing.  
- Write your query for the default EMP table. If you don't have the EMP table in your schema this article contains the scripts to generate it.

I'm looking forward to everyone's solutions!

Update: Please read the follow up post to see how I tested this solution.