Oracle View

We have JDEOW with AS/400 DB2 as its native DB. We have a remote Oracle DB. While JDEOW can access tables from the Oracle DB, we have not been able to have JDEOW access Oracle views. There is a view which was created in Oracle which presents data in a concise and logical manner very quickly (since Oracle performs all of the processing) and we need to access this from JDEOW. I've heard it can be done, but when I try to point my OCM to Oracle, I only see JDE-defined Oracle tables, not the views as well. Is there an exact procedure to follow? I'm not sure what we're missing. I'm new to OW, so the more detail the better. Thanks-in-advance

Hi riegel,
This topic was covered multiple times but I don't remember the subject/a useful keyword ... , but I know it is working, since I'd used it ... successfully
Shortly, Saad Ahmad Mian (Oracular Systems & Software, Inc.) says:
1 To Create a OW Table WITHOUT generating it; that is, you need the the OW Table_NAME only. The fields needed have to be PRECISELY the fields you already use within your Oracle View (my guess is you are using OW Data Dictionary Items, otherwise OW will get sooooo confused and will display the sooooo generic Invalid Memory error
2 The next step would be to CHANGE the name of your View; use the Table_NAME you just created (it has the worthy F... prefix!).
Good luck,
Adrian Chimirel
PS After some digging, here is the Source:
"For more information about this article contact Saad Ahmad Mian, Sr. JDE Specialist, at (920) 303-0470 ext. 207 or [email protected]"

And attached is Saad's document ...
Maybe we could add it to the Tips & Traps!

Thanks Adrian:

I had tried this process previously wihout any success.
OW would never see the ojects.
This time, I tried it using SqlServer 2000 as a test.
Everything worked as expected except, since I created the underlying table and view from SS's Enterprise Manager (first), it created them with an owner of "dbo".
Once I changed SS object owners to match the OW Database Data Source's userid, everything worked.
...Create the object in SS, create the spec in OW, set-up the OCM, and presto... OW thinks its a table when it really a view. No generation in OW needed at all (except for business views).

Thanks for your help.
...now on to Oracle and the "real' view I'm trying to access.

Hardly wait to hear you saying good things about Oracle, too.
Thank the JDEList (especially to Saad, whose valuable, should I say Oracular inputs are always welcome)!

FWIW

I have used Oracle Views before with great success.

James






To: James A. Wilson@ANDERSEN WO
cc:
[email protected] Date: 04/24/2002 02:47 PM
Sent by: Subject: Re: Oracle View
[email protected]


Message Mailed On Behalf Of ---> [email protected]


Please respond to jdelist








Thanks Adrian:

I had tried this process previously wihout any success.
OW would never see the ojects.
This time, I tried it using SqlServer 2000 as a test.
Everything worked as expected except, since I created the underlying table
and view from SS's Enterprise Manager (first), it created them with an
owner of "dbo".
Once I changed SS object owners to match the OW Database Data Source's
userid, everything worked.
..Create the object in SS, create the spec in OW, set-up the OCM, and
presto... OW thinks its a table when it really a view. No generation in OW
needed at all (except for business views).

Thanks for your help.
..now on to Oracle and the "real' view I'm trying to access.
--------------------------
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OW&Number=33367
+ - - - - - - - - - - - - - - - - - - - - - - - -+
This is the JDEList One World® / XE mailing list / forum.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found on the JDEList Forum at
http://www.JDEList.com

JDEList is not affiliated with JDEdwards®

+ - - - - - - - - - - - - - - - - - - - - - - - -+

I've been working with Oracle for 12 years.
SQLSever has come a long way, but I still have my preference... er.... bias.

<<Hardly wait to hear you saying good things about Oracle, too.>> is not my best ETL (English as a Third Language) phrase, that's true ...
Anyway, my expectations are 100% related to the "Using a Database VIEW within OW" issue, and not to the database, whichever that may be.
Hardly wait to hear you saying good things about making OW aware of the VIEW you generated in Oracle, too. How does this sound?
Adrian Chimirel
PS I can't say I have a preference, ... they are just tools, ... more or less expensive, therefore more or less attractive for our Clients.
And, if the Client uses a Klingon database, it is my task to make it (the Klingon database, not the client, ha ha) talk to OW, is it not?