ORA-12154 Error and Possible Job Corruption
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 74
- Joined: Sat Jul 08, 2006 4:09 am
- Location: Melbourne, Australia
- Contact:
ORA-12154 Error and Possible Job Corruption
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,
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,
Durga Prasad
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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....
Try connecting to Oracle from UNIX SQL prompt with your connect string before debugging your job.
Hope it helps....
Assume everything I say or do is positive
-
- Participant
- Posts: 74
- Joined: Sat Jul 08, 2006 4:09 am
- Location: Melbourne, Australia
- Contact:
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,
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,
Durga Prasad
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?
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?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
So, what's changed?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,
Who changed the tnsnames.ora file?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.