.NET Stored Procedures: Autogenerating INSERT Statements for rows in Oracle Tables

(Page 1 of 5 )

In this article, I shall introduce you to developing a .NET based CLR stored procedure, which can generate INSERT commands automatically for every row in a specified SELECT statement against an Oracle database.

---

A downloadable file for this article is available here.

---

Preparing before developing a .NET CLR stored procedure

I already introduced .NET based stored procedures in Oracle databases in my article ".NET CLR stored procedures within Oracle database: Another breaking revolution."   If you are very new to this concept (or having trouble working with the Oracle wizard in this article), I suggest you go through that article before proceeding further.

Before proceeding further with this article, make sure that all of the following software is properly installed and configured on your system:

• Oracle database 10g Release 2 database server (or Oracle v10.2 client connected to another Oracle 10.2 database server) available at http://www.oracle.com/technology/software/products/database/
  oracle10g/index.html
• Microsoft Visual Studio.NET 2003 Enterprise Architect
• Oracle Developer tools for Visual Studio.NET 10.2.x.x (freely downloadable at http://www.oracle.com/technology/software/tech/dotnet/odt_index.html)

In some real scenarios, we may need to provide INSERT commands to the customer, to have some default rows to be inserted into some of the tables.  If we have rows in an Oracle table, and we would like to generate INSERT commands for all of those, it would be very difficult to achieve (unless you are an expert in PL/SQL).  In this article I dare to achieve it, but by using a .NET stored procedure.

The .NET stored procedure that we are going to develop in this article will create a text file and place all auto-generated INSERT commands automatically.  It basically accepts any SELECT statement for fetching all the rows.  We also need to provide the table name on which the INSERT command should be based.  And finally we provide a filename (along with the path) to hold all the INSERT statements.  But you must make sure that the file is generated only at the server (unless you provide a network shared path).

As this is a demonstration, I simply worked with the table “emp” which relies on the SCOTT schema.  My entire source code focuses on the “SCOTT” schema.  I suggest that you make the necessary changes to the source code, if you create (or modify) the table in a different schema.

I assume that you have already connected to the Oracle database using “Oracle Explorer” using Visual Studio.NET 2003.  If you are not quite familiar with “Oracle Explorer,” you will want to refer to my article linked several paragraphs above.

Now we shall proceed to the next section, which deals with creating the stored procedure.

Next: Developing Oracle based .NET CLR stored procedure using Visual Studio.NET >>
 

More .NET Articles
More By Jagadish Chaterjee

Comments On: .NET Stored Procedures: Autogenerating INSERT Statements for rows in Oracle Tables

· Hello guys, this is my second article on .NET CLR stored procedures in Oracle. Enjoy...   >>> Post your comment now!