Data source empty
Moderators: chulett, rschirm, roy
Data source empty
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 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.
Re: Data source empty
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
How do I change the connect string of datastage
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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?
![Confused :?](./images/smilies/icon_confused.gif)
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.