invalid data inserted [message #396740] |
Tue, 07 April 2009 22:37  |
oracle_coorgi
Messages: 188 Registered: September 2006 Location: INDIA-karnataka
|
Senior Member |
|
|
Hi
how to load / insert data of special charater as it is
My nls parameter is
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
My sample insert data through insert.sql script is
alter session set nls_comp = ANSI;
alter session set nls_sort = GENERIC_BASELETTER;
INSERT INTO table1
(USER_name, FIRST_NAME, LAST_NAME,mail)
VALUES
('mike.Öak', 'mike', 'Öak', 'mike.Öak@test1.com');
INSERT INTO table1
(USER_name, FIRST_NAME, LAST_NAME)
VALUES
('Chirs.Ning', 'Chirs', 'Niñg');
commit;
out put of the query is
select USER_name from table1 where first_name='mike';
USER_name
------------------------
mike.Vzak
select last_name from fas_user where first_name='Chirs';
LAST_NAME
--------------------
Niqg
Here in place of mike.Öak its mike.Vzak loaded and for Niñg its
Niqg
i googled and i searched ine the forum but unable to get clear.
please guide me where i am going wrong. please.....
Thnx in advance
|
|
|
|
Re: invalid data inserted [message #396772 is a reply to message #396741] |
Wed, 08 April 2009 00:53   |
oracle_coorgi
Messages: 188 Registered: September 2006 Location: INDIA-karnataka
|
Senior Member |
|
|
hi
we have many .sql files , which loads data in DB is there any other way we can insert the data .we can not use any function .
cant we do anything in the script itself like
alter session set nls_comp = ANSI;
alter session set nls_sort = GENERIC_BASELETTER;
or
setting the os env..
this issue is becoming ....... from the client
pls look into this
thanx
|
|
|
|
Re: invalid data inserted [message #396969 is a reply to message #396774] |
Wed, 08 April 2009 09:19   |
oracle_coorgi
Messages: 188 Registered: September 2006 Location: INDIA-karnataka
|
Senior Member |
|
|
hi
SELECT 'Ö ñ í' FROM DUAL;
output is
'???'
-----------
? ? ?
SELECT dump('Ö ñ í') from dual;
DUMP('???')
--------------------------------------------------------
Typ=96 Len=11: 239,191,189,32,239,191,189,32,239,191,189
iam really need of help pls some guide me
thx
|
|
|
|
Re: invalid data inserted [message #396996 is a reply to message #396973] |
Wed, 08 April 2009 10:19   |
oracle_coorgi
Messages: 188 Registered: September 2006 Location: INDIA-karnataka
|
Senior Member |
|
|
hi
CREATE TABLE test_user
(
user_name VARCHAR2(90),
FIRST_NAME VARCHAR2(40),
LAST_NAME VARCHAR2(40),
EMAIL VARCHAR2(90) );
INSERT INTO test_user
(user_name, FIRST_NAME, LAST_NAME, EMAIL)
VALUES
('test1.Öabc','Öabc', 'test1', 'test1.Öabc@test1.com');
INSERT INTO test_user
(user_name, FIRST_NAME, LAST_NAME, EMAIL)
VALUES
('test2.abc', 'abcñ','test2', 'test2.abc@test1.com');
INSERT INTO test_user
(user_name, FIRST_NAME, LAST_NAME, EMAIL)
VALUES
('test3.abc', 'abcín', 'test3','test3.abc@test1.com');
COMMIT;
SELECT ASCIISTR(FIRST_NAME) from test_user;
SQL> SELECT ASCIISTR(FIRST_NAME) from test_user;
ASCIISTR(FIRST_NAME)
--------------------------------------------------------------------------------
\FFFDabc
abc\FFFD
abc\FFFDn
Thanks
|
|
|
|
Re: invalid data inserted [message #397142 is a reply to message #397050] |
Thu, 09 April 2009 03:41   |
oracle_coorgi
Messages: 188 Registered: September 2006 Location: INDIA-karnataka
|
Senior Member |
|
|
hi
Got some solution changed the env varlable and inserted and loaded its working fine for some records and for some its gining error.
actually even if i select .
select 'Öabc' from dual --- wroking fine
select 'abcñ' from dual ---- ORA-01756: quoted string not properly terminated
select 'abcín' from dual ---- ORA-01756: quoted string not properly terminated
thnx its cirtical... pls any one look into it
|
|
|
|