a non-numeric character was found - error

[Shoaib]

Hi all, I am a bit stuck and would appreciate any pointers in the
right direction. I have a legacy (oracle spatial) database, and when I
setup a simple AR model i am getting an unsual error - see trace at
the end.

DB:
ActiveRecord::Base.establish_connection(
:adapter => 'oracle_enhanced',
:database => db_config["database"],
:username => db_config["username"],
:password => db_config["password"]
)

Model:
class Sample < ActiveRecord::Base
set_primary_key "sampleno"
end

Query:
s = Sample.find_by_sampleid('GA2476/006DR002B')
s.activity_code
=> "I"
s.activity_code = "A"
=> "I"
s.save

... BOOM!

As u can see from the trace I am running
- OracleEnanced 1.2.2
- AR 2.3.4


ActiveRecord::StatementInvalid: OCIError: ORA-01858: a non-numeric
character was found where a numeric was expected: UPDATE samples SET
activity_code = 'A' WHERE sampleno = 1978952

...activerecord-2.3.4/lib/active_record/connection_adapters/
abstract_adapter.rb:220:in `log'
...activerecord-oracle_enhanced-adapter-1.2.2/lib/
active_record/connection_adapters/oracle_enhanced_adapter.rb:1305:in
`log'
...activerecord-oracle_enhanced-adapter-1.2.2/lib/
active_record/connection_adapters/oracle_enhanced_adapter.rb:547:in
`execute'
...activerecord-2.3.4/lib/active_record/connection_adapters/
abstract/database_statements.rb:265:in `update_sql'
...activerecord-2.3.4/lib/active_record/connection_adapters/
abstract/database_statements.rb:49:in `update_without_query_dirty'
...activerecord-2.3.4/lib/active_record/connection_adapters/
abstract/query_cache.rb:18:in `update'
...activerecord-2.3.4/lib/active_record/base.rb:2876:in
`update_without_lock'
...activerecord-2.3.4/lib/active_record/locking/optimistic.rb:
81:in `update_without_dirty'
...activerecord-2.3.4/lib/active_record/dirty.rb:146:in
`update_without_timestamps'
...activerecord-2.3.4/lib/active_record/timestamp.rb:64:in
`update_without_callbacks'
...activerecord-2.3.4/lib/active_record/callbacks.rb:282:in
`update'
...activerecord-2.3.4/lib/active_record/base.rb:2867:in
`create_or_update_without_callbacks'
...activerecord-2.3.4/lib/active_record/callbacks.rb:250:in
`create_or_update'
...activerecord-2.3.4/lib/active_record/base.rb:2538:in
`save_without_validation'
...activerecord-2.3.4/lib/active_record/validations.rb:1078:in
`save_without_dirty'
...activerecord-2.3.4/lib/active_record/dirty.rb:79:in
`save_without_transactions'
...activerecord-2.3.4/lib/active_record/transactions.rb:229:in
`send'
...activerecord-2.3.4/lib/active_record/transactions.rb:229:in
`with_transaction_returning_status'
...activerecord-2.3.4/lib/active_record/connection_adapters/
abstract/database_statements.rb:136:in `transaction'
...activerecord-2.3.4/lib/active_record/transactions.rb:182:in
`transaction'
...activerecord-2.3.4/lib/active_record/transactions.rb:228:in
`with_transaction_returning_status'
...activerecord-2.3.4/lib/active_record/transactions.rb:196:in
`save'
...activerecord-2.3.4/lib/active_record/transactions.rb:208:in
`rollback_active_record_state!'
...activerecord-2.3.4/lib/active_record/transactions.rb:196:in
`save'
from (irb):7
from (null):0irb(main):008:0>

[Raimonds Simanovskis]

Can you show "samples" table definition? What is result of
DESC samples
?

I'm wondering if "sampleno" is NUMBER column or not.

Raimonds

[Shoaib]

Thanks Ray

here it is:

name null? type
--------------------
sampleno not null number(10)
eno not null number(8)
sampleid varchar2(30)
sample_type not null varchar2(40)
top_depth number(8,3)
base_depth number(8,3)
parent number(10)
access_code not null varchar2(1)
entrydate not null date
enteredby not null varchar2(30)
lastupdate date
updatedby varchar2(30)
qadate date
qaby varchar2(30)
qa_status_code not null varchar2(1)
activity_code not null varchar2(1)
originator varchar2(30)
acquiredate date
ano not null number(10)
geom mdsys.sdo_geometry
comments varchar2(2000)
source varchar2(100)
procedureno number(10)
origno number(6)
confid_until date
geom_original mdsys.sdo_geometry
accuracy number(6)
elev_accuracy number(7)
acquisition_methodno number(3)
acquisition_scale varchar2(30)
method varchar2(40)
countryid varchar2(3)
stateid varchar2(4)
onshore_flag char(5)
prov_eno number(8)


On Nov 15, 9:41 am, Raimonds Simanovskis

[Raimonds Simanovskis]

Hmm, strange...
Don't you have any database triggers on this table which try to do
something during this update statement?

Can you try to do

UPDATE samples SET activity_code = 'A' WHERE sampleno = 1978952

from sqlplus or SQL Developer?

Raimonds

[Shoaib]

Thx Ray

In iSQL it works and we have a few triggers like:
lastupdated
updatedby
etc
Could it b related to Number(10) ?

On Nov 19, 11:47 pm, Raimonds Simanovskis

[Shoaib]

Yea quite a few triggers...

e.g. one of them is an audit trigger - any thing stand out?:

CREATE OR REPLACE TRIGGER A.audit_samples
after update or delete ON A.SAMPLES for each row
declare
v_action_type varchar2(10);
v_table_pk number(8);
v_access_code varchar2(1);
v_ano number(10);
begin
if updating
then v_action_type := 'U';
v_table_pk := :old.sampleno;
v_access_code := :old.access_code;
v_ano := :old.ano;
else v_action_type := 'D';
v_table_pk := :old.sampleno;
v_access_code := :old.access_code;
v_ano := :old.ano;
end if;

On Nov 19, 11:47 pm, Raimonds Simanovskis
<raimonds.simanovs...@gmail.com> wrote:

[Shoaib]

ok search for all the triggers they are

SELECT trigger_name
FROM all_triggers
WHERE owner = 'A'
AND table_name = 'SAMPLES'
AND status = 'ENABLED';

=>

TRIGGER_NAME
--------------------------
AUDIT_SAMPLES_GEOM
AUDIT_SAMPLES
PRE_U_GEOM_SAMPLES
PRE_I_SAMPLES
PRE_U_SAMPLES

The Pre_U_SAMPLES is the preupdate

its pretty complicated but here goes the past


CREATE OR REPLACE TRIGGER A.PRE_U_SAMPLES
BEFORE UPDATE
ON A.SAMPLES REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
vXYround integer;
vZround integer;
vAccuracyValue number;
begin
if :new.sample_type is not null and Lookupexists('A', 'SAMPLES',
'SAMPLE_TYPE',:new.sample_type) != 1 then
Raise_application_error(-20000,'SAMPLE TYPE '||:new.sample_type||'
not found in LOOKUPS.');
end if;
:new.updatedby := user;
:new.lastupdate := sysdate;
-- Keep original enteredby and entrydate
:new.enteredby := :old.enteredby;
:new.entrydate := :old.entrydate;
if (:new.qa_status_code != :old.qa_status_code) then
if (:new.qaby is null) then
:new.qaby := user;
end if;
if (:new.qadate is null) then
:new.qadate := sysdate;
end if;
end if;
if ((:new.access_code = 'C' or :new.access_code = 'A')
and :new.confid_until is null) then
-- raise_application_error(-20004,'Confidentiality date must be
entered for access code '||:new.access_code);
:new.confid_until := to_date('01-JAN-1950', 'DD-MON-YYYY');
end if;
if (:new.geom_original is not null) then
-- Convert geom_original to 3D
:new.geom_original := a.convert2d_to_3d(:new.geom_original);
if :new.geom_original is not null then
if :new.geom_original.sdo_srid in (8307, 8311) then
:new.geom := :new.geom_original;
:new.geom.sdo_srid := 8311;
-- Transform geom_original to 8311 (GDA94)
else
:new.geom := sdo_cs.transform(:new.geom_original, 8311);
end if;
-- Find the X and Y rounding factor based on accuracy value
[default 8]
vAccuracyValue := nvl(nvl(:new.accuracy, :old.accuracy), 0);
if vAccuracyValue >= 1 then
vXYround := 6 + length(trunc(vAccuracyValue));
elsif vAccuracyValue > 0 then
vXYround := 4 + length(vAccuracyValue);
else
vXYround := 8;
end if;
-- Find the Z rounding factor based on elev_accuracy value
[default 3]
vAccuracyValue := nvl(nvl
(:new.elev_accuracy, :old.elev_accuracy), 0);
if vAccuracyValue >= 1 then
vZround := 1 - length(trunc(vAccuracyValue));
elsif vAccuracyValue > 0 then
vZround := length(vAccuracyValue) - 1;
else
vZround := 3;
end if;
-- Point geometry
if :new.geom.get_gtype() = 1 then
:new.geom.sdo_point.x := round(:new.geom.sdo_point.x,
vXYround);
:new.geom.sdo_point.y := round(:new.geom.sdo_point.y,
vXYround);
:new.geom.sdo_point.z := round(:new.geom.sdo_point.z, vZround);
-- Line, polygon, etc geometry
else
for rec in 1..:new.geom.sdo_ordinates.last loop
if mod(rec, 3) > 0 then
:new.geom.sdo_ordinates(rec) := round
(:new.geom.sdo_ordinates(rec), vXYround);
else
:new.geom.sdo_ordinates(rec) := round
(:new.geom.sdo_ordinates(rec), vZround);
end if;
end loop;
end if;
end if;
else
if (:new.geom is not null
or :new.acquisition_scale is not null
or :new.acquisition_methodno is not null
or :new.accuracy is not null or :new.elev_accuracy is not null)
then
raise_application_error(-20003,'Original Geometry is Null!
Standard Geometry, Accuracy, Elev_Accuracy, Acquisition_Scale and
Acquisition_Methodno cannot be populated.');
end if;
:new.geom := Null;
:new.acquisition_scale := null;
:new.acquisition_methodno :=null;
:new.accuracy := null;
:new.elev_accuracy := null;
end if;
exception when others then
raise_application_error(-20990,'TRIGGER PRE_U_SAMPLES ERROR '||
sqlerrm);
end;

[Raimonds Simanovskis]

Can't notice anything suspicious in these triggers.

But I've got one idea that maybe some NLS session settings are
different when you connect to database using Rails compared to when
you connect to database in normal way. As a result probably some type
conversion functions like TO_DATE might fail if they expect some NLS
specific data format. Do you set the same values for NLS_LANG (and
maybe some other) environment variables when you connect from Rails to
database? (And you need to set these environment variables before
starting Ruby as Oracle Instant Client will not see environment
variables that you set just in Ruby)

And also you can try to test it from script/console using
ActiveRecord::Base.connection.execute "UPDATE samples SET

activity_code = 'A' WHERE sampleno = 1978952"

This will bypass any ActiveRecord type conversions.

If you can then try to disable triggers one by one to find out which
could cause the issue (and maybe start with disabling all triggers to
find out if triggers are causing the issue).

Raimonds

[Shoaib]

Ray you are the man! This fixed it for me :-)
ProdDb.connection.execute("ALTER SESSION set NLS_DATE_FORMAT ='DD-MON-
FXYYYY'")



On Nov 22, 5:30 am, Raimonds Simanovskis

[Shoaib]

in case anyone is curious about the context of this use

http://gist.github.com/224762