DSXchange

Hi
I have a simple problem. I am trying to load memo_line_id from a oracle table into a hash file. The following is the user defined sql in oracle stage.
SELECT Attribute1, MEMO_LINE_ID FROM Apps.AR_MEMO_LINES_VL

Attribute1 is the key in hash file.

The view data on the stage gives data source is empty. The same query fetches data in toad. I have tried all different possibilities but the same message is displayed. Can someone describe what is happening.

I have noticed that the AR_MEMO_LINES_VL is a view and not a table. Cant we obtain data from views.

babbu9 wrote:Hi
I have a simple problem. I am trying to load memo_line_id from a oracle table into a hash file. The following is the user defined sql in oracle stage.
SELECT Attribute1, MEMO_LINE_ID FROM Apps.AR_MEMO_LINES_VL

Attribute1 is the key in hash file.

The view data on the stage gives data source is empty. The same query fetches data in toad. I have tried all different possibilities but the same message is displayed. Can someone describe what is happening.

Views/tables, doesn't make a difference.

If the same query in Toad returns data, you need to verify the connection string. Make sure you're hitting the right instance, because your Toad connection on your PC could be using a different tnsnames.ora file than the DS server.

Thank You for the reply. How do I change the connect string of datastage so that it can pick the data as toad does.

kcbland wrote:Views/tables, doesn't make a difference.

If the same query in Toad returns data, you need to verify the connection string. Make sure you're hitting the right instance, because your Toad connection on your PC could be using a different tnsnames.ora file than the DS server.

Hi
Thank you for the reply for my post. I have tried to figure out the connection string problem but could not troubleshoot it.

View data works fine with all other tables except for 2. Do you have any suggestions to correct the problem.

Please inform.

You fully qualified the schema name, so you can't be getting fouled up with a local vs public synonym issue. I can't really help you much more, maybe someone else has an idea.

No, not really.

All things being equal, if "the same query in TOAD returns data" and DataStage says that the "data source is empty" then you are not connected to the same Oracle instance. You need to figure out why that is the case - check what you are using for Database Source Name / User ID / Password in the OCI stage versus Database / User Schema / Password in TOAD.

If they seem like they are the same, then as Ken noted the difference can lie in your tnsnames.ora files - TOAD uses one on your PC and DataStage uses the one in the $ORACLE_HOME specified in the dsenv file. Look at each entry and the problem should be revealed. Keep in mind the fact that the connection name can be the same in both but they could connect to two different instances under the covers.

Perhaps your DBA could help trouble-shoot this with you?

Thank you, I will look into it.

Hi,
I would suggest you to try column generated sql instead of user defeined sql and then view data in source.
Select all the columns when you view data in source.


Ketfos

I believe it is more to do with the connection via DataStage Server's tnsnames.ora than with the table / schema / field defn.

It will be wise to try sqlplus user/pass@ora_sid from the DataStage server machine in order to ensure the connection. Also look for the corresponding Oracle_SID entry in tnsnames.ora with the host ip-address associated with it.