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.
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.
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.
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.
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.
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.
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.
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!
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.
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.
21) Always in Solution Explorer, right-click on GETSTRINGS stored procedure, and select RUN.
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!
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..
Written by Montreal DBA Team
Download the scripts for this article (Prior to unzipping the content of the file you must Right-Click on the file --> Properties --> Unblock)