Thursday, 24 February 2011

CSV files in MS Excel and Oracle Open Office

My input file is created in MS Excel, it is 1 row of data with 3 columns (in the second column there is a newline character):

When I save this file as .csv in excel I get following structure:
³ó ?????,"2 äöü ??? lines
In 1 field ???",+123456

File is corrupted

When you open this file in Excel no multibytes and even lost "+" in phone number but the new line character properly inserted.

When I save the input Excel file as Unicode Text .txt the Unicode (MS Unicode ucs-2le) all the issues with .csv will be resolved (while saving).

łóś ещьул "2 äöü ыва lines
In 1 field ещь" +123456

Unfortunately while opening such file with Text Import Wizard:

In third step you can change type to Text (to rescue phone number):

After clicking "Finish" the result is dissapointing:

Multibytes and phone number is properly shown, but the newline is broken.

When you change the extension of the previous Unicode File from .txt to .csv and double click on it to open it with Excel you will have new line and multibytes but the phone will be corrupted:

When you try to open the same Unicode .csv file with Oracle Open Office:
File -> Open -> .csv file
In Import Wizard you set character set to Unicode (it's MS Unicode), delimiter to Tab and column types to Text (to rescue phone number):

Success story:

The question is:
"Why excel cannot properly open a CSV file created by itself?"

Or maybe there is a workaround (without modyfing the CSV file) to do so?

Oracle Open Office FTW!!!

Friday, 22 October 2010

validating emails in oracle db using java stored function => and testing from ruby

1. email validation function is written in java (flexibility) and compiled as stored procedure in oracle db (performance)

1a) write
import java.lang.String;
import java.util.regex.Pattern;
import java.util.regex.Matcher;

class EmailValidations {
public static int is_valid(String email) {
if (email == null || email == "") return 0;

String upper_email = email.toUpperCase();

Pattern p = Pattern.compile("^[\\w\\.-]*[^\\.]@([\\w\\-]+\\.)+[A-Z]{2,4}$");
Matcher m = p.matcher(upper_email);
return m.matches() ? 1: 0;

1b) load and compile java class into your oracle db:
$ loadjava -u scott/tiger@emeadb11 -v -resolve
--> replace scott/tiger@emeadb11 with your valid username/password@tnsname_entry

2a). create package that will contain your function
create or replace PACKAGE             "MISC_PKG"
FUNCTION is_email_valid (email_address in varchar) RETURN number;

2b). create package body:
create or replace

FUNCTION is_email_valid (email_address in varchar) RETURN number
NAME 'EmailValidations.is_valid(java.lang.String) return int';


3) test with ruby-plsql

3a) spec_helper.rb that will contain your credentials and include rspecs and ruby-plsql gems
require 'rubygems'
require 'rspec'
require 'ruby-plsql'

DB_USER = "scott"
DB_PWD = "tiger"
DB_SRVR = "emeadb11"

plsql.connection = DB_USER, DB_PWD, DB_SRVR
p "connected"

plsql.connection.autocommit = false

RSpec.configure do |config|
# config.filter_run :focus => true
config.after(:each) do

3b) write ruby specs spec_is_email_valid.rb
require File.dirname(__FILE__) + '/spec_helper'

describe "check is_email_valid function" do

it "should return 1 for valid emails" do
plsql.misc_pkg.is_email_valid("").should == 1
plsql.misc_pkg.is_email_valid("").should == 1

it "should return 0 for invalid emails" do
plsql.misc_pkg.is_email_valid("c123@o2,pl").should == 0
plsql.misc_pkg.is_email_valid("c@o2pl").should == 0

it "should return 0 as in email_err_reason" do
plsql.misc_pkg.is_email_valid("S.J@GMAIL").should == 0
plsql.misc_pkg.is_email_valid("SY.STORT").should == 0
plsql.misc_pkg.is_email_valid("01516081607").should == 0

it "should return 0 when invalid characters in it" do
plsql.misc_pkg.is_email_valid("J.AMS.@CATS.ORG").should == 0
plsql.misc_pkg.is_email_valid("STNJ@ISON.CO.ZA/JACO@VTR.CO.ZA").should == 0

it "should return 0 when null email" do
plsql.misc_pkg.is_email_valid("").should == 0
plsql.misc_pkg.is_email_valid(nil).should == 0

4) you can test it with:
$ rspec spec_is_email_valid.rb 

Finished in 5.64 seconds
5 examples, 0 failures

You can easily extend your tests once the change was done to your is_valid() java function.

For more information on testing pl/sql functions from ruby see excelent blog (author of ruby-plsl):

Sunday, 29 August 2010 domain with heroku rails app

1. assuming you have an app on heroku
2. change CNAME entry for DNS on
- Domains
- mark your and select DNS -> Einstellungen Bearbeiten
- for CNAME entry put:
- apply and wait couple of hours until it’s populated
3. in your heroku app you need to add an add-on “Custom Domains” (Basic is free)
$ heroku addons:add custom_domains
Adding custom_domains to ... FAILED
! Please verify your account before installing this addon
! For more information, see
! Verify now at

as you can see you need to verify your account before (credit card needed -> no charge applies)

when it’s done:
$ heroku addons:add custom_domains:basic
Adding custom_domains:basic to ... done

$ heroku domains:add
Added as a custom domain name to
$ heroku domains:add
Added as a custom domain name to

FULL instructions under:

Tuesday, 20 October 2009

oracle db - how to identify compilation errors

select * from user_errors;

SELECT RTRIM(INITCAP(e.type))||' '||||': Line '||
TO_CHAR(e.line)||' Column '||TO_CHAR(e.position) linecol,
s.text sourceline,RPAD(' ',(e.position - 1))||'^' errpos,
e.text error
FROM user_source s,user_errors e
WHERE e.type = s.type
AND e.line = s.line
ORDER BY,e.sequence

The latter one will give you the exact line where the error occcurs

Wednesday, 23 September 2009

encrypting partitions with LUKS in Linux

with these commands we encrypt a full partition located under /dev/sde1

In the below steps I use dcfldd (which is much more user friendly than dd and gives you progress status) and cryptsetup.

sudo apt-get install dcfldd cryptsetup

sudo dcfldd if=/dev/urandom of=/dev/sde1 statusinterval=10 bs=10M conv=notrunc
sudo cryptsetup luksFormat /dev/sde1
sudo cryptsetup luksOpen /dev/sde1 maxtor_encrypted
sudo mkfs.ext3 /dev/mapper/maxtor_encrypted
sudo mkdir /mnt/maxtor_enc
sudo mount /dev/mapper/maxtor_encrypted /mnt/maxtor_enc -t ext3 -o noatime,nodiratime

it is safer to use UUIDs instead of /dev/sde1 because even if order of the drives changes (USB drives, or because of your BIOS) you always can uniquely identify the one with the unique UUID.
You can get the UUIDs of all of your disks' partitions by running
$ blkid

Now let's write to scripts for mounting and umounting our encrypted drive:
chris@emeadb:~/bin$ cat mycryptmount
export PATH=/usr/local/bin/:/bin/:/usr/bin/
echo "mounting encrypted drive"

df | grep maxtor_enc && sudo /home/chris/bin/mycryptumount
echo "maxtor_enc umounted"

sudo cryptsetup luksOpen /dev/disk/by-uuid/7a89446f-c43f-41b1-bc2b-99c74c64cdeb maxtor_encrypted
sudo mount /dev/mapper/maxtor_encrypted /mnt/maxtor_enc -t ext3 -o noatime,nodiratime

chris@emeadb:~/bin$ cat mycryptumount
export PATH=/usr/local/bin/:/bin/:/usr/bin/

sudo umount /dev/mapper/maxtor_encrypted
sudo cryptsetup luksClose maxtor_encrypted

chmod +x both of them

now you are able to mount with
$ mycryptmount

and umount with
$ mycrypumount

Sunday, 13 September 2009

Thursday, 28 May 2009

ruby oracle oci8 UTF8 corruption

I have a small script oci1.rb:
require 'oci8' 
conn ="test","test","test_db")"out.txt","wb") do |out|
tabs = conn.exec('SELECT * from test_utf8') do |r|

that runs perfectly on windows producing from my test_db:

when I run exactly the same on my linux box:

I tested then a small script that reads and writes UTF8 on linux to be sure my linux terminal can display UTF8
(read_write_utf8.rb):"utf8_out.txt","wb") do |out|"utf8.txt","r").each do |line|

and that works good!
chris@emeadb:~/work/ruby/oci$ ruby read_write_utf8.rb 
chris@emeadb:~/work/ruby/oci$ cat utf8.txt
chris@emeadb:~/work/ruby/oci$ cat utf8_out.txt

Finally the issue was with my NLS_LANG variable that was not set in the Linux box on the account I used for running the script.

I tried first to set it up in the ruby script itself:

that did not work.

the solution was to set it in the shell.

you can set it in your .bashrc (depending on the shell you use) to make it default for the account.