Database.ca SQL Server Blog

My Blog

Monday, January 27 2014

Keywords: Oracle Projects, Visual Studio 2010, 2012, 2013, Debug Oracle Stored Procedures in VS, Oracle 11g, Oracle 12g, Oracle 12c

Manage your Oracle 11g and 12c Database Projects in Visual Studio 2013 like a Pro! Part 1 of 5

The blog is part of a series on Oracle Database Development with Visual Studio.
Read Part 2 of series here.
Read Part 3 of series here.
Read Part 4 of series here.
Read Part 5 of series here.
 
In this series we will:
 

  • Prepare your workstation for Oracle development with Visual Studio
  • Use Server Explorer to connect to Oracle server and to manage database objects
  • Create an Oracle project inside your Visual Studio solution
  • Import objects from your database into your Oracle project in Visual Studio
  • Add your database project to TFS or other source control software of your choice
  • Debug your Oracle Stored Procedures from within Visual Studio

Do not try the following exercises in a production environment.
 
Getting the environment setup so that you could start debugging PL/SQL with Visual Studio might not be as straight forward as debugging T-SQL, but it is quite feasible, thanks to Oracle Developer Tools.
 
There are some really powerful features in the newest Visual Studio tools released by Oracle on December 2013. As of this release the Oracle Developer Tools for Visual Studio supports Visual Studio 2010, 2012 & 2013, as well as Oracle 11g & 12c.
 
With that in mind, let's start our Oracle adventure:

 

1) I installed an Oracle 12c server on a dedicated server. Visual Studio 2013 will run off my workstation. You could be using Visual Studio 2010, 2012 or 2013, as well as Oracle Server 11g or 12c. This tutorial applies to all above, and will work in all of them.
 
2) I created an Oracle database called TESTDEBUG and a user called ramin.
 
3) Make sure the user has debug permission on the database:
 
   GRANT debug any procedure, debug connect session TO ramin;
 
4) I updated Visual Studio 2013 on my workstation with the latest Microsoft fix! (Update 1 at the time of writing).
 

 

Oracle Projects, Visual Studio 2010, 2012, 2013, Debug Oracle Stored Procedures in VS, Oracle 11g, Oracle 12g, Oracle 12c

5) You would need to install Oracle Data Provider for .NET. I have a separate article about this topic (Read!)...You will need to download a file called ODTwithODAC121012 from Oracle website. I was asked to create a free Oracle account first. You could download these files and more from the official oracle site.
 

 

Oracle Projects, Visual Studio 2010, 2012, 2013, Debug Oracle Stored Procedures in VS, Oracle 11g, Oracle 12g, Oracle 12c

6) Install above-mentioned ODT with ODAC file.
 
7) Creating TNSNAMES.ORA file in your Oracle client tools' Network/admin folder is no longer mandatory, but you could still do so and it will work.
With the new ODT you can now use EZ Connect and bypass those old-mentality .ORA files (about time!). In my tests I happily used Easy Connect.
 
Example of content of TNSNAMES.ORA file, if you still insist doing it the old way:
 
TESTDEBUG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.121)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDEBUG ) ) )

 

A wise and trouble-free way to create TNSNAMES.ORA would be to use "Net Configuration Assistant" installed by the installation of Oracle client on your workstation. If you use this app, select "Local Net Service Name configuration" to manage and actually test validate TNSNAMES.ORA entries.

 

8) Download the source code pertaining to this project, which could be found on the bottom of this page. Prior to unzipping the content of the file you must Right-Click on the file --> Properties --> Unblock, otherwise Windows will not trust the Dlls in the debug folder and it will bypass the PL/SQL code as you try to step into it.
 
There is also a .sql file to create the database objects inside an existing Oracle database. You need to run this script in your favorite Oracle client software, such as SQL Developer or Toad, in order to create the table and the stored procs we will be using for our exercises.
 
Please note that my sample example is of "Website" project type...you will notice that in the zip file I have two sub-folders. You need to copy the content of "Projects" sub-folder in my zip file to your Visual Studio "Projects" sub-folder, and then do the same for "Websites".
 
Launch Visual Studio 2013 and do Open Website and select OracleDebug website. Making sure the 'Debug' is the chosen configuration and not 'Release'. You see/select build configurations in the top toolbar.
 
Action Required: Now go ahead and open up Default.aspx.cs file and make sure String connectionstring variable's values are correct and pointing correctly to your database.
 
Oracle ODP.Net add oracle database project to Visual Studio Debug Stored Procedure
 
9) Right Click on the project --> Add Reference --> Extensions. Scroll down to letter O, and you will see that I had selected “Oracle.DataAccess version 4.x" (do not select the 2.x).

 

Oracle Projects, Visual Studio 2010, 2012, 2013, Debug Oracle Stored Procedures in VS, Oracle 11g, Oracle 12g, Oracle 12c

10) Note that in the ASP.NET project I used “ Oracle.DataAccess.Client” and not Microsoft’s deprecated System.Data.xxx.

 

Read Part 2 of series here.
Read Part 3 of series here.
Read Part 4 of series here.
Read Part 5 of series here.

 

 

 

Written by Ramin Haghighat