Oracle


Its been a while that I have been trying to set up a development environment on my box at home. One of the challenges was using MySQL. (Its not that there is anything wrong with MySQL; its more of a procrastination problem with me. 🙂 to move to something other than Oracle. I have been working on Oracle for sometime and I was keen on getting the same environment for my development at home.)

I tried Oracle Express Edition today and was amazed at its simplicity. I was up and running with some sample code in a couple of hours. I downloaded the deb package on my Ubuntu and the installation was as easy as clicking the package and running this command sudo /etc/init.d/oracle-xe configure
Here is a good article that spells out some details. But it was way easier for me. Once all the ports were configured and the database server installed , the management was easy. I used http://127.0.0.1:8080/apex url to get to the admin screen.(use the same ports that you specify when configuring the server) Creating users, configuration was all intuitive.

There is a tasks menu on the right side of the page that shows a link “Manage Http Access”. If you enable remote access by going to that page, the admin panel can be accessed from any other system remotely.

I couldn’t get the PL/SQL developer to work on the client machine to talk to the server, but SQL Developer did not give me any problems. PL/SQL Developer had problems with the tnsname but SQL Developer worked. Not sure why. Here is the tns configuration I used: (Got it from here)

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
hostname)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

And this is the connection url that I used from my application code:
jdbc: oracle:thin:@hostname:1521:XE

Now that I have a new playground, I hope to add more to my blog soon.

Other Links:
JDBC Driver
Oracle XE download for Linux

Advertisements

Having fixed the problem with the KVM switch on my machine I proceed to get my Ruby scripts working on Ubuntu. Installing Ruby and getting Rails is a breeze. The problem starts when I need to install ruby-oci8. You cannot get this by doing a gem install….need to download the tar.gz file and run the setup. I try to use tar -xvf ruby-oci8-1.0.0.tar.gz but it fails. (some problem with the header of the file). I then use gzip and tar commands separately and it works.

To install ruby-oci8 you can either do this

make
make install

or do this

ruby setup.rb config
ruby setup.rb setup
ruby setup.rb install

When I try them, both fail. Looking at the error I see that I need to configure ORACLE_HOME (in case of Oracle) or need to configure LD_LIBRARY_PATH (in case of Oracle Instant Client). I know that I don’t have them on my machine so I proceed to get the Oracle Instant Client. I read some blogs (see References) and find that I need the basic.zip and sdk.zip packages. I download them and unzip them at this location /opt/oracle/instantclient (need to sudo). Having done that I set my LD_LIBRARY_PATH to /opt/oracle/instantclient.

LD_LIBRARY_PATH=/opt/oracle/instantclient
export LD_LIBRARY_PATH

I then proceed to create two symlinks

ln -s libclntsh.so.11.1 libclntsh.so
ln -s libocci.so.11.1 libocci.so

Note: In the above case, the names would depend on the version of the instant client that you have.

I go back to try out the ruby-oci8 setup and it fails again. Back to square one and I seem to have run out of ideas. A friend suggests me to check if the instant client works properly by installing sqlplus and connecting to the database. I go ahead with that (I would need sqlplus anyways).
I get the sqlplus.zip file from here. Unzip it to the location /opt/oracle/instantclient. I now go ahead and set the environment variables through the command line. (I have the LD_LIBRARY_PATH set already) and add my tnsnames.ora file to the same location.

PATH=$PATH:/opt/oracle/instantclient
SQLPATH=/opt/oracle/instantclient
export SQLPATH
TNS_ADMIN=/opt/oracle/instantclient
export TNS_ADMIN

I try this command at the command line now. (where the username , password and tnsnames have appropriate variables)

sqlplus username/password@tnsname

I get this now:

sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

What the !! Now what is this supposed to be?? I read more and run these commands:

ldd sqlplus
strace sqlplus

The first command gives me a list of library dependencies that sqlplus requires and the second command (strace) gives me a trace of system calls and signals. (see man pages of the command for details). Both indicate that a library (libaio) is missing. I run the following command :

sudo apt-get install libaio-dev

Now when I try to run sqlplus I get this error:

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Dec 21 12:57:36 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

At least I am close. Hmmm…problem with the tnsnames. Soon, I find that I had set TNSADMIN instead of TNS_ADMIN. sqlplus runs once i correct that. So, I can now connect to the database and am sure that the instantclient is installed correctly. Back to ruby-oci8 now.
Running the setup gives me the same problem. This time however I notice that the instantclient is recognized but there is this error (I guess the instantclient was not the problem after all …doh !!)

checking for cc... ---------------------------------------------------
error messages:
Permission denied - mkmf.log
---------------------------------------------------

I see that mkmf.log exists in the oci8 dir structure and has root privileges. So, I need to run the setup as root. But since all the environment variables are set on my login the setup complains that it does not see LD_LIBRARY_PATH. I go ahead and add all environment variables to /etc/profile. For global environment variables in Ubuntu see this. I restart my shell and run the following.

sudo -i
ruby setup.rb config
ruby setup.rb setup
ruby setup.rb install

Success. oci8 now installs without complaints. I try my ruby scripts and everything looks good. The fruit of persistence is definitely sweet.

UPDATES: I was helping a friend install oci8 on his machine when we faced some more hurdles. Adding them here:

1. mkmf not found: You will need the development version of Ruby. sudo apt-get install ruby1.8-dev
2. Make sure you install 32 bit versions and not 64 bit ones.
3. If you see this error : “libsqlplus.so: cannot open shared object file:”, you are missing the instantclient path. Somehow adding the path in /etc/profile did not get picked up in our case. We had to add it in the .bashrc file. Make sure you open a new terminal after the changes.
4. If you have problems running ruby setup, install etc (see the last step above), check to see if you have the build-essential package installed: sudo apt-get install build-essential

References:
http://ubuntuforums.org/archive/index.php/t-92528.html
http://roqet.org/oracle_instantclient.html
http://ruby-oci8.rubyforge.org/en/InstallForInstantClient.html
http://craiccomputing.blogspot.com/2007/01/using-oracle-as-backend-to-rails.html
http://ubuntuforums.org/archive/index.php/t-535311.html
http://rubyforge.org/forum/forum.php?thread_id=13948&forum_id=1078
https://help.ubuntu.com/community/EnvironmentVariables

Technorati Tags: , , , , , , , , , , ,

Powered by ScribeFire.

It is no surprise that programmers spend a large portion of their time wading through tons of data. Data and applications go hand in hand after all. It was in one of a recent customer requests that I started looking for certain missing pieces of information in the database and got into the whole data crunching game. It was like looking for a needle in a haystack. (Needles in a haystack would be more appropriate; I never knew how many I was looking for; My manager described it as counting 50,000 cats that kept moving around) The good part of this not so interesting request was that I learnt ways to optimize queries, some new tools and tricks. Getting familiar with Oracle Regular Expressions was one of them.

Oracle regular expression is a new feature in Oracle 10g that gives a lot of flexibility in textual searches. Oracle introduces the REGEXP_LIKE operator and the REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE functions which are similar to the LIKE operator and the INSTR, SUBSTR and REPLACE functions respectively.

REGEXP_LIKE: Searches for a expression in the specified column

e.g. SELECT * FROM names WHERE REGEXP_LIKE(first_name,’^(.{5})$’ )

This will return records that have exactly 5 characters (except a newline) in the first name

REGEXP_INSTR: Returns the starting position of a patters

REGEXP_SUBSTR: Extracts a part of the string based on the expression passed to it.

REGEXP_REPLACE: Similar to the REPLACE function except that it takes the expression as the replace string.

POSIX is supported by this implementation which means that we could use expressions like [[:digit:]]{5} or something like [[:upper:]]{4}. The first expression looks for 5 digits and the second looks for 4 uppercase characters.

When to use REGEXP_LIKE and when to use LIKE ?

Most of the articles that I read complained about the slow performance of the regular expressions in Oracle. (compared to the corresponding operator/ functions). The best bet would be to use LIKE / INSTR/ SUBSTR / REPLACE where possible. If things get too complex use REGEXP. Functions like TRANSLATE could give added flexibility too.

Performance or not, it definitely gives you a lot of room for performing quick and dirty searches.

Powered by ScribeFire.