Home » SQL & PL/SQL » SQL & PL/SQL » invalid data inserted (oracle 9.2.0.1.0 linux)
invalid data inserted [message #396740] Tue, 07 April 2009 22:37 Go to next message
oracle_coorgi
Messages: 185
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 #396741 is a reply to message #396740] Tue, 07 April 2009 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>invalid data inserted
Unproven assertion above
>(oracle 9.2.0.1.0 linux)
a very buggy, obsolete, & unsupported version.

You can use ANSISTR() function to determine is the non-ANSI characters are being stored in the DB
or the problem is only a data presentation issue.
Re: invalid data inserted [message #396772 is a reply to message #396741] Wed, 08 April 2009 00:53 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
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 #396774 is a reply to message #396740] Wed, 08 April 2009 00:57 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>pls look into this
NO

enjoy your reality
Re: invalid data inserted [message #396969 is a reply to message #396774] Wed, 08 April 2009 09:19 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
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 #396973 is a reply to message #396740] Wed, 08 April 2009 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Is the problem one of getting the non-ASCII characters into the database or one of properly displaying the characters for presentation?
Re: invalid data inserted [message #396996 is a reply to message #396973] Wed, 08 April 2009 10:19 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
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 #397050 is a reply to message #396740] Wed, 08 April 2009 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Based upon most recent query results, we can see the non-ASCII characters are being stored within the DB.

Therefore you need to solve the data presentation issue.
Simply put, your client software is not properly displaying the non-ASCII characters.

The solution is between you, the client Operating System & client software.
Re: invalid data inserted [message #397142 is a reply to message #397050] Thu, 09 April 2009 03:41 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
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
Re: invalid data inserted [message #397501 is a reply to message #397142] Sat, 11 April 2009 10:41 Go to previous message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi

Thanxs every body for your support it worked ...

Thanks
Previous Topic: Not Null then Pass Value
Next Topic: One year minus on Date
Goto Forum:
  


Current Time: Wed Dec 07 02:40:59 CST 2016

Total time taken to generate the page: 0.12305 seconds