ORA-12154 Error and Possible Job Corruption

durgaps · Post by **durgaps** » Tue Mar 06, 2007 11:06 pm

Job Design 1:

Seq_File1 ==> TRANSFORMER1 ===> STP(Stored Proc) ===> TRANSFORMER2 ==> Seq_File2

I was getting the following error.
Error: ORA-12154: TNS:could not resolve service name

Checked all the ODBC entries in the respective locations. They look ok as there are other jobs using the same connection and executing successfully.

Next, I removed the Seq_File1 stage and put a Row_Generator stage and passed the input values from Job Parameters window.

Job Design 2:

Row_Generator ==> TRANSFORMER1 ===> STP(Stored Proc) ===> TRANSFORMER2 ==> Seq_File2

Guess what, the job ran without any hitch. I still do not understand why and how it was aborting when the Seq_File1 stage was on. I ran this job (design 1) in <project1> multiple times and every time it aborted with the ORA error.

And one more thing is, I have the same job (design 1) in <project2>. It runs fine without any issues.

Any idea why this might be happening? Is the job corrupted? Has anybody faced these issues before?

Thanks,

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 11:19 pm

What information you pass from sequential file to STP stage?
What other information you get apart from this error?

durgaps · Post by **durgaps** » Wed Mar 07, 2007 12:35 am

From Sequential File, i pick up a column value and pass it to the Transformer and then pass it to STP.

Right now, I pass a value from Job Parameters to the column in Transformer. And it works fine.

kumar_s · Post by **kumar_s** » Wed Mar 07, 2007 12:38 am

So you should check the data value that been extracted from Sequential file.
Dont you get any other error message other that the noted one?

durgaps · Post by **durgaps** » Wed Mar 07, 2007 3:38 am

Thanks for the reply Kumar. I am afraid there is no other error message other than this. I have cross-checked the extracted value. No issues with it. Is it a possibility that the Stages can get corrupted?

kumar_s · Post by **kumar_s** » Wed Mar 07, 2007 4:38 am

There is no reason the Stage can get corrupted. But with different stage and different method you are passing a different value atlest by different means.
Are you trying to pass input parameter values through Sequential stage via Transformer? Can you check what is the difference between passing the value from File vs passing it as parameter. You can find it out by writing in to another sequential file. You can give both input as well as parameters into different fields and compare one to one.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 07, 2007 1:16 pm

Go back. The error is "TNS name can not be resolved". Work out why.

csrazdan · Post by **csrazdan** » Wed Mar 07, 2007 9:44 pm

Just that you know Stored Procedure stage used ODBC connection only for loading MetaData. When the job executes Stored Procedure stage uses OCI connection. What it means that the connect string which you enter in Stored Procedure stage should be available in tnsnames.ora file on the your DataStage Server.

Try connecting to Oracle from UNIX SQL prompt with your connect string before debugging your job.

Hope it helps....

kumar_s · Post by **kumar_s** » Wed Mar 07, 2007 9:59 pm

If the same jobs runs if the Sequential file is replace by Other stage, would reveal the fact that the tnsnames had that entry in the file.
Need to check, whats mystery with the earlier design.

durgaps · Post by **durgaps** » Wed Mar 07, 2007 10:20 pm

Hi Ray/Kumar,

Tried Kumar's suggestion. Both are having the same values. No change. (No space, No junk characters etc ).

Moreover I had the same job running successfully a week back with the same inputs. Now, with the same input, same scenario the job is failing.

Rather strange.

Thanks,

kumar_s · Post by **kumar_s** » Thu Mar 08, 2007 4:21 am

We are missing something here.
Let me confirm, the data passed either from Row Generator or Sequential file or data from Parameter is just the input argument to the Stored procedure called by the STP stage?
And there is not change in connection parameters, like Schema, Tablename, usernaname or password??
And there is tnsentry for the schema that you are trying to connect for all the time is available in Server tnsnames.ora file?
Was the above all true?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Mar 08, 2007 5:44 am

Do you have the job that fails?

Can you run it again and confirm the results?

Can you connect to Oracle from DS Server via sqlplus with same parameters like those to STP?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 08, 2007 8:27 am

durgaps wrote:Hi Ray/Kumar,

Tried Kumar's suggestion. Both are having the same values. No change. (No space, No junk characters etc ).

Moreover I had the same job running successfully a week back with the same inputs. Now, with the same input, same scenario the job is failing.

Rather strange.

Thanks,

So, what's changed?

Who changed the tnsnames.ora file?

durgaps · Post by **durgaps** » Mon Mar 12, 2007 3:17 am

Hi All,

'Yes' is the answer to all questions asked by Kumar_S & Sainath. I really dont have a clue as to what might be the reason for this. Please let know if anybody has any leads on this.

Thanks,

oacvb · Post by **oacvb** » Mon Mar 12, 2007 4:18 am

What are the info you are passing from Sequential File to Transformer and then to STP. Just curious, Are you passing any db details (Login, Password, Schema) from sequential file?