• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

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

0
DwayneZandbergen
Asked:
DwayneZandbergen
  • 4
  • 4
1 Solution
 
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
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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
 
MikeOM_DBACommented:

NLS_LANG is local to the client from where you are connected.
 
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now