Repeated Old rows in Query

[khaleelurrehman_badeghar]

Hi

I am executing a stored procedure (which uses cursor) in a query template (Fixed Query with Output).

But whenever I execute query from workbench or webpage it gives results by appending to the old/previous rows whereas stored procedure gives correct/expected results if I execute from database direclty.

I am using MII 12.1.10 Build(71)

Help will be appreciated.

Thanks and Regards

Khaleel

[jcgood25]

It would not appear that anyone has encountered this particular issue in the past, so logging a customer support ticket with the details might be your best option.  The Oracle specific mode you mention is unique for the IDBC data server connections and the query mode, but unless there is some form of temp table not being cleared, I don't see why the cursor results coming back to the query results would be any different than what you are getting directly on the database side.

Perhaps share your query syntax for others to take a look at?

[khaleelurrehman_badeghar]

Hi Jeremy

Thanks for your response. Following is the query syntax.

CALL ProcedureName('[Param.1]',:x)

Thanks and Regards

Khaleel

[Former Member]

Hi Khaleel

I am not an oracle expert, but just a thought about cursors, are you closing the cursor in the stored procedure after you have done your iterations.

Cheers

Rupesh

[khaleelurrehman_badeghar]

Hi Rupesh

Thanks for your suggestion. But when the cursor is declared in a declaration section and not in a package Oracle Database will automatically close it when the block in which it is declared terminates. But it is good idea to close it explicitly which I have tested and as I told from database or Oracle SQL Developer when I test the procedure it gives correct results but when I execute it from Workbench it gives me previous old rows and replaces few columns with old data etc. I think this has to do something with JDBC driver because this is the only thing which is coming between Oracle and MII. I am using Oracle 10g and ojdbc14.jar is deployed.

Thanks and Regards

Khaleel