ODBC Stored procSQL and Datastage produces different results

[jinm]

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 ???

[chulett]

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.

[ArndW]

What sort of a stored procedure is involved - does it use internal cursors or other complex logic where the state of the query might affect the result?

[chulett]

I'd also confirm that you are connecting to the proper instance in the ODBC stage, make sure it's the same one that you tested this with via sqlplus.

[jinm]

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

[chulett]

this is not about the proc itself

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.

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.

[Sreenivasulu]

Jim,
Rather than the DML operation of the Stored procedure can you check whether the SP is passing and returning the values correctly to datastage.

I believe if the SP is passsing and returning the values correctly the 'scope of datastage with regards to SP is complete'.

Regards
Sreeni

[jinm]

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.

[qutesanju]

please try calling SP using command stage and see what you can see