Oracle support in development

Oct 31, 2007 at 2:57 PM
I just wanted to let the community know that development for Oracle support is underway.

I worked with contributor yogz on the Oracle version of the Data Access Guidance Package in the earlier version of the Web Service Software Factory. Based on this earlier work, I have started working on porting and enhancing that work within the Repository Factory. I have not yet checked in my code but that is forthcoming.

If anyone is interested in helping, please let me know as there are plenty of areas that can be worked on. I am tackling a very basic subset only at this point.

Cheers,

Philippe
Nov 12, 2007 at 9:23 AM
Philippe,

Can you give an estimation when Oracle support will be available? Is there already something in the sourcecode tree?

Thanks,

Niels
Nov 12, 2007 at 7:49 PM
Hi Niels,

Yes there is already quite a bit of code in the source tree. The schema discovery code is complete (I ported it from the earlier work that I'd participated on with the Web Service Software Factory). Here is what is left to do:

  • For the repositories, create Oracle-specic T4 templates, or modify existing T4 templates to support SqlServer, Oracle, and ODP.NET providers
  • Create T4 templates for the Oracle package/stored procedures, taking care to strongly type the REF CURSORS with recordtypes (this is essential for the data repository mapping of BEs to REF CURSOR columns)
  • Review UI changes as it relates to IN, OUT, and IN/OUT parameters. I had modiifed the code in the previous Data Access Guidance Package, and I expect I will need to do the same here.
  • Test, test, test!

This being said, I am going to need a few weeks because I have a number of time constraints.


nielss1409 wrote:
Philippe,

Can you give an estimation when Oracle support will be available? Is there already something in the sourcecode tree?

Thanks,

Niels

Jan 7, 2008 at 3:21 PM
I wanted to let everyone know that I just got to a good place. I'm to the point where the selection factory now processes REF CURSORS correctly. Both System.Data.OracleClient and Oracle.DataAccess.Client are supported. This is a big milestone, but by no means is the Oracle support completed. This is a fairly large effort, and I'll take this opportunity to renew my call for help. Though with the ADO.NET Entity Framework coming out later this year, I am now wondering is there really any interest left in having Oracle support within the repository factory.

Any feedback?
Jan 8, 2008 at 9:45 AM
Hi Philippe,

Since we (and I guess a lot more people) have the need for direct Oracle support in the Repository Factory, where we now have to jump through hoops to get it to work, we're definitely interested in your work. Unfortunately I don't have the time currently to assist you, but I am willing to assist in testing. Do you have a stable version available?

Thanks,
Robert
Jan 8, 2008 at 10:50 PM
Robert,

Thanks for your offer. I am definitely interested in testing help. That will be great. I am in the middle of making some enhancements to support children objects and how those are mapped into typed cursors. Let me get this in first before I ask you to test. I want to make sure I have the GetOne, GetMany, and GetAll operations well covered before I ask you to test.

If there are any takers, here are other areas that could be developed in parallel:

  • InsertOne T4 template modifications to support Oracle
  • UpdateOne T4 template modifications to support Oracle
  • DeleteOne T4 template modifications to support Oracle
  • Create CRUD stored procedures template for Oracle (I have a sample Oracle package and procedures I have been using and I can include that as a model in the source code tree)

Thanks to all who have helped to date. I would not be this far without them.

Cordially,

Philippe
Jan 31, 2008 at 5:34 PM
Hi Philippe,

after being stopped during my first steps with the repository factory by the missing Oracle support I started searching the discussions on a wild guess.

Wow, great. Yes, I am still interested.

Unfortunately I want to repeat the nagging question: how for to go still? I do not have time in abundance (who does?) but would be willing to help; although I am an experienced .NET developer I have no experience with Guidance Packages or Factories. Is the useable code already?

Thank you,
Karl



Philippe wrote:
I wanted to let everyone know that I just got to a good place. I'm to the point where the selection factory now processes REF CURSORS correctly. Both System.Data.OracleClient and Oracle.DataAccess.Client are supported. This is a big milestone, but by no means is the Oracle support completed. This is a fairly large effort, and I'll take this opportunity to renew my call for help. Though with the ADO.NET Entity Framework coming out later this year, I am now wondering is there really any interest left in having Oracle support within the repository factory.

Any feedback?

Mar 18, 2008 at 7:48 PM
Edited Mar 18, 2008 at 7:54 PM
A number of you have asked about the status of the Oracle support in the Repository Factory. Some of you have offered to test, and I even got a proposition to help develop (much appreciated). I believe that completing the development activities related to Oracle support (in C#) is within reach, so let me qualify what is supported with the latest version of the source code:

  • Repositories:
    • Code generation is in C#.
    • Support for both Microsoft and Oracle data access assemblies: System.Data.OracleClient and Oracle.Client.DataAccess. The determination of which assembly to use and code to generate is predicated by the connection string’s providerName attribute.
    • Support for Oracle REF CURSOR types. The code generation is dependent on having strongly-typed cursor definitions through the use of Oracle packages. I describe this at the end of this post.
    • GET ONE and GET ALL operations are tested.
    • GET MANY should work though I have not tested it as thoroughly as GET ONE and GET ALL.

Here is what is left to do:

  • Repositories:
    • INSERT, UPDATE, and DELETE operations.
  • Oracle package / stored procedure code generation.

Those of you who contacted me directly (you know who you are) will get an email from me to discuss these items and how to complete them.

Finally, let me address the Oracle REF CURSOR support requirements. The Oracle package specification must strongly type cursors for the code generator to be able to figure out the columns contained in the cursor. The sample below shows the structure of the package specification.


PACKAGE "HR".PKG_JOBS AS

-- Declare the cursor so that we can strongly type
-- the cursor variable that will be returned to the calling application.
CURSOR cur_jobs IS
SELECT JOBS.JOB_ID AS ID,
JOBS.JOB_TITLE AS TITLE,
JOBS.MAX_SALARY AS MAXIMUM_SALARY,
JOBS.MIN_SALARY AS MINIMUM_SALARY
FROM JOBS;

-- Declare the cursor type based on the cursor definition, assigning
-- the ROWTYPE to the return
TYPE jobs_refcur_type IS REF CURSOR RETURN cur_jobs%ROWTYPE;

PROCEDURE SP_GET_JOBS
(
P_CUR_JOBS OUT jobs_refcur_type
);

END PKG_JOBS;