Database.ca SQL Server Blog

My Blog

Monday, November 25 2013

Keywords: Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

Debug Oracle 11g Stored Procedures with Visual Studio 2012

Feburary 2014 Update: I have written a separate new blog on how to debug Oracle 11g and 12c from Visual Studio 2013. You could find that here
 
 
Debug your PL/SQL code from right within Visual Studio as part of your journey to developing highly effective, bullet proof and stable database code. Do not try the following in a production environment for various reasons but namely because Oracle stored procedures need to be 'Debug Compiled' prior to debugging...more on that later.
 
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.
 
With that in mind, let's start our Oracle adventure:
 
 
Part A: Setting up the environment
 
 
1) I installed an Oracle 11g server on a dedicated server. Visual Studio 2012 will run off my workstation.
 
2) 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 2012 on my workstation with the latest Microsoft fix!
 

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

5) You would need to install Oracle Data Provider for .NET. I have a separate article to guide you a little bit on this topic (Read!)...You will need to download a file called ODTwithODAC1120320 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. Alternatively if you are in possession of Oracle client 11.2.0.4 you could run that setup and opt for 'Administrator' or 'Custom' options.
 
January 2014 Update: Oracle has since made available the new ODAC12* family of drivers on their download page as well. I was able to debug Oracle 11g with them, following the same exact procedures as described in this article, so they seem very much backward compatible at first glance and yes you could download them instead of 11* for purposes of debugging from Visual Studio. The new file is called ODTwithODAC121012. Note that 11.* drivers are still available for download on that site as well.

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

6) Install ODTwithODAC that you had just downloaded, choose “client” tools installation (and not the server) if prompted with the option.
 
7) Create TNSNAMES.ORA file in your Oracle client tools' Network/admin folder and provide the necessary information to point it to your Oracle database.
 
Example of content of TNSNAMES.ORA:
 
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.
 
January 2014 Update: If you install ODTwithODAC12* then you could bypass ORA files and use EZ Connect aka Easy Connect to directly connect to an Oracle server. I prefer this method.
 
 
Part B: Start configuring your project
 
 
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 2012 and open the sample project provided on the bottom of this page. Making sure the 'Debug' is the chosen configuration and not 'Release'. You see/select build configurations in the top toolbar. 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.
 
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, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

10) Note that in the ASP.NET project I used “using Oracle.DataAccess.Client;” and not Microsoft’s deprecated System.Data.xxx.
 
11) In Server Explorer, click on “connect to database” . Select Oracle Database and then “Oracle Data Provider for .NET”
 

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

12) You will then see ODP’s "Add Connection" window. If you had placed the TNSNAMES.ORA file properly, then you should see the name of your Oracle database in Data Source list. Input your database username and password, select "Save Password", and then click on "Test Connection" to make sure it connects successfully.
 
As usual, I stumbled over many ORA errors before I was able to successfully pass the connection test. Welcome to the world of Oracle!
 
I didn't take note of all the causes of errors, but I do recall that one of them turned out to be caused by the fact that I had copied and pasted an existing TNSNAMES.ORA into the new Network/Admin folder and the existing file’s NTFS permissions was transferred with the copy process, thus inhibiting it from being read properly in the new folder.
 
Lesson learned: do Not copy and paste TNSNAMES.ORA, create a new file instead.
 
Another problem I ran into was OracleMTLRecoveryService (Windows Service) had failed to start. The reason was HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\OracleMTSRecoveryService\Protid_0\Host had an incorrect domain name (mycomputername.olddomain.com), I changed it to mycomputername.existingdomain.com and that fixed the problem. We had changed the network domain recently.

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

13) Once the connection is made. Find GETSTRINGS stored procedure in Procedures, and right click “Compile Debug”. Remember to revert it back to a regular compile at the end.
 
14) Double click on "GetStrings" stored procedure, this will open the stored procedure’s script in VS. put some breakpoints…especially on those otherwise useless variables I purposely put in the code for the sole purose of seeing their value during debug.
 

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

15) Tools --> Options --> Oracle Developer Tools --> Check off the database connection you just created.

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

16) Tools --> Oracle Application Debugging --> make sure it is selected.

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

17) Open default.aspx.cs and place breakpoints on the line where command.executereader() is, and anywhere else you wish.

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

18) Debug --> Start Debugging. Press F11 to "Step into".
 
19) You could also debug the other stored procedures I had provided in my database sample, they perform Update, Insert and Delete.
 
20) Make sure the “Locals” window is visible during debug. You could also look at the "Watch" window and enter variable names there manually.
 
 
Feburary 2014 Update: I have written a separate new blog on how to debug Oracle 11g and 12c from Visual Studio 2013. You could find that here

 

Oracle, Stored Procedure, Debug, Visual Studio 2012, VS 2012, Oracle 11g debug

Written by Ramin Haghighat