Linked Oracle View Problem

[ctsang]

Hi all,

Quick (and I hope easy) question. I am trying to link a view in an Oracle 9i database in an Access XP application. The link works fine and I can view the data without any issue. The problem though is that the data between what I am seeing in the Oracle view and the linked view through Access is different! Is there any explanation for this problem? Let me know if you want to know any specific details regarding the data differences.

In case it matters, in the Oracle view there is some manipulation of the data in certain columns (e.g. using the SUBSTR function to parse out pieces of certain fields etc.).

Thanks in advance,
Charles

 

[PHV]

Can you be more specific on the diferences ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ctsang]

Sure thing. Imagine I have a view with these values:

column A column b column c column d
fruit abc_mm abc mm
fruit abcd_mme abcd mme
meat a_ab a ab
meat 1234123_123123 1234123 123123
bread 123_321 123 321

So essentially in this view I am parsing the information in column b using the underscore as the delimiter. The view works perfect, the data is parsed properly and looks absolutely correct.

(FYI in the actual view column B does not appear, I simply chop up the values and display on the parsed information in addition to column A)

However in Access when I open the linked view I get this:

column A column c column d
fruit abc mm
fruit abc mm
meat a ab
meat a ab
bread 123 321

I hope the example is not too abstract however this is the best I can do without posting the actual information. The reality of this problem is that the information for the parsed or "chopped" fields is not displaying properly. I am wondering why this is happening? All the data manipulation of the columns is done in the Oracle view. I imagined when you use Access to link to it, it would only pull the already manipulated data.

Thanks. Please let me know if I am confusing anyone or if I need to clarify something.

 

[PHV]

It's just because the lack of primary key or unique index (even virtual) in the access linked table to help access uniquely identify each record.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ctsang]

Ah, thank you for your help. I didn't realize I had to do that. Thanks a lot!

Charles