SQL & PL/SQL :: Insert Some Chinese Character In Oracle Table?

May 9, 2011

I want to insert some Chinese character in a row of my master table and also retrieve the same to display on web page along with others.

View 2 Replies


ADVERTISEMENT

Oracle 10g - Chinese Character In New Columns

May 21, 2010

I am using Oracle 10g version and I have a database created and it contains around 9 tables and 25k records, as per my current requirement I need to add few more columns in the existing tables and those new columns are going to be filled with contents from multiple languages (as of now Chinese and English). I went through Oracle 10g globalization guide and I understood that, for my requirements I need to add my new columns with data types of either NVARCHAR or NCHAR.

With my about understanding I created the following table and tried to insert some Chinese characters as follows but it's now coming as expected and I'm getting inserted only "????" in my table columns

Find below the list of actions

>create table Employee(EmpId varchar(255), EmpName NCHAR(255));
>insert into Employee(EmpId, EmpName) values('280129','彭俊睦');
>select * from Employee;
280129 ¿¿¿

>insert into Employee(EmpId, EmpName) values('28018',N'彭俊睦');
>select * from Employee;
280129 ¿¿¿
28018 ¿¿¿

When I run the “select * from v$nls_parameters;” query I’m getting the following data

NLS_LANGUAGE ---------à SIMPLIFIED CHINESE
NLS_TERRITORY---------à AMERICA
NLS_CURRENCY---------à $
NLS_ISO_CURRENCY---------à AMERICA
NLS_NUMERIC_CHARACTERS---------à .,
NLS_CALENDAR---------à GREGORIAN
NLS_DATE_FORMAT---------à DD-MON-RR
[Code] .....

View 3 Replies View Related

Server Administration :: Unable To See / Insert Chinese Characters In Oracle Database

Jun 14, 2013

We are getting problem with the Chinese character set. My current character set is as follows.

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
[code]....

My column description for the table product is as follows.

PART_NBRVARCHAR2 (30 Byte)
PART_DESCNVARCHAR2 (2000)

when trying to insert Chinese character using the insert command below

insert into product(part_nbr,part_desc,cust_name) values('322341',unistr('功'),'test');

I am getting the value when selecting the same record using the select command

select a.part_nbr,a.part_desc,a.cust_name from product a where a.part_nbr='322341'322341¿test

When I running this command on TOAD

select a.rowid,a.part_nbr,a.part_desc,a.cust_name from product a where a.part_nbr='322341'

and manually editing/inserting '功' character in output from select command above. After that I am able to get the same Chinese character when I am running select next time.

View 7 Replies View Related

Globalization :: Convert Chinese Character From US7ASCII To AL32UTF8?

Sep 17, 2012

Source Database: AMERICAN_AMERICA.US7ASCII
Target Database: AMERICAN_AMERICA.AL32UTF8

From the source database, the chinese characters are stored in some schema table. From the csscan result, there are convertiable, truncate, data lossy character. So, I have tried to use exp/imp for the conversion. However, all chinese characters are invalided and cannot be read anymore. How can I convert them from US7ASCCI to UTF8 database?

Also, I have tried build up another database with AMERICAN_AMERICA.ZHT16MSWIN950. The exp/imp is used for conversion again. The chinese characters are readable in AL32UTF8 database.

- source database (US7ASCII)
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export LANG=AMERICAN_AMERICA.US7ASCII
exp userid='/ as sysdba' file=export.dmp full=y

- target database (AL32UTF8)
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export LANG=AMERICAN_AMERICA.US7ASCII
imp userid='/ as sysdba' file=export.dmp full=y ignore=y

Result:
from US7ASCII to AL32UTF8:
the chinese characters cannot be read

from US7ASCII to ZHT16MSWIN950:
the chinese characters cannot be read

from ZHT16MSWIN950 to AL32UTF8:
the chinese characters can be read

How can I convert the chinese character from US7ASCCI to UTF8 database?

View 6 Replies View Related

Globalization :: Forms / Reports Chinese Character Support?

Jul 4, 2013

We have an existing db (10.2.0.4.0) and forms (11.1.2.1.0) application, that we're trying to extend to support Chinese characters. We're looking to add some unicode (nvarchar2) columns to existing tables, rather than converting the whole db charset. I've pasted my environment settings below. What I've found so far in trying to create a local (ie. running the form in Builder with local weblogic running) test form, is that I can insert the chars ok (using plsql developer) and the test form can display them correctly, but cannot write them back to the database. They appear as upside down question marks in any records the form has created. 

Env variables:NLS_LANG = .UTF8 Database:NLS_CHARACTERSET = WE8MSWIN1252NLS_NCHAR_CHARACTERSET = AL16UTF16 1)

So, how to get the form to write the characters back into the database correctly? 2) The chinese chars will only be relevant to a few forms inside the app, are there any settings local to the form that will enable unicode support, rather than setting at OS level. ie, an alter session, or equivalent? 3) Oracle Reprts doesn't appear to have an nchar datatype unlike Forms, is there anyway to get Reports (generating PDFs), to include Chinese?

I now have the chars writing back to the db ok. If you do it via an INSERT statement from inside the form, it doesn't work. It appears the value is sent to the db in the normal charset rather than the national charset, and it's written as a question mark. If you pass the value from the form into a back end stored proc though (which does the insert) it works okay.

View 3 Replies View Related

SQL & PL/SQL :: How To Insert Special Character / ORA-00911 / Invalid Character

May 25, 2010

We have Oracle 10g and user trying to Insert following in one of our table field and getting error:

'REVOLUCIÃ"N Historical Corruptions Agenda'

Getting follwoing error:

ORA-00911: invalid character
create table Employee
(Emp_ID Varchar2 (10),
Company_Name Varchar2 (40)
)
Insert into Employee
values ('Emp1', 'REVOLUCIÃ"N Historical Corruptions Agenda');

View 5 Replies View Related

SQL & PL/SQL :: Creating Oracle Database To Support Chinese Fonts

Aug 21, 2012

mentioned database is created with Character set = UTF8 and the National Character Set = AL16UTF16 and got the result for other languages (latin,german,french etc) but still Chinese language was not supported.

NLS_CHARACTERSETAL32UTF8
NLS_NCHAR_CHARACTERSETAL16UTF16

I was getting the output as given below-

SQL> select translator ('table and chair','zh-CN','en') English_to_chinese from dual ;

ENGLISH_TO_chinese
--------------------------------------------------------------------------------
┐┐┐┐┐

and the function is given below

/* Formatted on 16/08/2012 21:55:39 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE FUNCTION translator (p_words IN CLOB, -- words to be translated
p_to IN VARCHAR2, -- language to translate to

[Code].....

View 15 Replies View Related

Globalization :: Junk Character Insertion In Oracle Table

Feb 6, 2013

How to avoid Junk character insertion in oracle table. I have prepared scripts like this Say

customer - info

After insertion the data is inserted like below in production

Customer ¿ info

We are using command prompt for script execution in production environment. I am using PLSQL developer and SQL developer for development. i cannot see junk data in PLSQL developer and latest SQL developer , but its caught in old version of SQL developer. Also in Application also i can able to figure out junk data.

View 6 Replies View Related

Insert Special Character In Database?

May 5, 2008

I am trying to insert data into table through SQL.

However my table has a column by name "User Name". Now I have a user name " x,yz" (see there is comma b/w x and y).

how do I escape and insert this data(x,yz) into the table.I need data as it is in DB for my application.

View 1 Replies View Related

Oracle 10g - Take Snapshot Of Table Before Insert Or Update Happens To That Table

Dec 28, 2010

I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..

[URL].......

how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?

assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?

create materialized view my_view refresh fast as select * from t1;

create materialized view log on my_view;

and then schedule in a dbms_jobs?

View 1 Replies View Related

Client Tools :: Unable To Insert Euro Symbol In Database With Character Set WE8MSWIN1252?

Mar 29, 2011

I am using oracle 9.2.0.6.0 on HP-UX.

I am unable to insert and even display euro symbol from server as well as windows client.

Following are the details of my database server

SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252

[code]....

1) When I try to insert € from Db server (using putty) using Alt+0128 it does not print anything (nothing gets typed on the screen). Not even junk characters Also following query does not print anything

SQL> select chr(128) from dual;
C
-

2) while I set NLS_LANG on client and try to insert €, Alt+0128 produces a question mark symbol And following query displays junk character

SQL> select chr(128) from dual;

C
-
Ç

3) Regardless when I inserted couple of rows and tried UNISTR following was the result

SQL> select unistr(v) from t;
Error 45 initializing SQL*Plus
Internal error
$

View 19 Replies View Related

Server Administration :: How To Know Character Set As Single Character Or Multi Character

Sep 26, 2013

how do we know database character set is either single character set or multi character set?

While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".

Below are steps taken to resolve the issue -

ALTER DATABASE CHARACTER SET WE8MSWIN1252;

i got this error: ORA-12712: new character set must be a superset of old character set

below are the commands executed by me:

SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;

And its working...

I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?

View 11 Replies View Related

SQL & PL/SQL :: Insert Into Oracle Table Of UNIX Script Values

Dec 24, 2012

I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.

E.g. : insert into tbl_daily_status values(STAT,ENDTIME);

#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do
STAT=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $6}'`
ENDTIME=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $4" " $5}'`
echo "$STAT"
echo "$ENDTIME"
done

View 2 Replies View Related

PL/SQL :: How To Read CSV File And Insert Data Into Oracle Table

Feb 6, 2013

I have a Clob file as a in parameter in my PROC. . File is comma separated.need procedure that would parse this CLOB variable and populate in oracle table .

View 6 Replies View Related

Application Express :: What Would Cause Oracle To Insert Duplicate Rows Into A Table

May 2, 2013

What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.

View 9 Replies View Related

Server Utilities :: Insert Data Without Writing Insert Statement In Oracle?

May 15, 2010

how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.

View 2 Replies View Related

SQL & PL/SQL :: Chinese Zodiac Associated With Birth Years

Aug 8, 2010

I have a question to write a pl/sql for Chinese zodiac but i cant do it, even i Google it i cant find any solution..the question is as below:

The Chinese zodiac associates birth years with the following; animals:

Birth Year Animal
1924,1936.1948,1960,1972,1984,1996 Rat
1925,1937,1949,1961,1973,1985,1997 Cow
1926,1938,1950,1962,1974,1986,1998 Tiger
1927,1939,1951,1963,1975,1987,1999 Rabbit
1928,1940,1952.1964,1976,1988,2000 Dragon
1929,1941,1953,1965,1977,1989,2001 Snake
1930,1942,1954,1966.1978,1990,2002 Horse
1931,1943,1955.1967,1979,1991,2003 Sheep
1932,1944,1956,1968,1980,1992,2004 Monkey
1933,1945,1957,1969,1981,1993,2005 Chicken
1934.1946,1958,1970,1982,1994,2006 Dog
1935,1947,1959,1971,1983,1995,2007 Pig

Write a command to declare a date variable named birth_date, and assign to it your birth date. Use an IF/ELS1F structure to test every year and determine the animal associated with your birth year. Then display your birth year and the associated animal name. For example, the program would display the following output for someone born in 1984:

I was born in 1984, which is the year of the Rat. Declare and use additional variables as needed.

View 18 Replies View Related

SQL & PL/SQL :: Display Data In Chinese Language In Windows 7

May 21, 2013

I want to display data in Chinese language.I have a emp table in my scott user.

View 7 Replies View Related

Client Tools :: Toad Converts Chinese

Oct 26, 2012

I have some data in an Excel Sheet which is in Simplified Chinese, which I have to enter in oracle database. As soon as I paste the data into the insert statement on TOAD it gets converted into question marks.

Toad Version : 9.5.0.31
Oracle DBMS: 10.2.0.4.0
NLS_CHARACTERSET= UTF8

Chinese language is also installed on the instance with base language as US.

View 1 Replies View Related

Client Tools :: How To Display Chinese Characters In Sql Developer

Apr 22, 2013

I have a table named invoice_info(INV_NUMBER number,BILL_TO_NAME char(55),SHIP_TO_NAME char(55)) which is having chines characters in the BILL_TO_NAME & SHIP_TO_NAME fields.

If i am displaying this tables data on my sql developer it's showing data like some junk chars like ?????????¨¬???? for the fields BILL_TO_NAME & SHIP_TO_NAME .

How can i display them as chines chars on sql developer.

View 3 Replies View Related

SQL & PL/SQL :: Access Column Having Japanese Data Along With English And Chinese?

Sep 19, 2011

I have a table having data in different languages like English, Japanese and Chinese. I need to retrieve only those rows which are in Japanese. What all settings do I need to make. When am doing a normal select, rows in languages other than English are appearing as Junk data.

View 8 Replies View Related

SQL & PL/SQL :: Drop Table With Special Character?

Jul 15, 2011

I have created a table with space by mistake.how to drop that..?

QL> select * from cat where table_name like 'OM_DW_RTNORD%';

TABLE_NAME TABLE_TYPE
------------------------------ -----------
OM_DW_RTNORD? TABLE
OM_DW_RTNORD TABLE

[code]....

no rows selected

SQL> drop table OM_DW_RTNORD? purge;
drop table OM_DW_RTNORD? purge
*
ERROR at line 1:ORA-00911: invalid character

View 3 Replies View Related

Creating Table Error - Invalid Character

Mar 3, 2011

I am trying to create two tables and I am getting the "ORA-00911: invalid character" Error. I have been trying to work it out for last past 30 minutes to no success.

CREATE TABLE special_rate
(
rate_id VARCHAR(3),
rate VARCHAR(5),
rate_expiry_date NUMBER(12),
PRIMARY KEY( rate_id ),
FOREIGN KEY( contract_id ) REFERENCES contract(contract_id)
[code]....

View 5 Replies View Related

Inserting Character From Extended ASCII Character Set?

Mar 8, 2007

I'm trying to insert a character from the extended ascii character set. Specifically, there's a company that has an accented e (�) in the name. Right now, the company name doesn't have the e at all, accent or no accent. So I'm trying to do an update, something like

update table1 set company_name='blah�" where company='blah'

It runs, but doesn't do the update. Even when I try to forcefully do an insert (instead of an update) I get nowhere; the accented is simply dropped. So the basic question is, how do you insert extended ascii characters into oracle?

View 3 Replies View Related

PL/SQL :: How To Select String Between Nth Specific Character To Nth+1 Character

Aug 9, 2012

I have one string 'SWAP_20120224_MEGAMART_MAR_Sales.csv'

I want to get the string between second underscore and third underscore i.e. 'MEGAMART'

How to achieve this in sql statement?

View 4 Replies View Related

Globalization :: Character Encoding - LATIN-1 Character In UTF-8 DB?

Feb 9, 2013

I am using C++ OCI LIB, to insert some report data from remote OCI client to oracle 11 server. This data is read by another process to create the report.The DB CHARSET is UTF-8. But the report tool expects the data to be ISO08859-1 encoded. So while inserting the data into the database i specify the following LANG and CHARSET for my table colulmn in client:

The TARGET DB CHARSET is UTF-8
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
size_t csid = 871; // UTF-8
OCIAtrSet((void *) bnd1p, (ub4) OCI_HTYPE_BIND,
*(void *)&csid*,
(ub4) 0,
(ub4)OCI_ATTR_CHARSET_ID, errhp);

This solution works for almost every case of ASCII and Extended ASCII Charest but we are facing issues if we have few specific characters to be inserted.f we are trying to insert single beta character [β] through client, the data goes empty to the column.

Beta Character details:
DEC     OCT     HEX     BIN     Symbol           Description
223     337     DF     11011111     ß     Latin small letter sharp s - ess-zed

DB Output after insert single β:
select rawtohex(NAME) from PERSONS where EID=333;

RAWTOHEX(NAME)
---------------------------

But if the string is *"ββ"* everything work fine:
DB Output for "ββ":
select rawtohex(NAME) from PERSONS where EID=333;

RAWTOHEX(NAME)
---------------------------
DFDF

View 6 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

PL/SQL :: Selecting Records From 125 Million Record Table To Insert Into Smaller Table?

Jul 17, 2013

Oracle 11gI have a large table of 125 million records - t3_universe.  This table never gets updated or altered once loaded,  but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads -  that will be updated with regard to when the lead is mailed and for other relevant information.  select records from this 125 million record table to insert into the smaller table. 

I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below.  Then use a second query that inserts into T3_Leads from this View V_Market.  This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause?    My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.   

CREATE VIEW V_Market  asWITH got_pairs    AS     (         SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */  l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no       ,      ROW_NUMBER () OVER ( PARTITION BY  l.address_key                                    ORDER BY      l.hh_verification_date  DESC                    ) AS r_num         FROM   t3_universe  e         JOIN   t3_universe  l  ON                l.address_key  = e.address_key             AND l.zip_code = e.zip_code           AND   l.p1_gender != e.p1_gender      

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Oracle 9i DB - Developer Suite And Character Set

Feb 20, 2012

I am running an Oracle 9i DB with character set : FRENCH_FRANCE.WE8ISO8859P1.

The data inside the DB deals with two languages :
- French and Arabic.

I insert, delete, update data on the DB through one of these tools :
- SQL*PLUS
- Forms 6i

and everything works fine especially with Arabic : characters are interpreted correctly by these tools. Now, I want to use Dev Suite as a new tool.

My problem is with Arabic characters : they are not displayed correctly. On the web form (run by a JRE : JInitiator) the characters are not replaced by "?", they are wrongly interpreted.

Example :

The right interpretation of "Amine" in arabic is : أمين and not : Ããíä.
So, querying a table with Dev Suite returns me Ããíä and not ????.

It seems like the JRE "understands" the characters but doesn't know how to "interpret" the Arabic characters correctly. I've tried to play with the NLS_LANG parameter on the regedit but in vain.

View 6 Replies View Related

Forms :: Read Data From Table And Insert To Another Table With A Cursor?

Feb 20, 2013

I have a table with a BLOB column ;

I want read data from table and insert to another table with a cursor

My code is :

procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;

[code]....

when I run form , error FRM-40734 occurred

error in line " fetch .... "

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved