错误00932,获取OCIError：ORA-00932：不一致的数据类型从Rails 3的

Here is the scenario.

I'm writing my geo-ruby oracle adapter for Ruby On Rails which supports working with SDO_GEOMETRY out of box.

It was going well. I wrote codes for selecting SDO_GEOMETRY objects from Oracle DB successfully.

Everything ruins up when I wanted to write insert and update parts.

Following is what's in my mind. I want to be able to do this statement:

g = GeoShape.new(name:"point1", shape: Point.from_x_y(-34,-43,4326))

g.save

I generated following sql query from the above statements:

INSERT INTO "GEO_SHAPES" ("CREATED_AT", "ID", "NAME", "SHAPE", "UPDATED_AT") VALUES (:a1, :a2, :a3, :a4, :a5) [["created_at", Tue, 03 Jul 2012 08:42:01 UTC +00:00], ["id", 10112], ["name", "point1"], ["shape", "SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(-34,-43))"], ["updated_at", Tue, 03 Jul 2012 08:42:01 UTC +00:00]]

But execution of above query gave me this error:

ActiveRecord::StatementInvalid: OCIError: ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CHAR

I got into oracle_enhanced_adapter to trace the problem. I tried to monkey patch it and manually initialize binds[3][1] (which is the value of sdo_geometry column in my DB) as follows:

def exec_insert(sql, name, binds)

log(sql, name, binds) do

returning_id_index = nil

cursor = if @statements.key?(sql)

@statements[sql]

else

@statements[sql] = @connection.prepare(sql)

end

binds[3][1] = "SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(-34,-43))" ### DAVE

binds.each_with_index do |bind, i|

col, val = bind

if col == :returning_id

returning_id_index = i + 1

cursor.bind_returning_param(returning_id_index, Integer)

else

if val == "SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(-34,-43))" ### DAVE

cursor.bind_param(i + 1, val, OCI8::Object::Mdsys::SdoGeometry) ###DAVE

else ### DAVE

cursor.bind_param(i + 1, type_cast(val, col), col && col.type)

end

end

end

cursor.exec_update

Unfortunately, it didn't help. I still got the same error ORA-00932.

Any ideas? It's so critical for me to fix this.

P.S: ###DAVE parts are my monkey patches of oracle_enhanced_adapter.rb

P.S: Here is my config.

Oracle 11.2

Ruby version 1.9.3 (i386-darwin11.3.0)

Rails version 3.2.5

Active Record version 3.2.5

解决方案

Finally, I managed to fix the problem.

Enhanced adapter uses bind_param method to perform binding variables to their appropriate oracle types. Here was the problem and error.

I had to override some codes in oracle_enhanced_adpter.rb.

I add a method to this file to manually build a SDO_GEOMETRY object.

Then I bind this returned object to OCI8::Object::Mdsys::SdoGeometry type.

My method contains these codes (Now, only for creating SDO_GEOMETRY Points):

#I needed a connection to my oracle database. Connection placeholder is conn.

def create_sdo_geometry_object(conn, gtype, srid, point, x, y)

local_cursor = conn.parse("BEGIN :geom := SDO_GEOMETRY(:sdo_gtype, :sdo_srid, :sdo_point, :sdo_elem_info_array, :sdo_ordinate_array); END;")

local_cursor.bind_param(:sdo_gtype, OraNumber)

local_cursor.bind_param(:sdo_srid, OraNumber)

local_cursor.bind_param(:sdo_point, OCI8::Object::Mdsys::SdoPointType)

local_cursor.bind_param(:sdo_elem_info_array, OCI8::Object::Mdsys::SdoElemInfoArray)

local_cursor.bind_param(:sdo_ordinate_array, OCI8::Object::Mdsys::SdoOrdinateArray)

local_cursor.bind_param(:geom, OCI8::Object::Mdsys::SdoGeometry)

sdo_gtype = OraNumber.new(gtype)

sdo_srid = OraNumber.new(srid)

sdo_point = nil #Temporarily I set it to nil

#sdo_elem_info_array must be [1,1,1] for creating points.

sdo_elem_info_array = OCI8::Object::Mdsys::SdoElemInfoArray.new(conn,OraNumber.new(1),OraNumber.new(1),OraNumber.new(1))

#I want to create a point with x and y coordinates

sdo_ordinate_array = OCI8::Object::Mdsys::SdoOrdinateArray.new(conn,OraNumber.new(x),OraNumber.new(y))

local_cursor[:sdo_gtype] = sdo_gtype

local_cursor[:sdo_srid] = sdo_srid

local_cursor[:sdo_point] = sdo_point

local_cursor[:sdo_elem_info_array] = sdo_elem_info_array

local_cursor[:sdo_ordinate_array] = sdo_ordinate_array

local_cursor.exec

local_cursor[:geom]

end

Then when I wanted to bind this parameter I used the following code:

cursor.bind_param(i + 1, create_sdo_geometry_object("PARAMS HERE"), OCI8::Object::Mdsys::SdoGeometry)

It worked like a charm!