DS 7.5.1A on Windows 2003 SP2 - oracle = Ora9.2.0
We have an oracle stored procedure which we execute through Datastage 7.5.1 odbc stage (stored procedure stage is NOT available).
When we in sqlplus run "exec owner.stored.procedure" we get the correct result but if we use datastage odbc {call owner.stored.procedure} we get a different and wrong result.
Job is designed by first import the procedure -> using "Call stored procedure" as update action -> change to user defined SQL -> eliminate input params (proc runs with no inputs)
this is not about the proc itself, but is there any specific behaviour in the ODBC stage that can cause this sort of behaviour ???
ODBC Stored procSQL and Datastage produces different results
Moderators: chulett, rschirm, roy
The Stored Procedure stage IS available, someone there may have chosen to not install it however. And no, never heard of nor seen such a thing when you 'fully qualify' the proc name, expecially considering it has no input parameters to get wrong. ![Confused :?](./images/smilies/icon_confused.gif)
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
for all the basic questions (and I know you mean well)
Yes - we do connect to the correct data - other wise we couldn't see the digfferent output.
Yes we do run cursors, and we suspect, that the call from DS leaves the cursors either too soon or only takes the last result part
Basically for a given date it runs through a number of records to see if the current date fullfills a criteria (holiday, thursday, 8th day of month etc.
All records that fullfill criteria should then be inserted in another table.
it seems that we either get nothing or a fraction
running same SQL i SQL Plus gives corect result
Yes - we do connect to the correct data - other wise we couldn't see the digfferent output.
Yes we do run cursors, and we suspect, that the call from DS leaves the cursors either too soon or only takes the last result part
Basically for a given date it runs through a number of records to see if the current date fullfills a criteria (holiday, thursday, 8th day of month etc.
All records that fullfill criteria should then be inserted in another table.
it seems that we either get nothing or a fraction
running same SQL i SQL Plus gives corect result
Re: ODBC Stored procSQL and Datastage produces different res
So actually this *is* about the proc itself. And we have to ask 'basic questions' when people don't supply relevant details, especially considering how often the answer comes from those kind of questions.jinm wrote:this is not about the proc itself
If your proc is returning multiple records from your cursor(s) to serve as a source in a DataStage job, from what I recall there are rules about how it needs to be coded. I don't have the specifics at hand but that should be included as part of the Stored Procedure stage's documentation.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Chulett: You are right. Of course basic questions needs to be asked.
My intention was in the first place to get a quick response as to wheither there actually is a difference in the way SP's are initiated from SQL plus vs Datastage.
In parallel to this topic we are looking into the proc itself running in debug etc to see what takes place, but sometimes one can see the light by asking the simplest questions instead of producing a scientific report on what you try do accomplish
The Stored proc does not return records to the DS job.
It takes records in an input table, and based on the date, it inserts a number of records in a second tabel, and then ends.
That's it.
Problem is probably that each record is looped through a number of criterias to see if it fits one, and as soon as it gets an OK it is put on an insert list.
After completion of all records the insert list is then run.
At the time we are focusing on the SP code
Sreenivasulu:
The Datastage job itself runs in a correct way. it starts the SP and receives proper return code.
We have literally hundreds of jobs that kicks off Oracle SP, but this one I have never seen in the ten years I have been working with Datastage.
My intention was in the first place to get a quick response as to wheither there actually is a difference in the way SP's are initiated from SQL plus vs Datastage.
In parallel to this topic we are looking into the proc itself running in debug etc to see what takes place, but sometimes one can see the light by asking the simplest questions instead of producing a scientific report on what you try do accomplish
![Laughing :lol:](./images/smilies/icon_lol.gif)
The Stored proc does not return records to the DS job.
It takes records in an input table, and based on the date, it inserts a number of records in a second tabel, and then ends.
That's it.
Problem is probably that each record is looped through a number of criterias to see if it fits one, and as soon as it gets an OK it is put on an insert list.
After completion of all records the insert list is then run.
At the time we are focusing on the SP code
Sreenivasulu:
The Datastage job itself runs in a correct way. it starts the SP and receives proper return code.
We have literally hundreds of jobs that kicks off Oracle SP, but this one I have never seen in the ten years I have been working with Datastage.