Limit and offset in Oracle

If you are considering about using some kind of pagination like will_paginate or some pagination of your own flavor, and you are stuck using Oracle i have a solution for you. Oracle is known not to have limit and offset helpers for the select statements. Those two pieces of code that are missing are helping us to paginate properly. Yeas, you can use rownum, and i am using it too, but we have to encapsulate our select a few times, so it won’t make trouble for us(bad data, not ordering properly or something).

I have already made a patch for arel, so it’s embedded in Rails, if you really have use Oracle as your database.

Let’s say that we want to list all the employees from scott.emp table, and order them by empno descending and fetch the records from rows of that data 41 to 50.

SELECT * FROM (
  SELECT raw_sql_.*, rownum raw_rnum_
    FROM (select * from scott.emp order by empno DESC) raw_sql_ )
              WHERE raw_rnum_ between 41 and 50

Yes, i know it looks stupid, but it works, and until Oracle implements limit and offset into it, sadly, this is the only way.

Installing Rails Server on Ubuntu 12.04 with RVM, Nginx, Passenger and Oracle support

We have a new product ready and it is a Ruby on Rails webshop bundled with an ERP software written on Oracle Forms 6i. I will not go into the details of this work, maybe later i will make some posts of the process but as it is very linked to current software(only authentication is done with Devise, every other data and processing is done internally on Oracle database) there is no sense for me to explain it deeper. Off to the installation.

Server installation and configuration

First we need a clean install of Ubuntu server 12.04, x86 or x64 version will work all the same, but be careful later on when you download the Oracle instant client. On the installation, fill all the relevant data needed, and for installed services, choose only the ssh server, as you won’t be needing anything more. Another reminder: this will only be the webserver, assuming that you have the Oracle database installed somewhere in network reach.

After you login to your server for the first time, take your time to set up the ip address because it defaults to dhcp.
Edit the /etc/network/interfaces file and fill your address, gateway and dns servers(this is a new thing, i think starting in 12.04 /etc/resolv.conf gets updated whenever you restart networking, so everything you write there will get overwritten)


# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
auto eth0
iface eth0 inet static
    address 192.168.0.100
    netmask 255.255.255.0
    gateway 192.168.0.1
    dns-nameservers 8.8.8.8 8.8.4.4.

After the config is saved, restart the networking and do a full update/upgrade of the server to get the latest packages installed:


sudo /etc/init.d/networking restart
sudo apt-get update
sudo apt-get -y dist-upgrade

After all is done, reboot your server.

Installing RVM and latest Ruby version

After your server is updated you can proceed with installing RVM and the latest stable Ruby(as i’m writing this, the latest version is 1.9.3p194 so i will be using this version)

First we install RVM prerequisites:

# rvm requires curl and git to install, and you will probably need them on your server so go ahead and install them
apt-get -y install git-core curl

# Installing RVM as sudo, to get RVM ruby system wide
curl -L get.rvm.io | sudo bash -s stable

# sourcing rvm environment so you can use it untill you login again
source /etc/profile.d/rvm.sh

And that will install RVM and make it available for all users that belong to the rvm group(hint: edit /etc/groups and add rvm after the desired username). After installing RVM and adding our user to the  rvm group we are off to installing latest Ruby version

# install all requirements needed for YARV/MRI Ruby (you can easily get these by running 'rvm requirements' in the terminal)
sudo apt-get install build-essential openssl libreadline6 libreadline6-dev curl git-core zlib1g zlib1g-dev libssl-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt-dev autoconf libc6-dev ncurses-dev automake libtool bison subversion

# install requirements needed for Oracle Instant client
sudo apt-get install libaio1

# install latest stable 1.9.3 and set it as default ruby version
rvmsudo rvm install 1.9.3 --default

# disable installation of RDoc and RI when installing gems
echo 'gem: --no-ri --no-rdoc'  >> ~/.gemrc

# install bundler gem, as you will later need for deployment
rvmsudo gem install bundler

 Installing Passenger and Nginx web server

After you have ruby and everything needed installer go and install passenger gem and nginx web server with it

# install  passenger gem
rvmsudo gem install passenger

# install passenger nginx module (and nginx if you haven't installed it yet), choose first option(Yes: download, compile and install Nginx for me) when it asks you what to do
# if there are more requirements the script will tell you and after you install them rerun the command
rvmsudo passenger-install-nginx-module

The installation will set up the nginx.conf file for you and show you how you can enable a rails site on your server. But i will include a sample config later on.
Just for convenience, symlink nginx conf folder that is in /opt/nginx/conf to /etc/nginx and download linode nginx startup/shutdown script

# symlink nginx conf folder to /etc/nginx
sudo ln -s /opt/nginx/conf /etc/nginx

# download linode nginx init script and create the service
wget -O init-deb.sh http://library.linode.com/assets/660-init-deb.sh
sudo mv init-deb.sh /etc/init.d/nginx
sudo chmod +x /etc/init.d/nginx
sudo /usr/sbin/update-rc.d -f nginx defaults

Installing Oracle instant client

To connect your Rails application to Oracle database, you will have to install Oracle client, either full or instant one. As installing full client requires alien and converting .rpm packages to .deb it’s a bit of a hassle. Also, it will consume more space on your drive, and all that is unnecessary, we are trying to keep it small here.

First you should download zip files from Oracle site depending what architecture you are running:
Oracle Instant Client 11.2 for linux:  X86X64

Download only instantclient-basic, instantclient-sqlplus and instantclient-sdk zip archives and unzip them all into the same instantclient_11_2 folder. Now as sudo move that folder so the path is /opt/oracle/instantclient_11_2

After that you only have to add two environment variables to be able to install ruby-oci8 gem, which is prerequisite for running Ruby applications on Oracle database. Add the following lines to /etc/enviroment file

LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2

# as i am from Croatia, and we use our national date and money preferences, my NLS_LANG is this one, yours will maybe be different
NLS_LANG=CROATIAN_CROATIA.AL32UTF8

After you reboot you have to symlink the oracle library so it will function properly(i don’t know why they don’t do this themselves).

ln -s /opt/oracle/instantclient_11_2/libclntsh.so.11.1 /opt/oracle/instantclient_11_2/libclntsh.so

After that try and install ruby-oci8 gem.

rvmsudo gem install ruby-oci8

If there are no errors, and there should not be if you followed the guide, you are set to deploy your application on the server you just installed. In the next post, i plan to write something about tips, tricks and caveats of developing Rails applications on Oracle database.

Useful links to get and learn about all the stuff i have mentioned in the post:

Ruby Version Manager(RVM): https://rvm.io
Ruby: http://ruby-lang.org
Oracle Instant Client 11.2 for linux:  X86, X64
Passenger server: http://www.modrails.com/

Speaking at a conference with no time to prepare

I was surprised to know that i am presenting a product this week at the oracle user conference HROUG.hr

A colleague and me were supposed to hold a talk about creating our latest web app, a nursery management app created using Ruby on Rails and Oracle XE. As we thought, the talk was canceled and we were free of all obligation. But no, this saturday, 5 days before the talk, i was reviewing the agenda and found out the talk is on. So what can i do?

I will walk you through the steps, though they shouldn’t be used unless you are in a crunch, and it is not longer than an hour, luckily my talk is only half an hour.

1. Gather as much irrelevant info about the company and the client(our somewhat of a manager sorted that out)

2. If you are the developer, try to talk as much as you can about the technology and its integration( I love Rails so that is no problem )

3. Scrape out the basic workflow of the application you are presenting, user experience and why is something done in that way.

4. Take as much screenshots as it takes

5. Talk about details in the application, and finally leave at least 5 minutes for the audience to ask questions.

I managed to cramp all that into 30 minutes, i will update this post after i’m done thursday morning, to write how the talk went.

Lesson for everyone: Know your talking schedule at least one month before you are doing a talk, this way you can prepare and practice.

Update: The talk went surprisingly well, the hall was almost full, with 24 people listening. I haven’t got my reviews yet, but i feel they will be great for my first conference talk.

Moving oracle tables with lob columns to another tablespace

I have encountered a problem with oracle database that it does not reuse lob data. So, if you have 5000 rows of some lob data, and you delete them from the table, then insert the same 5000 rows, you will have 10000 rows of consumed space. That could be done easily with shrinking the table but the default tablespace segment space management (SSM) on oracle 10g is manual, so it does not allow row movement and table shrinking.

First, you create a tablespace with ASSM, that is automatic segment space management:

CREATE TABLESPACE NEW_TABLESPACE DATAFILE
'/home/oracle/product/10.2.0/db_1/dbs/new_tablespace.dbf' SIZE 16109M AUTOEXTEND ON NEXT
1000M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Now we can proceed with table movement and after that lob_segment movement. There are some steps we should follow:

  1. Move table
  2. Rebuild indexes (and move them to the new tablespace if you wish so)
  3. Move the lob segment
  4. Enable row movement
  5. Shrink the table and lob segment

Here is the sql needed, of course, you will need to change and/or add indexes that reside on the current table.

--moving the table to the new tablespace
alter table table_name move tablespace new_tablespace;

--rebuilding the index(es) and moving them to the new tablespace
alter index index_name rebuild tablespace new_tablespace;

--moving the lobsegment(s) to the new tablespace
alter table table_name move lob (lob_column) store as
(tablespace new_tablespace);

--shrinking the table and lobsegment
alter table table_name enable row movement;
alter table table_name shrink space cascade;
alter table table_name modify lob (lob_column) (shrink space);

Table and the lob segment will shrink during migration, but just in case you need further space shrinkage i have included the scripts needed.

Datediff function in Oracle

As i am working on Oracle database at my day job, i have found something that is by default on sqlserver but not in oracle. That is the datediff function that given the input of two dates and an identifier like day, month or year returns integer with the requested number.

So to cut the long story short, i have written my own datediff function for oracle use. You can request more return types in the comments, and i will be glad to add them. Here is the code:

create or replace function datediff(v_type VARCHAR2, date_from DATE, date_to DATE) return integer as
v_diff INTEGER;
begin
v_diff :=
  case upper(v_type)
    when 'MONTH' then
      months_between(date_to, date_from)
    when 'DAY' then
      date_to - date_from
    when 'YEAR' then
      trunc(months_between(date_to, date_from)/12)
    when 'HH' then
    (date_to - date_from) * 24
    when 'MM' then
    (date_to - date_from) * 24 * 60
    when 'SS' then
    (date_to - date_from) * 24 * 60 * 60
  end;
return abs(v_diff);
end;

As you can see, the type parameters are: MONTH for difference in months, DAY for difference in days and YEAR for difference in years respectively.

I hope oracle will see the light and include this function in any of the future releases.

UPDATE: As my friend LILkillaBEE has mentioned that he could be in a need of hour/minute/second difference, i have added that too, so, this should be the final implementation.

New parameters are HH for hour, MM for minute and SS for seconds difference.

Creating ASP.NET MVC application against Oracle database pt. 1

This is a first in a series of posts, as i will try to build ASP.NET MVC application against Oracle XE database, but this example can be used for any Oracle database greater than 9i.

I will try to use Oracle ODAC.net the latest version that i can get from the Oracle Site.

With a little help from LILkillaBEE i created generic helpers that return IEnumerable<T> on which i can use LINQ and play with it.

First thing first, as i am creating a Contact manager application part by part, the first functionality i want to implement is getting the data from the Oracle XE database and working with it in MVC way. So i have created the table with personal data only in it.

CREATE TABLE  PEOPLE
   (
    PERSON_ID NUMBER NOT NULL ENABLE,
    NAME VARCHAR2(100) NOT NULL  ENABLE,
    SURNAME VARCHAR2(100) NOT NULL ENABLE,
    ADDRESS_LINE1  VARCHAR2(100) NOT  NULL ENABLE,
    ADDRESS_LINE2 VARCHAR2(100),
    ZIP_CODE  VARCHAR2(100) NOT  NULL ENABLE,
    CITY VARCHAR2(100) NOT NULL  ENABLE,
    COUNTRY VARCHAR2(100) NOT NULL ENABLE,
     CONSTRAINT PEOPLE_PK PRIMARY KEY  (PERSON_ID) ENABLE
   );

CREATE OR REPLACE TRIGGER  BI_PEOPLE
  before insert on PEOPLE
  for  each row
begin
    select PEOPLE_SEQ.nextval into  :NEW.PERSON_ID from  dual;
end;

ALTER TRIGGER  BI_PEOPLE ENABLE;

After this, i have created the Person class in my project

public class Person
{
    public virtual int PersonID { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual string AddressLine1 { get; set; }
    public virtual string AddressLine2 { get; set; }
    public virtual string ZipCode { get; set; }
    public virtual string City { get; set; }
    public virtual string Country { get; set; }
}

And an interface that i will implement later in my repository

interface IPersonInterface
{
    IEnumerable&lt;Person&gt; GetAll();
    Person GetById(int id);
    void Add(Person person);
    void Delete(int id);
}

EDIT: After considering to finish this tutorial i finally gave up because there is no easy way of doing this and i kept hitting the wall a few times.