Hello friends,
I am getting a strange problem in one of the jobs of our project. The job has 9 steps (each step loads data into one table).
Step 1:
TABLE ==>TRANSFORMER==>ORA9i Stage==>SquentialFileStage
At the end of each step, we are updating the "CurrentExecuteDate" by using the following SQL:
select to_char((to_date(#CurrentExecuteDate#, 'yyyymmdd') + 1), 'yyyymmdd') from dual
The problem is sometimes the last step (updating the date) is failing with the error
"MERCHDMART1..TRANS1: ORA-12535: TNS:operation timed out"
Can anyone help me with this?
Thanks a lot in advance
Yamini
Oracle 9i Stage issues
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The SQL you posted won't update anything.
Try the oerr command (oerr ORA 12535) to get recommendations for this particular error.
I suspect it's something to do with locks on the table; you may still have a lock from the earlier cursor not released when you come to do the update. If you do the update in a separate job (controlled by the same controller) this may ameliorate the problem.
Try the oerr command (oerr ORA 12535) to get recommendations for this particular error.
I suspect it's something to do with locks on the table; you may still have a lock from the earlier cursor not released when you come to do the update. If you do the update in a separate job (controlled by the same controller) this may ameliorate the problem.
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.
No, dual is a perfectly valid 'pseudo' table and can be used in the manner that the OP showed. However...
I don't understand what 'at the end of each step' means and where exactly this SQL is being executed. And as Ray notes, by itself this SQL doesn't update anything. This seems like an awfully clunky way to accomplish something, even if I'm not quite sure what that something is yet.
Can you explain what it is you are trying to accomplish rather than how you are trying to accomplish it? That would help people here to be able to provide a better, more 'DataStage-centric' methodology for you.
I don't understand what 'at the end of each step' means and where exactly this SQL is being executed. And as Ray notes, by itself this SQL doesn't update anything. This seems like an awfully clunky way to accomplish something, even if I'm not quite sure what that something is yet.
![Confused :?](./images/smilies/icon_confused.gif)
Can you explain what it is you are trying to accomplish rather than how you are trying to accomplish it? That would help people here to be able to provide a better, more 'DataStage-centric' methodology for you.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi,
This is getting ridiculous. How can you claim that you have coded a "select to_char((to_date(#CurrentExecuteDate#, 'yyyymmdd') + 1), 'yyyymmdd') from dual" SQL to update the table???
How can you not know that "DUAL" is a pseudeo table and never used it?
Some people really need to take the Oracle SQL 101 class!
This is getting ridiculous. How can you claim that you have coded a "select to_char((to_date(#CurrentExecuteDate#, 'yyyymmdd') + 1), 'yyyymmdd') from dual" SQL to update the table???
How can you not know that "DUAL" is a pseudeo table and never used it?
Some people really need to take the Oracle SQL 101 class!
Oracle 9i Stage issues
Hello friends,
I might have lead you guys in a wrong way. By update I mean, at the end of each step (ORA9i Stage==>SquentialFileStage) I am executing the query to get a 'Date' and insert that value into the Sequential file. My intension is when I run the job the next time it extracts data based on the data present in the Sequential file
Am I doing anything wrong?
Thanks a lot in advance
Yamini
By the way, my dear friend lstsaur, I know what 'Dual' is
I might have lead you guys in a wrong way. By update I mean, at the end of each step (ORA9i Stage==>SquentialFileStage) I am executing the query to get a 'Date' and insert that value into the Sequential file. My intension is when I run the job the next time it extracts data based on the data present in the Sequential file
Am I doing anything wrong?
Thanks a lot in advance
Yamini
By the way, my dear friend lstsaur, I know what 'Dual' is