DSXchange

Hi,

I am trying to view data from Oracle OCI8i stage and receiving "ORA-12154 TNS:Could not resolve service name" error.

Details:
server job reading from a sequential file and writting to a Oracle OCI8i stage. I am using environment variables for Oracle user id, password and connect string. When I try to view data job parameters window pops up. I have added the environment variables to the job and define $PROJDEF as the default values. so when the pop-up comes I am just clicking OK. But this is resulting in error. If I enter the actual values for the parameters here in the parameter window it works fine. The datastage server and oracle are installed on the same windows server.

Also, when I was looking for solution on this problem there was many suggestions to use tnsping from datastage admin to check for connectivity. When I tried from DataStage admin -> command and executed tnsping <servername> it did not work. I have received message "TNSPING not in VOB". Any help is appreciated on these issues.

Thanks,
Srinivas

Please add the database entry in tnsnames.ora file,Please send email to DBA,he will add it.
Thanks

Please note I am able to view the data by manually entering the parameters instead of defaulting to enviroment variable values. Also, I have added and verified that a database entry exists in tnsnames.ora file.

csri wrote:Hi,

Also, when I was looking for solution on this problem there was many suggestions to use tnsping from datastage admin to check for connectivity. When I tried from DataStage admin -> command and executed tnsping <servername> it did not work. I have received message "TNSPING not in VOB". Any help is appreciated on these issues.

Thanks,
Srinivas

[/quote]

I missed to mention that I also tried this command and it returned saying this command do not avaible in this environment. I guess "SH" is for UNIX environment.

Just login your Windows server box and from the command prompt, e.g. C:\, issue the tnsping "service_name".

try importing table definitions using the plugin ....if u can see the DSN of urs....then its for sure that there is no issue with tnsnames.ora file...if not post the tnsnames.ora file ...we will take care

Hi

You please first check TNSNAMES.ORA in ORACLE_HOME\NETWORK\ADMIN if it is windows.
You can find Alias for the Service_Name or SID in that file.
while connecting to ORACLE at Database source name : Give the alias namebut not the actual service name/sid.
example:

ECUST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ECUSTOMS)
)
)
here use ECUST which is Alias name you need to use.

You guys need to read the thread and not just jump onto the end, trying to solve a problem that doesn't exist.

csri wrote:Also, when I was looking for solution on this problem there was many suggestions to use tnsping from datastage admin to check for connectivity. When I tried from DataStage admin -> command and executed tnsping <servername> it did not work. I have received message "TNSPING not in VOB". (sic) Any help is appreciated on these issues.

So I assume this was an academic exercise as your connectivity was just fine. As noted, "tnsping" is an Oracle command line utility, meaning you run it from your operating system's command prompt, not from DataStage. For Windows that means "Start -> Run -> "cmd" and then typing "tnsping <instance>" from the DOS prompt. And note this will only work if you A) have $ORACLE_HOME/bin in your PATH or B) "cd" to that directory first.

csri wrote:If I enter the actual values for the parameters here in the parameter window it works fine.

What exact 7.x version are you running? I'm wondering if it is an older version since you're still using the OCI8 stage. And I seem to recall that older versions may not fully support the $PROJDEF (etc) concept.

Also, does the job run using the environment variables and it's just "view data" that doesn't work or do they not work at all for you?