Martinicity

Rails on Oracle

Posted by Mike Blake Thu, 29 Mar 2007 21:15:00 GMT

When rails on jruby and connecting to Oracle via JDBC ,the following error eventually appears:

Update: java.sql.SQLException: Io exception: Broken pipe

Fix:

You need a dedicated connection from Oracle. Change the url: line in database.yml from url: jdbc:oracle:thin:@localhost:1521:XE

url: jdbc:oracle:thin:@(DESCRIPTION = (ADDRESSLIST = (ADDRESS =(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECTDATA =(SERVER = DEDICATED) (SID = XE)))

Workarounds

Duplicating an existing Oracle Database using the build in task rake db:test:clone presented a few problems. Here’s what I did to work around them.

ORA-01727: numeric precision specifier is out of range

OCIError: ORA-01727: numeric precision specifier is out of range (1 to 38): CREATE TABLE employee (id NUMBER(38) NOT NULL PRIMARY KEY, createddate DATE DEFAULT NULL, startdate DATE DEFAULT NULL, jobid DECIMAL DEFAULT NULL, totalhours NUMBER(126) DEFAULT NULL)

Fix:

For some reason whne rails dumps the schema, it reports Oracle type FLOAT as NUMBER(126), so you just need to changed that back to FLOAT if you want to import that schema.

OCIError: ORA-00972 identifier is too long:

OCIError: ORA-00972: identifier is too long: CREATE SEQUENCE gametime_responsibility_ref_seq START WITH 10000

Fix:

Rails tries to create sequences in Oracle to handle AUTOINCREMENT id fields. It uses TABLENAME = ’SEQ’ for the sequence name. If a sqequence name is too long then you have to shorten it in schema.rb .

OCIError: ORA-00907: missing right parenthesis

OCIError: ORA-00907: missing right parenthesis: CREATE TABLE board (id NUMBER(38) NOT NULL PRIMARY KEY, name VARCHAR2(150) NOT NULL, name VARCHAR2(150) NOT NULL, parent VARCHAR2(150) DEFAULT NULL, loglevel DECIMAL DEFAULT NULL, modifieddate DATE(6) DEFAULT NULL, token DECIMAL DEFAULT NULL)

Fix:

For some reason Rails assigns Date fields a size in schema.rb . You’ll need to change all occurances of DATE(6) to DATE .