Since I’ve been working most of my time with iPhone Dev for the last couple of months, I thought it will be worthwile to post how to import files to SQLite3. In a nutshell, use MesaSQLite’s IMPORT function. It will save you a lot of time. Now it is up to you how to export into a CSV or TAB delimited file. For Oracle, I used SQLDeveloper. For other databases, such as MySQL or Postgres, I’ve used Navicat. Read more…
I’ve been toggling back and forth two different oracle homes by editing the environment variables from the Control Panel in Windows which is a pain in the ***. What I did was, to create two different command prompt shells with different oracle home environments. I got this idea after a glimpse from FWTools.
1. Create a Command Prompt Shortcut and drag it to your oracle directory, i.e, E:\oracle\Oracle Shell Local
2. Edit the target as:
C:\WINDOWS\system32\cmd.exe /K "E:\oracle\setlocal.bat"
3. Create “setlocal.bat”
@echo off
SET ORACLE_HOME=E:\oracle\product\10.2.0\db_2
set PATH=E:\oracle\product\10.2.0\db_2\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem
Do the same for your remote oracle but with a different bat file.
1. Repeat steps 1 and 2 but name it “Oracle Shell Remote” and “E:\oracle\setremote.bat”
2. Create “setremote.bat”
@echo off
SET ORACLE_HOME=E:\oracle\product\10.2.0\client_1
set PATH=E:\oracle\product\10.2.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem
Note: This assumes that you have installed an Oracle Client on E:\oracle\product\10.2.0\client_1
Ok, now let’s test. I know that I have different record count for a table in my local and remote
1. Fire up “Oracle Shell Local”. Run sqlplus
>sqlplus username/password@instance_name_defined_in_local_tnsnames.ora
Note: E:\oracle\product\10.2.0\db_2\NETWORK\ADMIN\tnsnames.ora
2. Fire u p “Oracle Shell Remote”. Run sqlplus
>sqlplus username/password@instance_name_defined_in_remote_tnsnames.ora
Note: E:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
So what? Well its very useful when doing export and imports. Say I want to export a table from my local and import it to my remote oracle. I’ll just fire up two shells, issue an export in my “Oracle Shell Local” and run an import command in my “Oracle Shell Remote”
In summary, install Oracle Instant Client and try to run sqlplus. If sqlplus connects to the oracle sid then go ahead and install the rails adapters for oracle. What is important to note here, is to install the oracle-instantclient for the architecture of your machine.. I have tested this on Debian Lenny (i386) and CentOS5 (x64)
1. Download from http://www.oracle.com/technology/software/tech/oci/instantclient/
a. oracle-instantclient-basic-10.2.0.4-1.i386
b. oracle-instantclient-devel-10.2.0.4-1.i386
c. oracle-instantclient-sqlplus-10.2.0.4-1.i386
2. Unzip everything to /opt/oracle/instantclient . You should have something like the ff:
[root@csapp1 instantclient]# ls -la
total 102704
drwxr-xr-x 3 root root 4096 Dec 10 21:54 .
drwxr-xr-x 3 root root 4096 Dec 10 22:03 ..
-rw-r--r-- 1 root root 228 Dec 10 21:52 BASIC_README
-r--r--r-- 1 root root 1609607 Dec 10 21:52 classes12.jar
-rwxr-xr-x 1 root root 67542 Dec 10 21:52 genezi
-r--r--r-- 1 root root 1525 Dec 10 21:52 glogin.sql
lrwxrwxrwx 1 root root 17 Dec 10 21:54 libclntsh.so -> libclntsh.so.10.1
-rwxr-xr-x 1 root root 21038613 Dec 10 21:52 libclntsh.so.10.1
-r-xr-xr-x 1 root root 3796601 Dec 10 21:52 libnnz10.so
-rwxr-xr-x 1 root root 1664116 Dec 10 21:52 libocci.so.10.1
-rwxr-xr-x 1 root root 72674185 Dec 10 21:52 libociei.so
-r-xr-xr-x 1 root root 138033 Dec 10 21:52 libocijdbc10.so
-r-xr-xr-x 1 root root 1435561 Dec 10 21:52 libsqlplusic.so
-r-xr-xr-x 1 root root 997409 Dec 10 21:52 libsqlplus.so
-r--r--r-- 1 root root 1555682 Dec 10 21:52 ojdbc14.jar
drwxr-xr-x 4 root root 4096 Dec 10 21:52 sdk
-r-xr-xr-x 1 root root 7773 Dec 10 21:52 sqlplus
-rw-r--r-- 1 root root 232 Dec 10 21:52 SQLPLUS_README
-rw-r--r-- 1 root root 516 Dec 10 21:53 tnsnames.ora
[root@csapp1 instantclient]#
3. Make a symbolic link for libclntsh.so.10.1 as shown above.
4. Create the Oracle Environment variables
export ORACLE_HOME=/opt/oracle/instantclient
export TNS_ADMIN=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export DYLD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME
5. At this point, you should have oracle-instantclient properly installed. You can test by running sqlplus.
[root@csapp1 instantclient]# sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 11 11:47:40 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
NOTE: Sometimes you will get a SEGMENTATION FAULT. If so, try to open a new shell with the environment variables loaded and do an sqlplus in a directory which is not /opt/oracle/instantclient.
6. Install the oracle adapter for rails
7. gem install ruby-oci8
8. gem install oracle_enhanced-adapter –source=”http://gems.rubyonrails.org/”
activerecord-oracle-adapter (1.0.0.9250)
activerecord-oracle_enhanced-adapter (1.1.8)
NOTE: Try to look for the latest gems, the source above is at the time of this writing so it might change.
9. Test using irb
[root@csapp1 instantclient]# irb
irb(main):001:0> require 'rubygems'
=> true
irb(main):002:0> require 'oci8'
=> true
irb(main):003:0>
1. Read http://www.foliosus.com/2008/05/05/connecting-ruby-on-rails-to-oracle-on-an-intel-mac-in-leopard-take-2/
2. Install Oracle Instant Client on Mac.
a. Instant Client Package – Basic: All files required to run OCI, OCCI, and JDBC-OCI applications
- instantclient-basic-macosx-10.2.0.4.0.zip (34,020,719 bytes)
b. *Instant Client Package – SDK: Additional header files and an example makefile for developing Oracle applications with Instant Client
instantclient-sdk-macosx-10.2.0.4.0.zip (603,493 bytes)
OR download the whole bundle (10.2.0.4.zip) with sqlplus installed from my installers.
3. Put this on your sudo vim ~/.bash_profile.
export ORACLE_HOME=/Library/Oracle/instantclient/10.2.0.4
export TNS_ADMIN=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME
export DYLD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME
4. Make a symbolic link
cd /Library/Oracle/instantclient/10.2.0.4
ln -s libclntsh.dylib.10.1 libclntsh.dylib
5. Go to /Library/Oracle/instantclient/10.2.0.4 and edit tnsnames.ora. Point the Oracle SID to the IP where you installed Oracle.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.155)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL_2_11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)6. Install the oracle-adapter for rails
sudo gem install activerecord-oracle-adapter --source http://gems.rubyonrails.org
7. In your database.yml file
development:
adapter: oracle
database: orcl
username: youzhu_mobile_dev
password: your_password
or browse the contents of a sample rails project youzhumobile.tar.gz
8. If you ever encounter an encoding problem, then we need to set the NLS_LANG environment variable before running script/server.
# export NLS_LANG=American_America.UTF8
# script/server
or I prefer setting it in the environment.rb
Rails::Initializer.run do |config|
ENV['NLS_LANG']='American_America.UTF8'
# Settings in config/environments/* take precedence over those specified here.
Note: If you don’t know your database encoding, then read this post.
This is the first time I ever did a screencast and it was a lot of fun! Please do forgive me as my voice was horrible. I used iShowU and keyCaster. Google it!
I hope you guys enjoyed it..as much as I did..
oracle_spatial_1.mov
oracle_spatial_2.mov