I'm inserting some records into a table on an Oracle 8 database for reporting purposes. The funny thing is, that if I run the query from Oracle 8 against the Oracle 9 database (where the source tables reside), I see all the data correctly.
When I run the insert statement, it inserts all column data of type varchar2 as a null value. I can only assume this is something to do with the NLS language differences. Does anyone have a way around this issue?
create table report_product_densities
density number )
insert into report_product_densities
select rtrim(s.product) as product,
rtrim(s.id_numeric) as sample_id,
rtrim(t.test_number) as test_number,
rtrim(s.batch_name) as lot_number,
rtrim(s.sampling_point) as location,
from lims_sample s, lims_test t, lims_result r
where s.id_numeric = t.sample
and t.test_number = r.test_number
and (s.status = 'C' or s.status='A')
and t.analysis = '4'
and (r.value > 0 and r.value < 1)
and s.sampling_point like 'TK%'
and t.date_completed > sysdate-365