Database.ca SQL Server Blog

My Blog

Monday, February 03 2014

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

Add ODP.NET to your Oracle 11g and 12c Database Projects in Visual Studio 2013. Part 2 of 5

The blog is part of a series on Oracle Database Development with Visual Studio.
Read Part 1 of series here.
Read Part 3 of series here.
Read Part 4 of series here.
Read Part 5 of series here.
 
11) In Server Explorer, click on “connect to database”. Select Oracle Database and then “ODP.NET Managed Driver”. Note that there is also an unmanaged driver.

 

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

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. Better yet, select EZ Connect from the Connection Type list box first. Then input your database username and password, select "Save Password", and then the rest of the information that EZ-Connect requires. No matter which approach you took, EZ-Connect or TNSNAMES, make sure you click on "Test Connection" to make sure it connects successfully at the end. Once it connect successfully, you are set to follow the next step.

 

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

13) Now we are ready to add a new Oracle project to our solution. This project type was added when we installed ODT.
 
In Solution Explorer, right-click on your solution and select Add --> New Project.

 

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

14) Select "Oracle" on the left pane, and then "Oracle Database Project" from the list on the right pane. Give a proper name to your Oracle database. Do not be too harsh when naming Oracle projects by naming them based on your sentiments about Oracle! In my example above, I named it OracleDBProject.

 

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

15) If you had completed step #12 successfully, then you should see the name of your Server Explorer connection in the above list. Select your connection and click OK.
 
Create Oracle Database Projects In Visual Studio 2012 2013
 
16) The Oracle project is successfully created. When you look at its structure carefully you will notice a few things, namely the fact that the folders are completely empty. The DDL definitions were not imported.

 

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

17) In Server Explorer, one by one right-click on each object type (ie Tables, Views, Stored Procs etc) that applies to your project --> "Generate Create Script to Project". You must have an Oracle project in your solution in order to be able to perform this.

 

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

18) Verifying the Tables & Procedures sub-folders in Solution Explorer now displays our objects. The DDL scripts have been imported into our project. But what good does that do? Plenty!
 
Create Oracle Database Projects In Visual Studio 2012 2013
 
19) Did you notice a "Scripts" sub-folder in Solution Explorer under the Oracle project? it contains some files with .sql extension. These files are basically log files of which objects have been imported into your project and when.

 

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

20) Let's modify one of our existing objects, by opening it from Solution Explorer, and save and promote this updated script to the actual Oracle server. Basically the reverse of the import process we just performed.
 
In Solution Explorer, go ahead and double click on GETSTRINGS stored procedure to open its definition script. Write some comment in it, as displayed in the picture above. Save and close the file.
 
Create Oracle Database Projects In Visual Studio 2012 2013
 
21) Always in Solution Explorer, right-click on GETSTRINGS stored procedure, and select RUN.
 
Create Oracle Database Projects In Visual Studio 2012 2013
 
22) Now back to Server Explorer...find and open GETSTRINGS by double clicking on it. You will see that it contains the comment you had placed earlier. It works like a charm! You just made changes to a stored procedure and uploaded it to the Oracle server with unprecedented ease!

 

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

23) There are various ways to create new objects in your Oracle database. Per example as the picture above displays, you could do so directly from Server Explorer window. But this was just for your information...do not do that yet. We will create a new stored procedure by different means in the next article..

 

Read Part 1 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