Re: Reverse Engineering and selecting specific tables

On Wed, 02 Nov 2005 00:27:31 -0800, Peter Nolan wrote:

> Hi Hans,
>
> "The biggest benefit of going to the underlying table is to need to
> check
> whether Oracle has changed the table definition or even table purpose -
>
> each and every time they issue a patch."
>
> I respectfully disagree.

With all due respect, in return. Responses embedded ...

>
> 1.
> In the case I used as an example (HZ_PARTIES) rows in this table are
> exposed via a number of views.....if we take from the views we must
> re-integrate that data in the DW should we implement a similar concept
> of a 'party'.....this takes quite a bit of extra code (a new ETL job
> per view) and we need to be very careful about how we perform that
> re-integration......If we take from the underlying table we need only
> write one job and we can expose the various views of HZ_PARTIES using
> the same logic as OAP uses...

Oracle tests their views. They place guarantees on them and clearly state 'use this and not the underlying table'.

The intent is to use the views as published APIs. You are asking if it is OK to bypass the published APIs.

Best thing to do is ask the specific question of Support via a TAR.

>
> 2. True. Oracle may change underlying tables release to release.....
>
> However, on my last OAP project where we saw the notes written in
> reports from a change from 10.7 (I think it was) to 11i I saw extensive
> comments in the sql about how the reports had to be changed due to
> changes in the views too.
>

10.7 to 11.0 to 11i (11.5) is a rather dramatic set of upgrades. It is not surprising that much needed to be changed, especially when you consider the chyange of the first number indicates a fundamental change in structural philosophy. Such upgrades are never to be taken lightly.

We are, I hope, discussing changes between 11.5.x and 11.5.y and the last time I saw the TRMs, Oracle was pretty clear about their intent on APIs.

> It did not seem to me that the client was spared too much pain by
> reading through the OAP views. Though I would agree the client would
> have likely had more pain (how much I would not know) had they written
> reports directly off tables.
>

Oracle does provide the supporting documentation. If you bypass the APIs, you have to reverse engineer them EVERY PATCH RELEASE to ensure your application is stable.

>
> 3. (And since I am actually a consultant it would indeed be in my best
> personal interest to take data from views because that would generate
> more work during development......there is no job security for a
> consultant....but I am glad you were amused...;-) )
>

In fact I have seen stuff like this used by consultants to ensure their contracts are extended as "no one else knows how to do it" and "it takes a lot of detailed effort that we can't afford to hand off to our staff".

Apologies if this broad-brush caused by 'unscrupulous' consultants rubs you the wrong way. (And realize that I am a consultant as well.)

> My concern is what would be the most cost effective approach for my
> client over the 2-4 year time frame which will include upgrades to
> Oracle Apps.
>

Another issue is whether using the underlying tables is a violation of the support agreement.

> As I said, I would be interested in hearing other peoples opinions on
> this... :-)

And I am only one voice. I would appreciate hearing others as well.

>
> Best Regards
>
> Peter
> www.peternolan.com

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting guarantees I will not respond further ***