Use. NET applications connect to the Oracle database

Recently asked the classmate "why." NET application connecting to an Oracle database is always an error? "I think this is because we usually use Microsoft's products and lack research on other platform products." Microsoft has always had a "platform unified" dream, but unfortunately this dream will never come true. Why, because there are many companies like Oracle do not buy Microsoft accounts, they will not be too much support for Microsoft's products, they are often launched their own products, and many of their products are more users than Microsoft, so Microsoft's "platform Unified" dream can never be achieved. So much nonsense, I'll talk about how to properly configure Oracle to adapt to. NET application development.

First, download the required components

• Oracle 11.2g (64-bit)
  : http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html
  Note: This article uses 64-bit, if your machine does not support 64-bit, please download 32-bit.
• Oracle Data Access Components (ODAC) (64-bit)
  This component is the. NET applications to connect with Oracle. NET application is to access the Oracle database through this component.
  64-bit: http://www.oracle.com/technetwork/cn/database/windows/downloads/index-098472-zhs.html
  32-bit: http://www.oracle.com/technetwork/cn/database/windows/downloads/index-101312-zhs.html
  Note: This article uses 64-bit, if your machine does not support 64-bit, please download 32-bit.
• Oracle SQL Developer
  This tool is a visual tool developed by PL/SQL and you don't need to download this tool if you think it's cool with sqlplus.
  : http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
  Note: This article uses 64-bit, if your machine does not support 64-bit, please download 32-bit.

Ii. start installation of required components

1, Oracle installation is relatively simple, directly click "Next" in the end to complete the installation.

2. Then, the most critical step, configure the Oracle Listener.ora file. I have this file in the "C:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN" directory, if you have Oracle installed elsewhere, please go to the relevant directory to find. When the file is found, open with Notepad, and then we need to add a "SID_DESC" node with the following content:

(Sid_desc =
    (Global_dbname = ORCL)
    (Oracle_home = C:\oracle\product\11.2.0\dbhome_1)   
    (Sid_name = ORCL)
)

Then we need to add a "Default_service_listener" parameter configuration item and set its value to "ORCL". If this is not configured, then the next call to the ODAC component will continue to be reported as "Ora-12504:tns:listener is not given the service_name in Connect_data" error, Remember! Remember! This is the force of the thing, it took me a day time, design This parameter of the people TMD brain residue!

Default_service_listener = ORCL

So, finally see the content of the Listener.ora file should be the following, note the yellow section:

# Listener.ora Network Configuration File:c:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
 
Sid_list_listener =
(sid_list =
(Sid_desc =    
    
 
(global_dbname = ORCL)
    
 
(oracle_home = C:\oracle\product\11.2.0\dbhome_1)
    
 
(sid_name = ORCL)
    
 
    )
(Sid_desc =
(sid_name = clrextproc)
(oracle_home = C:\oracle\product\11.2.0\dbhome_1)
(program = extproc)
(Envs = "Extproc_dlls=only:c:\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )
 
LISTENER =
(description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
    )
  )
 
Default_service_listener = ORCL
 
Adr_base_listener = C:\oracle

The Tnsnames.ora file should then be modified accordingly, as follows:

# Tnsnames.ora Network Configuration File:c:\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (Address_list =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
    )
    (Connect_data =
      (SERVER = dedicated)
      (service_name = ORCL)
    )
  )

3, then restart the "Oracleoradb11g_home1tnslistener" service and "ORACLESERVICEORCL" service.

4. Then we open the Sqlplus command-line tool and enter "/as sysdba" to log in to Oracle as an administrator, then enter "Startup" to start the database. The normal situation should be as follows:

However, what if the display is shown?

Here is a recommended solution I found on the Internet, address: http://blog.csdn.net/lpftobetheone/article/details/11099785. You can refer to this article as described in the repair, note that the article in some parts of the description and inconsistent, please try to be accurate. If you really don't understand, you can leave a message to me below.

5, Oracle installation is finished, and now we need to create a table and insert a few data. I personally do not like to write SQL statements in Sqlplus, if you like, then please skip to the 6th step.

1) Unzip the "Sqldeveloper-4.0.3.16.84-x64.zip" package we downloaded, then double-click on the "Sqldeveloper.exe" on it and then click on the "New Connection" button as shown:

2) Enter as shown, then click Connect:

3) then enter the following SQL statement:

--CreateSuzyuser, password is:ORCL, the default table space is:Users
Create User Suzy identified  by ORCL default tablespace users; 
 
--AuthorizedSuzyuser does not restrict the use of their tablespace
Grant Unlimited tablespace  to Suzy; 
 
--AuthorizedSuzyPermissions for user connection permissions and resource access. 
Grant CONNECT,RESOURCE  to Suzy; 

4) then run the SQL statement:

5) then repeat the 2nd step to log in as "Suzy":

6) then write the SQL statement that created the table and execute:

7) Then insert two records:

6, the next step is to install the ODAC components, the download of the ODAC components to a directory, for example, I extracted here to "C:\ODAC112021Xcopy_x64" this directory.

7. Open the command line under the path extracted from the previous step, then enter the command "Install.bat". We see a few command prompts, you can choose according to your needs, here I choose to install all ODAC components, and install it to "C:\odp.net", and then enter the following command, and wait for it to complete:

Install.bat All C:\odp.net myhome

8, then open "C:\odp.net" can see has created a lot of files, then we open the Command window in this directory, enter the command "Configure.bat", we will still see the command prompt message, and then according to the prompt information enter the following command, and wait for it to complete:

Configure.bat All MyHome

9. Use the VS new console project and add the reference "C:\Windows\Microsoft.NET\assembly\GAC_64\Oracle.DataAccess\v4.0_4.112.2.0__ 89b483f429c47342\oracle.dataaccess.dll ". Of course, if you have a "oracle.dataaccess" in the GAC list of VS, you don't have to go to the GAC directory of C-drive to find it, because I can't find it in the GAC list of my vs, so I run the C-drive GAC directory.

10, write the test code, as follows:

Static voidMain (string[] args)
{
    stringconnectionString =ConfigurationManager. connectionstrings["ConnStr"]. ConnectionString;
    using (OracleConnectionconn =New OracleConnection(connectionString))
    {
        stringsql ="SELECT * from TESTORCL";
        using (OracleCommandcmd =New OracleCommand(SQL, conn))
        {
Conn. Open ();
            OracleDataReaderreader = cmd. ExecuteReader ();
             while(reader. Read ())
            {
                stringid = (reader. GetValue (0)?? String. Empty). ToString ();
                stringname = (reader. GetValue (1)?? String. Empty). ToString ();
                Console. WriteLine (String. Format ("id={0}, Name={1}", ID, name));
            }
        }
    }
    Console. ReadKey ();
}

Where the connection string is configured in the "app. Config" file, as follows:

<connectionStrings>
<Add name="ConnStr" connectionString="Data source=127.0.0.1; User Id=suzy; PASSWORD=ORCL;" ProviderName="Oracle.DataAccess.Client"/>
</connectionStrings>

Then, set the target platform for the project to "x64", or it will fail to run. Of course, if your machine is 32-bit, you should choose the "x86" platform.

11. The result of operation is as follows:

At this point, use. NET application to connect to Oracle is the general step. Of course, Oracle also provides a library of related libraries that support entityframework, and interested students can search for them at Oracle's website.

Reference documents

• http://ora-12xyz.com/error/ORA-12504
• Http://gerardnico.com/wiki/database/oracle/listener.ora
• http://blog.csdn.net/lpftobetheone/article/details/11099785

Use. NET applications connect to the Oracle database