Every time I was setting up Oracle for testing (without having a DBA around) I got stuck somewhere and wasted a lot of time troubleshooting – even started to dislike Oracle for that.
With MS SQL Server I have less problems configuring it. See How to Configure MS SQL Server for Testing with Visual Studio. But SQL Server tends to drive me nuts, locking itself to death when using it’s default pessimistic concurrency model and not row-versioning 🙂 (see Know your Database System!).
To save you and me this frustrating experience next time, her are my steps to set up Oracle for testing with Visual Studio:
- Install Oracle database with default DB instance ORCL in a VM
- Create a Listener on Server for remote access to ORCL
- Open Listener’s Port in Server Firewall
- Install ODAC and Tools for Visual Studio on dev Workstation
- Create Data Connection in Visual Studio Server Explorer
- Create test user and database
- Use Oracle via ADO.NET
- Use Oracle via Entity Framework and the Oracle EF data provider
Install Oracle database in a virtual machine
Ex: Host DNS name= VM01
It often happened to me that uninstalling Oracle did not work and I was left with a garbled Oracle Installation on dev machines.
The Oracle Installer told me to use a command line tool for uninstalling and I was unable to determine the input values for this tool.
To force an English version to be installed (with English error messages!) set the Windows locale to English during installation. The Oracle setup parameters and options to select the language did never work for me.
Let the installer create it’s default database instance with SID “ORCL”.
- Oracle does not accept some special characters like “+” in the password.
- Oracle GUI Tools sometimes show behind other windows
In case you do install on the dev workstation:
- Beware! If you choose a drive different from your OS Drive
this drive will be included in your Windows Backup Images, which might make than larger than you like.
- Better stick with Oracle’s installation folder default c:\app
to avoid confusion with later Oracle installs
Create Listener on Server for Remote Access
The default Installation only creates a Listener listening on “localhost”.
Executed on the server:
- TNSPING localhost shows:
- but TNSPING shows error:
TNS-12541: TNS:no listener
Create a Listener service using “Oracle Net Manager”.
- The Listener name does not matter.
- You must select a Port different from the default 1521
ex: Port 1522
- Add your database instance, ex: the default ORCL, to the Listener
- Don’t forget to “Save Changes”!
If you later change the Listener config, you must restart the Listener Service, for your changes to take effect, using Computer | Manage | Services.
I your database is not added to the Listener, trying to connect to the database will throw:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Install ODAC and Developer Tools for Visual Studio on Workstation
Install Oracle Data Access Components and Developer Tools for Visual Studio on your dev Workstation:
- With Oracle ADO.NET data provider Oracle.DataAccess.Oracle*:
ODAC 11.2 Release 3 (184.108.40.206.1) with Oracle Developer Tools for Visual Studio
- With Oracle EF provider:
ODAC 220.127.116.11.40 Beta 2 for Entity Framework and LINQ to Entities
To force an English version to be installed (with English error messages!) set the Windows locale to English during installation.
Optionally install Oracle SQLDeveloper:
- Should automatically be installed with Oracle 11g
But did not on my machines
- Do not unzip into a Oracle home folder!
Open Listener’s Port in Server Firewall
With the Listener’s port closed, executing on the client:
- TNSPING : shows error:
TNS-12535: TNS:operation timed out
Ex: TNSPING VM01:1522
To open the port, add an inbound port rule (ex: for port 1522) for Domain and Private.
Create Connection in Visual Studio Server Explorer
Add a Connect Descriptor in TNSNAMES.ora (here seems to be no way to use EZConnect naming instead):
- in C:\app\pm\product\11.2.0\client\Network\Admin
Copy the template from the \Admin\Sample to \Admin
- Add a Connect Descriptor for your database
<pre>(<ConnectDescriptorName>=</pre> (DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=<ServerDNSName>) PORT=<PortNumber>)) (CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME=) ) )
Ex: Connect Descriptor VM01_ORCL
VM01_ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = VM01)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) ) )
In Visual Studio add a Data Connection using Server Explorer:
- Select Oracle Database
- Select Data source name (=ConnectDescriptorName)
- Click “Test Connection”:
This should result in error: “Invalid username and password”
- Enter username SYS and the Password used at the Oracle Server Installation
- For this connection select Role: SYSDBA
- The connection name is not important (Ex: SYS.VM01.ORCL)
If you get a duplicates error, simply use a different name.
Create a Test Database
Create a test user and database using
- the VS Data Connection
right-click | Query Window
- or SQLPlus
- or SQLDeveloper
Create User (=Schema in Oracle)
For a minimal dev user:
CERATE USER test identified by ???; DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT DBA TO test;
Create a sample table:
CREATE TABLE inventories ( ProductName varchar2(20) PRIMARY KEY, QuantityInStock number NOT NULL , RowVersion number ) ; INSERT INTO inventories VALUES ('Hammer',100, null); INSERT INTO inventories values ('Nail',1000, null);
Use Oracle via ADO.NET
Create a project in VS
- Add reference to Oracle.Data.Access
Use the correct (newest?) Version!
Do not use MS System.Data.OracleClient (is marked as deprecated)!
- Use EZConnect connection strings.
Working with TNSNAMES is too cumbersome.
Many of the EZConnect strings formats found in the internet do not work for me.
Using cnx As New OracleConnection("Data Source=//VM01:1522/orcl;User Id=test;Password=???;") cnx.Open() Dim updCmd As New OracleCommand("update inventories set quantityinstock = quantityinstock + 30 where productname = :productname", cnx) updCmd.Parameters.Add("productname", "Hammer") If 0 = updCmd.ExecuteNonQuery() Then Throw New ApplicationException() End Using
Use Oracle via Entity Framework
A beta of the Oracle support for EF is now available:
ODAC 18.104.22.168.40 Beta 2 for Entity Framework and LINQ to Entities
Simply use an Oracle Connection instead of a SQL Server connection.
For optimistic concurrency checks use a number column, autoincremented via a custom trigger (ORA_ROWSCN seems to be not dependable!).
See DB Concurrency Control with .NET – Details.