smithvoice.com
 Y'herd thisun? 

“Seen the funny and GREAT movie "the Big Dish?" Heading to Cali for a vaca? You have to visit the Goldstone array complex. Check this to see why”

from Visit to Goldstone by Smith

MONO/Ubuntu part 20 - Connecting Mono to Oracle

TaggedDatabase, ASP.Net, Linux

Now that your Oracle XE server is installed and running, you might like to hit it with a web page or two. You'll need to get a couple of extras for that, the System.Data.Oracle dll that's part of the MS.Net installation but not the Mono core and also a bit of middleware that will route your application calls through that dll to the Oracle server.

Like most things, this is all really easy once you've done it wrong a few times. Lucky for you, I've made all the mistakes already so all you have to do is just follow the bouncing ball.

Step 1: Get and install the Mono System.Data.OracleClient

On the Ubuntu server machine where you'll be running your Mono Apache2 web apps, run this command:

sudo aptitude install libmono-oracle2.0-cil

Step 2: Get and install the Oracle Instant Client "middleware"

Wow, this is a lot easier than the olden days of Oracle 8 and 9i. Just a simple XCOPY of files in a zip and a couple of text file settings. You'll have to sign up for a free Oracle account to do the Instant Client download, and worry not, they don't bug you with emails if you opt out of them during the account creation steps.

Get the Instant Client from Oracle's download page.

There are different downloads for 64 and 32 bit Linux, from the above Oracle page link choose the link for the bitver of your server.

On the actual download page for your OS, there will likely be a bunch of version groups with several package options in each. The Database version is 10g but the Instant Client version can be higher, go to the topmost/highest version on the download page (as of this writing that version is 11.1.0.7.0 for Intel chipped boxes). Within the version group you need just the "Instant Client", if you're running a USEnglish box, just get the "Instant Client Package - Basic Lite" zip. It's all you need.

Unzip the files in the zip to a folder on your desktop machine. Then using WinSCP (for Windows) or SCP commandline (for Mac/Linux desktops), copy the whole unzipped folder over to your server.

On your Ubuntu server, create a new folder to hold the files - it doesn't appear to matter what the folder is named, I call mine "orainstant". There are lots of different places you can make the folder, everyone in Linuxland has a different preference so it's up to you, but I suggest you just put them in a new subfolder of /usr/local because it's fast and easy to type.

The full path to the Oracle Express server itself is /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/... and some tipsters say to go that deep for your Instant Client. Have fun with all that typing if you want but it's not needed.

sudo mkdir /usr/local/orainstant

Copy the files from your home directory to the new folder (for this example I unzipped the download to a folder named "instantclient_11_1" and copied that over to the /home/kilgore directory so the command copies all of the files in that folder to the final destination folder created in the last step.

sudo mv /home/kilgore/instantclient_11_1/* /usr/local/orainstant/

Copy the files from your home directory to the new folder (for this example I unzipped the download to a folder named "instantclient_11_1" and copied that over to the /home/kilgore directory so the command copies all of the files in that folder to the final destination folder created in the last step.

sudo mv /home/kilgore/instantclient_11_1/* /usr/local/orainstant/

Now you have to inform your Apache/Mono service of the location of the Instant Client software. Do this with a startup loader helper text file:

sudo nano /etc/ld.so.conf.d/oracleinstant.conf

In this new text file just type the full path to the oracle instant client directory.

Save and close nano. The setting will be picked up whenever the machine is restarted but you don't have to reboot to force it in now. Force the setting into the system with this command...

sudo ldconfig

...And restart Apache so it picks it up now too:

sudo service apache2 restart

Step 3: Test it out.

Ready to test it out? Create a new aspx page in your webapp folder, I'm calling mine oratest.aspx.

sudo nano /home/kilgore/sites/asdf/oratest.aspx

Copy and paste this whole text to the nano but don't save it yet.

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Assembly name="System.Data.OracleClient" %>
<%@ Import Namespace="System.Data.OracleClient" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
 
protected void Page_Load(object sender, EventArgs e)
{
String connstring = "User ID=system;" +
"Password=password;" +
"Data Source= \'(" +
"DESCRIPTION=(" +
"ADDRESS=(PROTOCOL=TCP)(HOST=172.121.0.9)(PORT=1521)" +
")" +
"(CONNECT_DATA=(SERVER=DEDICATED)" +
"(SERVICE_NAME=XE)))\'";
 
using (OracleConnection conn = new OracleConnection(connstring))
{
 using (OracleCommand cmd = new OracleCommand())
 
 cmd.Connection = conn;
 conn.Open();
 cmd.CommandText = "SELECT SYSDATE FROM DUAL";
 DateTime retVal = (DateTime)cmd.ExecuteScalar();
 lblOracle.Text = "Server time is:" +
 retVal.ToLongDateString() + " " +
 retVal.ToLongTimeString();
  
 }
}
}
 
</script>
 
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="lblOracle" runat="server"></asp:Label>
</form>
</body>
</html>

Hopefully this looks pretty normal. Standard ADO.Net code from the days before so many easifying Wizards took the fun and power of SQL away from poor overworked developers ;-). We're querying the current SYStemDATE from Oracle's psuedo-table SProc host which is named "DUAL".

I've bolded two parts of the connection string, the Password and the "HOST" value which is the IP Address of the Oracle server. Replace those bolded values in your file with the password you set for the system account when you installed Oracle Express and with your own server's IP Address. Ctl-X to save the aspx file and close nano.

Yes, you can feel free to put the whole connection string in your web.config if you want, I did it all inline here to make copy&pasting faster.

Test that web page in a remote browser. Depending on the Instant Client you downloaded you may now get a web page showing you the current date/time on the server ... but most likely you'll get a 500 error concerning a missing dll.

 

This error is because Mono's Oracle dll is hard coded to look for the file libclntsh.so which was the correct file for the original Oracle client, but the name of the file changes with each Oracle Instant Cient update so all you have to do is reroute the call. You do that with a quick symbolic link (Linux's version of a Windows Shortcut) in the instant Client folder. In my case the /usr/local/orainstant/ folder had the file named libclntsh.so.11.1 to match the Instant Client version 11.1 so the link is made like this:

sudo ln -s /usr/local/orainstant/libclntsh.so.11.1 /usr/local/orainstant/libclntsh.so

Now lower level calls to libclntsh.so will be silently routed to libclntsh.so.11.1. No muss, no fuss. Restart the Apache2 service and try the oratest.aspx page again. If you see the server datetime, be happy!

If you still get errors, run through these steps again a bit more slowly.


jump to:

  • 1: Why?
  • 2: Installation
  • 3: Update the OS with APT
  • 4: Remoting to the box with SSH
  • 5: NANO quickies for 95% of the jobs
  • 6: Firewalling Ubuntu 8.10 Server
  • 7: Installing Apache2 and MONO
  • 8: Test client host files
  • 9: Configure MONO on Apache2
  • 10: Apache default pages
  • 11: Handling Apache and Mono Errors
  • 12: Subdomains and Christian porn
  • 13: Virtual Host Tweak: unmanaged cAse sensitiviTy
  • 14: Managed cAse sensitiviTy
  • 15: Managing files and folders
  • 16: Logging Apache Accesses and Errors
  • 17: Ubuntu Task Managers
  • 18: Ubuntu Services and Service Managers
  • 19: Installing Oracle 10g XE Server
  • 20: Connecting Mono to Oracle


  • home     who is smith    contact smith     rss feed π
    Since 1997 a place for my stuff, and it if helps you too then all the better smithvoice.com