Inserting data into Oracle 8 from Oracle 9 database - VARCHAR2 columns as null

Hello,

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
(product          varchar2(10),
 sample_id        varchar2(10),
 test_number      varchar2(10),
 lot_number       varchar2(10),
 location         varchar2(10),
 date_completed   date,
 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,
       t.date_completed date_completed,
       r.value density
  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
/

Open in new window

LVL 1
DwayneZandbergenAsked:
Who is Participating?
 
MikeOM_DBACommented:

NLS_LANG is local to the client from where you are connected.
 
0
 
MikeOM_DBACommented:
What are the NLS language settings?
Check against the NLS_LANG environment variable of client/server from where you are executing DML.
 
0
 
DwayneZandbergenAuthor Commented:
SELECT NLS_CHARSET_NAME(2) FROM DUAL; returns WE8DEC when connected to the oracle 8 database.

SELECT NLS_CHARSET_NAME(2) FROM DUAL; returns WE8DEC when connected to the oracle 9 database.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
MikeOM_DBACommented:

Confirm:
Check NLS_LANG environment variable of client/server from where you are executing DML (sqlplus).
This means: On WinDoze check the value of NLS_LANG key in the registry, on *nix do "echo $NLS_LANG"
 
0
 
DwayneZandbergenAuthor Commented:
I found several instances of NLS_LANG in the registry... some stated NA and some stated AMERICAN_AMERICA.WE8ISO8859P1
0
 
MikeOM_DBACommented:

HKEY_LOCAL_MACHINE> Software> Oracle Home> NLS_LANG

Don't know if it will work, but try setting it to:

AMERICAN_AMERICA.WE8DEC

0
 
DwayneZandbergenAuthor Commented:
Will that compromise any other oracle dependent applications that I may currently be running?
0
 
DwayneZandbergenAuthor Commented:
Sorry, I don't want to change my local NLS_LANG setting for fear of impacting other applications using the same config on my PC (I have many applications connecting to mulitple versions of various Oracle databases).  Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.