. Net 2.0 accesses Oracle-differences with SQL Server, precautions, common exceptions

Http://cwbboy.cnblogs.com/archive/2006/03/22/356017.html

You can access a database on the. net platform in the following ways:
1. OleDB database access program,
2. ODBC Database Access Program,
3. proprietary database access programs. For example, when accessing SQL Server 2000, we generally like to use proprietary SQL Server. NET Framework database access programs. The namespace is System. Data. SqlClient.

Here I use the third type, that is, the "dedicated database access program" (Oracle. NET Framework Database Access Program) to access the Oracle database.

Before Version 1.1, Oracle. NET Framework needs to be downloaded separately. net Framework itself does not have this component. :

In Framework 2.0, the Oracle. NET Framework database access program is provided. But it does not mean that with the Oracle. NET Framework, you can access Oracle smoothly. To access the Oracle database, in addition to the proprietary database access program, the following conditions must be met:

You must install the Oracle 8i Release 3 (8.1.7) client or a later version.

The following is a detailed description of Oracle Database Access:

Frequently Used Components

The namespace of the Oracle. NET Framework Database Access Program is System. Data. OracleClient. The file name is System. Data. OracleClient. dll, which is located in the global assembly cache. By default, vs 2005 does not reference this component. To use this component, you only need to add a reference.
Similar to SqlClient, The OracleClient namespace consists of OracleConnection, OracleCommand, OracleDataReader, OracleParameter, and OracleType. The above lists the most commonly used classes. For more detailed class views, see MSDN.

Field Type, parameter type

The field type is generally involved when Parameter is used. In SQL Server, we generally use SqlDbType enumeration to represent various field types in the database, while in Oracle, OracleType is used. In Oracle, character fields often use Varchar2 or Nvarchar2, while Numeric Fields use Number. Varchar is followed by a 2 character, so I didn't go deep into Oracle. I don't know what the meaning of this 2 character is. When Parameter (Parameter) is used.
OracleType. Varchar indicates varchar2 in the database,
OracleType. Nvarchar indicates Nvarchar2 in the database,
OracleType. Number indicates Number
OracleType. DateTime indicates Date
For example, OracleType. int32 because it does not have the corresponding field type in Oracle, it is generally small. If the value of the Number type field in the database does not have a decimal place, you can also use OracleType. int32 corresponds to Number.

Database Connection

Similar to SqlConnection, the connection string is generally: User ID = User name; Data Source = Database Server Source Name (paiel Database Service name); Password = Password

The following is the connection string I used: User ID = search_user; Data Source = etbank_192.168.0.250; Password = 12345

Use of Stored Procedures

A stored procedure in Oracle is called a Packages. A package is divided into a packet header and a package body, similar to the class declaration in C ++. The header defines the name and parameters of a stored procedure. Besides the name and parameters, the package body also contains all statements of the stored procedure. Unlike SQL Server, the stored procedures in Oracle are generally written as functions rather than PROCEDURE. Oracle stored procedures do not directly return record sets. Record Sets are returned through parameters in the form of cursors. A package can contain multiple stored procedures.Package name. Stored Procedure nameThe following is a typical Oracle stored procedure. It is located in the Packages named "Test". Its usage should be: Test. GetList

Function GetList (keywords In varchar2
, P_info_list_Cursor out get_data_cur_type)
Return Number
As

Begin

Open p_info_list_Cursor
Select * from Test where Key = keywords
;
Return 0;
End;

The stored procedure returns only one Number, and the record set is returned as an out parameter. The method of calling in. net is as follows:

1 OracleConnection OracleConn = new OracleConnection (connection string );
2 OracleCommand cmd = new OracleCommand ("Test. GetList", OracleConn );
3 cmd. Parameters. AddRange (
4 new OracleParameter []
5 {
6 new OracleParameter ("keyWords", OracleType. VarChar ),
7 new OracleParameter ("ReturnValue", OracleType. Number, 0, ParameterDirection. ReturnValue, true, 0, 0, "", DataRowVersion. Default, Convert. DBNull ),
8 new OracleParameter ("p_info_list_Cursor", OracleType. Cursor, 2000, ParameterDirection. Output, true, 0, 0, "", DataRowVersion. Default, Convert. DBNull)
9 });
10 cmd. Parameters [0]. Value = 'beauty ';
11 cmd. Parameters [0]. Direction = ParameterDirection. Input;
12 cmd. CommandType = CommandType. StoredProcedure;
13 OracleConn. Open ()
14 OracleDataReader rdr = cmd. ExecuteReader ();
15 // other code
16 OracleConn. Close ();
17

The parameter name in OracleParameter must be the same as the name in the stored procedure, which can be case-insensitive. A Stored Procedure defines two parameters, one KeyWords and the other a cursor of the out type: p_info_list_Cursor. Because the Function has a return value, we also need to add aReturnValue"Parameter, which is fixed. The record set is returned through p_info_list_Cursor. After the parameter configuration is complete, you can directly use the Exec method of cmd. Although we use an Out parameter to accept the record set cursor, this parameter does not need to be processed and OracleCommand will automatically process it, we only need to get the DataReader and then read the data like SqlCommand in the past.

Directly execute SQL statements

":" Is used in SQL statements to indicate parameters.
In SQL Server, we can use the SQL statement as follows: "Insert into Table (Field1, field2) values (@ Value1, @ Value2)", and then we will create several New paramters: new SqlParameter ("@ Value1", value )...
The @ + character is used in the query string to describe the parameter. The parameter name in SqlParameter must also use the "@" symbol.
In Oracle, SQL statements cannot use the @ symbol instead of the colon ":". For example:

String SQL = "Insert into SEARCH_HISTORY (KEYWORDS, PHONE, RESULT_ID, SEARCH_TIME) values (: KEYWORDS,: PHONE,: RESULT_ID,: SEARCH_TIME )";
OracleCommand cmd = new OracleCommand (SQL, OracleConn );
Cmd. Parameters. AddRange (new OracleParameter [] {
New OracleParameter ("KEYWORDS", OracleType. VarChar ),
New OracleParameter ("PHONE", OracleType. VarChar ),
New OracleParameter ("RESULT_ID", OracleType. Number ),
New OracleParameter ("SEARCH_TIME", OracleType. DateTime)
});
Cmd. Parameters [0]. Value = Keywords;
Cmd. Parameters [1]. Value = Phone;
Cmd. Parameters [2]. Value = 2;
Cmd. Parameters [3]. Value = DateTime. Now;

Common Errors:

1. the number or type of parameters for calling 'stored procedure name' is incorrect"

The error occurs because the parameter name you used to create OracleParameter is inconsistent with the parameter name defined in the stored procedure or SQL statement. In addition, you should also note that, although the SQL statement uses the colon ":" To represent the parameter, but when creating OracleParameter, the specified parameter name cannot use the colon, when new OracleParameter, parameterName can only use the character section of the parameter.

2. "Stored Procedure name" is not a process or has not been defined"

The number of Parameters in the Parameters set of OracleCommand is inconsistent with the number defined in the stored procedure. You may have missed a Parameter not created

3. "ORA-01036: Invalid variable name/number"
 
The cause of this error is probably that the parameter definition of the package body in the stored procedure is different from that of the header. Many times the package body is modified, but the packet header is forgotten. In addition, this error may occur when you create OracleParameter due to a large number of constructor versions. We recommend that you specify OracleType when creating OracleParameter.
When you directly use an SQL statement, the parameter section in the SQL statement does not use a colon as the prefix, or the "@" symbol of SQL Server is incorrectly used.

In short, this error occurs when the ParameterName specified by Parameter does not match the actual Parameter name.
 
Note: In new OracleParameter (), the specified ParameterName only needs to contain the character section of the parameter and does not need to contain the prefix, for example, colon.

4,System. Data. OracleClient requires Oracle client software version 8.1.7 or greater. 

This error indicates that you need to install the Oracle client. If you have already installed the Oracle client and this error still occurs, you must have answered the following question due to permission issues: http://www.cnblogs.com/jeet/archive/2005/06/24/115150.html:

1. Log on as an administrator;
2. Find the ORACLE_HOME folder (for example, C: \ oracle \ ora92), right-click it, select attribute-security, and select "Authenticated Users" in the group or user bar ", in the following permission list, remove the "read and run" permission, and then press the application. Re-select the "read and run" permission and click the application; select the "advanced" button under the permission box, confirm that the application following "Authenticated Users" is "this folder, subfolders, and files", and apply the permission changes to this folder according to OK;

The first time I used Oracle, I encountered this problem and solved it using the jeet solution. Jeet said that the system must be restarted, but I can do it without restarting. This may be a problem with the operating system version. I use windows 2003.

Summary:
The access to Oracle and SQL Server is very different:

1. Different Field Types
2. There is a big difference in the stored procedure. Oracle cannot directly return the record set, and an out parameter is required. There is an OracleType. Cursor type in OracleType that corresponds to it. Most of the stored procedures in Oracle are defined as Funcion and return values. Add a "ReturnValue" parameter when defining the Command parameter set.
3. Oracle parameters do not require the "@" symbol
4. in Oracle SQL statements, add a colon Before the parameter":", While SQL Server's SQL is preceded "@"

-- SQL statement of SQL Server
Insert into Table (Column1, Column2) values (@ Value1, @ Value2)

-- SQL statement in cmdel
Insert Into Table (Column1, Column2) values
(: Value1,: Value2)