Frans,

Firstly, long time no talk . I've been playing around with the new beta--very nice work! I hope you're getting some vacation time in now and then

Is there any way that I can generate a data layer off of an Oracle 10g Spatial schema? Obviously normal, non-spatial tables present no issues. However any table that contains a an Oracle SDO_GEOMETRY data type will have no entity generated for it. Is there a way I can accomplish this with a custom type converter? The only other option I have come up with so far is to create a view that excludes those columns--this will work for many of my scenarios but there are still a significant number of scenarios in which I need to retrieve that spatial data in order to integrate with a GIS application. Any ideas/recommendations would be highly appreciated.

Thanks, Larkin

Would those SDO_GEOMETRY columns be mapped to .NET Byte[] ?

As a workaround, would you try to use a dummy schema where you have BLOB columns instead of the SDO_GEOMETRY columns, then create the LLBLGenPro project against this dummy database and try to use it against the original spatial database.

I have the same problem with llblgen when the datasource is Oracle 10g Spatial. This makes refreshing catalog a headache . Any solution to that from llblgen?

You may try and build a custom driver. You can start with the Oracle 10g driver source code. (available in the SDK package).

erdogdum wrote:

I have the same problem with llblgen when the datasource is Oracle 10g Spatial. This makes refreshing catalog a headache . Any solution to that from llblgen?

I modified the Oracle 10g driver sourcecode and adding a mapping for the type "SDO_GEOMETRY" to map to "System.Byte[]". I don't know if this will work if you intend to actually supply that field data to an application, such as a GIS viewer, but I merely did this so that the designer would make that table available to map an entity to. Then I simply used the designer to remove the mapping to the Geometry field as I would not want that returned in most queries anyway--when I need that field I just use a stored proc.

To be more specific, in the Oracle 10g driver code, open the project and edit the OracleDBDriver.cs file:

In the OracleDbTypes enum, add "Geometry" just above the "AmountOfOracleDbTypes" member.

In the FillDBTypeConvertArrays() function, add the following line of code to the end of the code block that populates the DBTypesAsProviderType array:

base.DBTypesAsProviderType[(int)OracleDbTypes.Geometry] = OracleDbType.Blob.ToString();

Finally, in the String2DBType() function, add the following code to the switch statement:

case "SDO_GEOMETRY":
                    toReturn = (int)OracleDbTypes.Blob;
                    break;

Build the project, copy it to the Drivers\Oracle10g folder in the LLBLGen Pro directory (might want to make a backup of the shipped assembly first) and then restart LLBLGen Pro. You should now see the table containing the SDO_GEOMETRY column in the dialog of available tables to map entities to.

Hope that helps!

• Larkin

What I now wonder is: what does the ODP.NET datareader return for .NET type for this geometry type? A byte array or a custom type? Earlier ODP.NET versions simply crashed when you fetched a row from a table with a non-standard type.

Otis wrote:

What I now wonder is: what does the ODP.NET datareader return for .NET type for this geometry type? A byte array or a custom type? Earlier ODP.NET versions simply crashed when you fetched a row from a table with a non-standard type.

It returns a byte array as far as I recall--but the only fetching of that data type for our application is through a custom oracle driver used by the GIS software.

Ok, then you're fine with the driver modification

Walaa wrote:

You may try and build a custom driver. You can start with the Oracle 10g driver source code. (available in the SDK package).

We have no SDK package? Where can i find it?

erdogdum wrote:

Walaa wrote:

You may try and build a custom driver. You can start with the Oracle 10g driver source code. (available in the SDK package).

We have no SDK package? Where can i find it?

You can find it on the downloads page on the LLBLGen Pro web site--granted that you have purchased a license for the product, of course.

jookyone wrote:

erdogdum wrote:

Walaa wrote:

You may try and build a custom driver. You can start with the Oracle 10g driver source code. (available in the SDK package).

We have no SDK package? Where can i find it?

You can find it on the downloads page on the LLBLGen Pro web site--granted that you have purchased a license for the product, of course.

Thanks guys. I download the sdk and modified the oracle driver. Everything seems fine.

Has this been working well for those that implemented it? i am having the same issue and creating views just to drop the sdo column is getting old. Do you guys intend to include this dataype and mapping in your enumeration. What was the best solution for those that got this working?

VBAHole22 wrote:

Has this been working well for those that implemented it? i am having the same issue and creating views just to drop the sdo column is getting old.

so... are you suggesting we should simply add support for it, even though it's not possible to fetch the data? 'getting old' is what it is, the type isn't supported, neither are Java based types or PL/SQL based types other than default Oracle types.

Do you guys intend to include this dataype and mapping in your enumeration. What was the best solution for those that got this working?

We're not planning to add it as it's not a core type supported by ODP.NET / MS Oracle. Either these providers crash or throw an error. If they would allow it and accept for example parameters with data and have support for the type, no problem. That doesn't seem to be the case however.

The driver is available to you in sourcecode. If you want to ignore columns of sdo types, no problem, alter the driver code. However keep in mind that you aren't able to save data in these tables.

In the latest 11g provider, oracle has added support for .net clr types, however this isn't really going to help in this case either.