Oracle 11g Study Notes (2)

Some ASP. NET + Oracle 11G systems write-and-learn essays, including Oracle's "batch Update" and Oracle's internal encoding set to the Unicode Character Set.

(4) Oracle "batch Update", "batch Add", and "batch Delete" Syntax

Oracle can still isolate Multiple SQL statements using semicolons (;), such as SQL Server and Sybase databases. Only the begin and end keywords must be added before and after oracle. For example:

String strsql = "begin insert into table01 (ID, name) values (99, 'test'); insert into table01 (ID, name) values (99, 'test '); end ;";
String strsql = "begin update table01 set name = 'test2' where id = 99; update table01 set name = 'test2' where id = 99; end ;";
String strsql = "begin Delete from table01 where id = 99; delete from table01 where id = 19; end ;";

Due to Oracle's rigorous syntax, special symbols such as ";" cannot appear directly in SQL statements.

(5) Enable Oracle to support Unicode Processing

When Oracle 11g is just installed, the preset character set cannot process texts in other languages. If you enter commands in sqlplus:
Select parameter, value from V $ nls_parameters where parameter like '% characterset ';

The following information about nls_lang is displayed:
--------------
Nls_characterset
Zht16mswin950 (Traditional Chinese) or
Zhs16gbk (Simplified Chinese)

Nls_nchar_characterset
Al16utf16
--------------

Next, we will use the "alter database character set" command to change the character set encoding method in Oracle.

Please go to sqlplus firstProgram, Use the sysdba role to enter. The command is as follows:

Connect sys/password as sysdba;

Then, enter the following commands. After the command is executed, the conversion task is automatically executed:

Shutdown immediate;
Startup Mount;
Alter session set SQL _trace = true;
Alter system enable restricted session;
Alter system set job_queue_processes = 0;
Alter system set aq_tm_processes = 0;
Alter database open;
Alter database character set internal_use al32utf8;
Alter session set SQL _trace = false;
Shutdown immediate;
Startup;

This method may cause confusion and loss of existing storage data in Oracle. It is best to back up data before executing the preceding commands.

Next, run the following command to check the information about nls_lang:
Select parameter, value from V $ nls_parameters where parameter like '% characterset ';

The information about nls_lang has been changed:
--------------
Nls_characterset
Al32utf8

Nls_nchar_characterset
Al16utf16
--------------

In addition, this method is widely used on the network, which is incorrect:
Update props $ set value $ = 'al32utf8' where name = 'nls _ characterset ';
After changing the characterset using the correct alter database character set command, Oracle will change at least 12 data dictionaries. However, if you directly update the props $ table using this method, in this case, only 1/12 of the tasks are completed, and the potential integrity and worry are conceivable.

If we want to use an ASP. NET application to capture and write data to Oracle, if there are Japanese and other Chinese characters, in addition to changing the character set in Oracle to Unicode as described above,
Pay attention to the online method of ADO. net. In Visual Studio 2005, its built-in oledb data provider is used for online Oracle 11 GB, and cannot correctly process special characters and other Chinese Characters in UNICODE, or even write data into Oracle, the following error occurs:

The parameter [1] ''data value cannot be converted because the positive and negative numbers do not match or the data overflow exists. ID: 0

The solution is to use oracleclient data provider or Oracle's official oledb data provider. The download point is:
Http://www.oracle.com/technology/software/tech/windows/ole_db/index.html
Http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

========================
Reference file:
Http://www.monster.com.tw/archives/471
Http://www.javaworld.com.tw/jute/post/view? Bid = 21 & id = 137350 & sty = 1 & TPG = 2 & age = 0
Http://blog.roodo.com/mywork/archives/6198547.html

========================