Installing Oracle XE on Ubuntu

This is more a note to myself, as I seem to forget this step whenever I am installing Oracle XE on an Ubuntu server.

As well as all the usual suspects (build-essential, linux-header-server, etc), also remember to install:

• libaio1
• libaio-dev
• bc

Either that, or if you feel like downloading a random 200MB, you can install it from the Oracle repositories.

Now excuse me, while I go beat myself over the head for spending hours trying to work out why Oracle wasn't working.

CASE in ORDER BY statement

This was one of those things where I've just gone 'I wonder if this works..' and strangely enough it DID!

Situation was something like the following (okay - something is a very broad term, because it wasn't really like this at all) -

I have a document management system - and each document has a document_createdDate and a document_releaseDate - however if the user chose to release a document 'Now' the document_releaseDate would be set to NULL.

So when I went to order a document list by document_releaseDate, obviously that would be rather hard, as values could be NULL in a date list.

So I figured - why not sort such that, if is document_releaseDate is NULL, then sort by the document_createdDate - that would provide a able solution to my sorting issue.

Then I discovered I could put CASE statements into my ORDER BY clause - and that made things real easy - e.g.

select *
from
tbl_document
order by
case
WHEN document_releaseDate IS NULL then
document_createdDate
else
document_releaseDate
end DESC

And presto - a conditional sort! Of course there are other ways to solve the same problem, but I thought that was nifty.

This was done a Oracle 9i database.

Oracle 9i Hierachical Data support

Was searching for a way to do ordering within CONNECT BY clauses in Oracle PL/SQL, and came across this great article on all sorts of aspects of Hierarchical data support within Oracle 9i.

Querying Hierarchies: Top-of-the-Line Support

Particularly interesting facts include the ability to ORDER SIBLING BY to order within a Hierarchical query, and using SYS_CONNECT_BY_PATH to display an output of the tree in delimiter separated list.

Good reading!

Connecting CFMX Pro to Oracle

This has been convered in a few places, but people ask this alot, so I figured I would drop my version of the answer somewhere that may be a little easier to find. I've done this a few times here and there.

There are a variety of ways that you can connect Oracle, but most of them provide limited functionality.

JDBC Thin driver

Details can be seen here:
ColdFusion Technote
CFGuru Blog (Yup, that comment at the bottom is me).

Pros - This is by far the most lightweight installation to get Oracle connected with CFMX.

Cons - You cannot get Result Sets to return from Stored Procedures using this driver. (There may be other stored proc issues I may not be aware of, but I hit this one and gave up.)

Oracle ODBC Driver

Pros - You have the full functionality of Oracle (i.e you can get result sets out of stored procs).

Cons - It's a longer and slightly more tricky install process as you have to install the Oracle Client.

Personally, I like the ODBC version the best, simply because getting result sets out of Stored Procs is of high priority to me.

The steps for installing the ODBC version are the following:

1. Download the Oracle Client (It's big. Be Warned.)
2. Download the Oracle ODBC driver.
3. Install the ODBC Client (create an 'Oracle Home' in any dir. Default should be fine).
4. Install the ODBC drivers (You may have to do this with the Oracle Universal Installer, which comes with the Client).
5. Open Oracle - oraHome > Configuration and Migration Tools > Net Manager
6. Click Service Naming in the tree.
7. Click Edit > Create from the menu at the top. You will get a new window.
8. Enter the Net Service Name. I tend to use the same name as the Oracle SID/Service Name. Click Next
9. Leave at TCP/IP and click Next.
10. Enter the Host Name of the Oracle server (i.e. oracle.mysite.com), and the port number. The default Oracle port is displayed. Click Next.
11. Enter the SID/Service name of the Oracle database you are connecting to. Leave as Database Default for connection type. Click Next.
12. Click Test to test the Oracle connection. First test will probably fail, as it will have the wrong login details. Enter the right details and try again.
13. Once the connection works, click Finish.
14. Exit the Net Manager and click Save on the dialog.
15. Goto Control Panel > Administrative Tools > Data Sources.
16. Click System DSN.
17. Click Add.
18. Choose Oracle in OraHomeand click Finish. (Why finish? you're not done!)
19. Enter the name in DataSource Name. Doesn't really matter what. I tend to stick to the SID / Service Name.
20. Choose your TNS Service Name from the drop down provided.
21. Test the connection. The first test will probably fail, as the username and password will be wrong. Enter the correct details until it works.
22. Click OK.
23. Now go into the CF Administrator > Data Sources
24. Add a ODBC Socket datasource.
25. Choose the Oracle ODBC DSN from the drop down.
26. Click Show Advanced Settings and enter the username and password (if required).
27. Click Submit.
28. The datasource should now validate (if it doesn't, try restarting the machine, as sometimes a restart is required for the new odbc connection to take full effect).

I don't think I've missed anything. Like I said, it's a tad long and convoluted, but it does work in a production environment.

Once it's set up once, adding a new datasource is a 5 minute job.

I hope that solves someone's problem, because it took me a few weeks to work that one out.

 

Oracle 9i Database Documentation

If anyone is wondering why I poste the 9i documentation, is because it always seems to me that most people have trouble finding it in the Oracle site. It's buried pretty deep.

(Or maybe they don't look - either way, it's right here)

Oracle9i Database Online Documentation

I must say, as far as documentation goes - it is very thorough.

Enjoy. :o)