Oracle 11g Study Notes (4)

Some ASP. NET + Oracle 11G systems write-and-learn essays, including referencing Oracle's official data provider and changing the date format of Oracle storage.

(8) Reference Oracle's official data provider

At the end of the last post "Oracle 11g Study Notes (3)", an ASP. NET 2.0 example is provided for you to download. However, as mentioned at the end of this post, if the server-side of Oracle 11g is not installed in the operating system of your exampleProgram(For example, Oracle is installed on another host). When you use Visual Studio to execute this example, the following error message may appear:
"Oraoledb. oracle.1" is not registered on the Local Computer
The oraoledb. Oracle provider is not logged on to the local computer.
The oraoledb. Oracle provider is not registered on the local machine

Solution: Go to the Oracle official website to download and install the client-side programs of data provider and Oracle. The download URL is as follows:
Http://www.oracle.com/technology/software/tech/windows/odpnet/index.html (newer)
Http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html (older)

After installation, you can use Visual Studio to execute this example. However, if you use IIS for example execution, the above error message may still appear, because if you are using a Windows operating system, you must use the NTFS partition, set up IIS/asp.. Net users have the permission to write to the Oracle main program directory of the host where IIS is located. This error occurs because Oracle and IIS are installed on different hosts.

(1)
I am using the Windows Server 2003 operating system. I open the folder permission in the following two directories to allow "ASP. net user, iis_wpg group, and Network Service Group have the write and modify permissions (set the host where iis or Visual Studio is located, rather than the host where the Oracle server-side program is located ):
C: \ app
C: \ Program Files \ oracle

Then, execute "gpupdate/force" in Windows to force the setting to take effect immediately (reboot if it still does not work ).

(2)
Several articles on the networkArticleTo make some settings:
Http://365.blog.ccidnet.com/blog.php? Do = showone & amp; Itemid = 687411 & amp; typ = blog
Http://topic.csdn.net/t/20050428/09/3972494.html
Http://topic.csdn.net/t/20040510/17/3050699.html
Http://www.blueshop.com.tw/board/show.asp? Subcde = brd20050513132850f5h & fumcde = fum20041006161839lrj & rplcnt = 16

For example, set the sqlnet. ora file on the host where the Oracle server-side program is located:
Sqlnet. authentication_services = (CNT)
Change
Sqlnet. authentication_services = (none)

Sqlnet. ora is in the following directory:
C: \ app \ Administrator \ product \ 11.1.0 \ db_1 \ Network \ admin

(3)
We also mentioned on the network that if the aforementioned Errors still occur, we will perform the following registry registration actions on the host where the Oracle client-side program is located (that is, the host where IIS is located:
Regsvr32 .. \ ORACLE_HOME \ bin \ oraoledb11.dll

However, I do not use this function for the support staff. IIS can use ASP. NET Applications to connect to Oracle 11g on another host.

 

Note that if you are using oledb data provider provided by Oracle, after you set the database online through Visual Studio 2005's graphic setting interface, brackets [] are automatically added as follows:
Select * from [t04]

When you execute this set of brackets, the following errors are thrown:
ORA-00903: The table name is invalid

The solution is to manually remove the brackets. If you are using oracleclient data provider, this problem does not occur. However, if the oracleclient database is set to be online through Visual Studio 2005 graphical interface, "Double quotation marks" "will still be automatically added before and after the data table name, as shown below:
Select * from "t04"

Bytes ------------------------------------------------------------------------------------------

(9) change the date format of Oracle

After oracle is installed in the Chinese operating system, in the date type, the default date format is "03-3-03 」. In this case, Asp. NET application, although it can still "read and write" Oracle date type, but to write data to the database through SQL plus, it must be behind the number of "month, you can add a Chinese Text "month" to write data to the database correctly.

If you enter the following commands in SQL plus:
Select * From nls_session_parameters;

You will see
Nls_date_formatParameter, whose value is:DD-MON-RR
Nls_languageParameter, whose value is:Traditional Chinese(Take the traditional Chinese Windows operating system as an example)

In some cases, we may want to change the date format stored in Oracle. in windows, we must modify registry )」. If you are using other operating systems, you can refer to the following articles:
Http://space.itpub.net/658698/viewspace-265000
Http://topic.csdn.net/t/20030320/16/1555747.html
Http://tech.techweb.com.cn/archiver/tid-228974.html

Take the Windows Server 2003 operating system and Oracle 11G as an example. In Windows, enter the "Regedit" command on the host where the Oracle server-side program is located to enter the Registry, and then enter:
HKEY_LOCAL_MACHINE → software → Oracle → key_oradb11g_home1

(1) modify the existing nls_language parameter and change the format of the stored month from the Chinese "month" to English:

Enter the Registry directory and change the existing nls_language parameter from the original "Traditional chinese_taiwan.zht16mswin950" to "american_america.zht16mswin950". The simplified Chinese operating system is the same, change the First Half of the string to "american_america.

(2) Add the nls_date_format parameter to modify the storage format as needed:

Enter the preceding directory of the registry, create a new "string value", name it "nls_date_format", and set its value:
YYYY-MM-DD: hh24: MI: SS

In this way, the default time format of Oracle is successfully modified. In this case, if you run the following command on SQL plus:
Select * From nls_session_parameters;

You will see
The nls_date_format parameter, whose value has changed to: YYYY-MM-DD: hh24: MI: SS

The preceding two modifications can still be used by ASP. NET and other applications. Normally, they are written to the Oracle database in the format of 2008/1/3 or 2008/01/03 12:30:30.

However, you should note that if the "Oracle SQL developer" GUI tool is built in Oracle server-side or independently downloaded from the Oracle website, if you enter "select * from
Nls_session_parameters.

In Oracle, whether it is through SQL plus or Oracle SQL developer, as long as the data is inserted, update, the next commit; or rollback; command is required. Previously, I found that Oracle SQL developer writes database records through SQL plus or ASP. net has been unable to read, originally thought that the two date format, language families are independent, and set different reasons, in fact not, but to write the database, and then the next "commit; "command to truly complete the write operation.

In addition, another command circulating on the network:
Alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';

You can only temporarily modify the language settings. Therefore, to modify the language family, it is best to modify the registry of the host installed in the Oracle server-side program, which is a permanent solution.