Hi,

I have several tables for which I would need to map the Oracle ROWID pseudocolumn. It seems that this is not currently possible in LLBLGen Pro 2.5.

The reason is that I need to use the Change Notification feature of ODP.NET 10gR2.

This feature allows an application to register for notifications on table data changes. The notifications received by the application also include information about which rows were added/updated/deleted. Unfortunately, these rows are identified by rowid, not by primary key.

For more information about the Change Notification Feature see the following articles:

http://www.oracle.com/technology/oramag/oracle/06-mar/o26odpnet.html http://www.oracle.com/technology/oramag/oracle/06-may/o36odp.html

I found the following ugly workaround that allows me to access the ROWID pseudocolumn:

Let's say I have a table "person" with primary key "person_id". Then I do:

create or replace view person_row_id as select person_id, rowid row_id from person ;

and map the view "person_row_id" as an entity. Next, I can add a 1:1 relationship from person to person_row_id.

However, this is an ugly hack and requires an additional view per table.

Can you suggest a better/easier workaround or (preferrably ;-) add the capability to map rowids to the designer?

Thanks and best regards, Christoph

These are indeed not supported at the moment.

I can only think of a workaround like this (not tested, so it might be wrong): add a column to your table which is a computed column and has as value function the rowid of the row. That column will show up in the table and you can access it.

Hi Frans,

AFAIK the "virtual columns" feature has just been added to Oracle in version 11g (which is currently available for Linux only). Unfortunately, I am running 10g on Windows, so "virtual columns" are not an option.

Do you intend to add support for mapping Oracle ROWIDs in future LLBLGen versions?

Thanks and best regards, Christoph

cknittel wrote:

Hi Frans,

AFAIK the "virtual columns" feature has just been added to Oracle in version 11g (which is currently available for Linux only). Unfortunately, I am running 10g on Windows, so "virtual columns" are not an option.

I meant actually a 'calculated' column, similar to a total field which calculates Quantity * price in the same row I think that's possible in Oracle.

Do you intend to add support for mapping Oracle ROWIDs in future LLBLGen versions? Thanks and best regards, Christoph

This is the first time it's been brought up. If they're needed for normal software development, we'll add them in the future. (so if there's no other way to retrieve their values)

Otis wrote:

cknittel wrote:

AFAIK the "virtual columns" feature has just been added to Oracle in version 11g (which is currently available for Linux only). Unfortunately, I am running 10g on Windows, so "virtual columns" are not an option.

I meant actually a 'calculated' column, similar to a total field which calculates Quantity * price in the same row I think that's possible in Oracle.

I also meant a calculated column. These are new in Oracle Database 11g and are called "virtual columns" by Oracle, see e.g. http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/whatsnew.htm#sthref10

Otis wrote:

cknittel wrote:

Do you intend to add support for mapping Oracle ROWIDs in future LLBLGen versions? Thanks and best regards, Christoph

This is the first time it's been brought up. If they're needed for normal software development, we'll add them in the future. (so if there's no other way to retrieve their values)

That would be great, thanks! The ROWIDs are definitely needed for using the Change Notification feature.

Hmm, I didn't know they lacked that feature! (even access had it for years already ).

I've added it to the v2.6 shortlist.

Building this in requires a lot of changes. The main thing is that you definitely don't want is that every table gets its own ROWID field, which is then also automatically mapped in the designer.

You only want the ROWID column to added to selects in special areas, namely the ones where you need change notification. After the ROWID's are obtained, you then also need to refetch the entities based on a filter on ROWID.

You can do so in the code in v2.6: - inject a ROWID column in the fields collection for fetches, exactly the same as it's done in this ASP.NET example where a scalar query is injected in the entity fields: http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx - for fetching, use a predicate which has an entityfield created like: new EntityField2("ROWID", "<tablename>", typeof(string)) and that compared to the rowid. (or a field compare range).

Otherwise it's hard to build in. You can also use a simple predicate class derived from Predicate (similar to FieldCompareValuePredicate for example) which filters on ROWID...

I.o.w.: we didn't add this to v2.6, but hopefully with the above changes you can add change notification. After all, you also have to register the OracleCommand, so it already requires a derived class from DataAccessAdapter, an open connection etc.

Hi Frans,

Thanks a lot for getting back to me on this. That's great customer support!

I am currently using the ugly workaround I described in my first post.

I will try your suggestion as soon as I find some time and will let you know the results.

Best regards, Christoph