How to Configure Oracle for Testing with Visual Studio

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”.

Beware!

  • 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

See: Understanding Network Configuration

The default Installation only creates a Listener listening on “localhost”.

Executed on the server:

  • TNSPING localhost shows:
    OK
  • 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:

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)
    Ex: VM01_ORCL
  • 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 Database

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 11.2.0.2.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.

About Peter Meinl

IT Consultant
This entry was posted in Computers and Internet and tagged , , , . Bookmark the permalink.

3 Responses to How to Configure Oracle for Testing with Visual Studio

  1. I could set up a connection in the server explorer, but not in the entity data model wizard. I tried pretty much all permutations and combinations with and without VS 2010 SP1

    Here is my configuration:
    OS: Windows 7 32bit / Windows 2008 R2 64 bit
    Oracle Server : 10g R2
    Visual Studio 2010 Ultimate (with and without SP1)

    Can somebody please help me towards a solution.

    How can I use oracle connection from entity data model wizard, is there a work around.
    Thanks a million in advance.

  2. Pingback: Blog J.Schweiss | How to Configure Oracle for Testing with Visual Studio

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s