.NET Stored Procedures: Autogenerating INSERT Statements for rows in Oracle Tables - Deploying and testing the Oracle based .NET CLR stored procedure using Visual Studio.NET
(Page 5 of 5 )
Once you complete all of the steps in the previous section, proceed with the following steps to deploy the .NET CLR based stored procedure using Visual Studio.NET.
- Press Ctrl+Alt+L to open “Solution Explorer.”
- Right click on “OracleSample” project and select “Deploy.”
- Skip the welcome screen by clicking on “next.”
- Select the “data connection” you already created (you can even create a new connection by clicking on the “New Connection” button) and click “next.”
- In the next screen select the “Copy assembly and generate stored procedures” option and click “next.”
- Accept the defaults in the next screen and click “next.”
- Accept the defaults in the next screen and click “next.”
- Select all checkboxes in the “Available methods,” select “External” for security and click “finish.”
You need to select “External” as part of security, because we are trying to access “something outside” of the Oracle database. And thus it is considered to not be “safe.” This is a bit different from what we saw in my first article.
I already introduced testing the stored procedure from within the Visual Studio.NET environment in my previous articles. To test the above stored procedure, I just proceed with iSQLPlus.
Open any browser and go to “http://server:port/isqlplus”. Provide the “Username”, “password” and “connect identifier” according to your configuration and click “login.”
Once it opens workspace, just type the following code and press “execute.”
Begin
GenerateINSERTcommands(“select * from
emp”,”emp”,”c:\sample.txt”);
End;
That’s it. Actually, the above three lines are nothing but some simple PL/SQL statements, generally used to call (or execute) any stored procedure stored in the Oracle database.
To know and work with “OracleConnection”, “OracleCommand” and other objects, I suggest you go through my series on “ODP.NET with ASP.NET” within this website. Other enhancements to this solution would be something like “sending the filename as parameter”, “working with multiple columns”, “working with delimiters” and so on. You can download the entire source code (Visual studio.NET) solution at the beginning of this article. I leave it to the programmers for further enhancements. Any doubts, comments, suggestions, bugs, errors or feedback are welcomed at jag_chat@yahoo.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
