Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

CallableStatement resultset returns only columns and no data, where data exists.

 
Jyotsna Pai
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi there,

Can someone please help me with this one.
I am trying to retrieve results by invoking a stored procedure - APPCOMMON.MY_PROC.
This procedure has 3 IN parameters of type varchar and 1 OUT parameter of type SYS_REFCURSOR.

The concern is that when I hard-code the 3 above mentioned input parameters in my stored procedures, the java code below works fine and gives me output as a Collection - 'results' which I then use to display an excel sheet. This shows me a table with 88 columns and 39 records.

But when I try to pass the parameters like below -
callStmt.setString(1, district);
callStmt.setString(2, workGroup);
callStmt.setString(3, systemStatus);

I don't get any data from the backend. But I am able to retrieve the column-names into the Collection - results.
Seems like some problems retrieving resultset, but I am not able to identify it.

Please advise.




The Stored procedure is -



Please advise. Any help is much appreciated.

Thanks and Regards,
Jyotsna Pai

 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you sure the input values are correct ? Try to hard code the values in your Java source :

(or whatever the values are)
 
Jyotsna Pai
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Christopher,

Thanks for your reply.

I have tried hard-coding the setString exactly as you advised.
It did not help. I could again retrieve only the columns, but no data.

I also tried the below -


Doesn't work either.
 
Jyotsna Pai
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everybody,

To all those who went through this page, Thanks a lot!
I have finally managed to resolve this issue myself.
The issue was not with the java code, but was with the stored procedure.
I changed the stored procedure to include MY_TYPES_PKG.refCursor instead of SYS_REFCURSOR.-
The stored procedure now is -


I created a package called MY_TYPES_PKG as follows -


I ensured to give GRANT to the user on both the package and the procedure.

And my code works fine.

For those of you, who would wish to see the code again, it is as follows -


Thanks to everyone!

Cheers,
Jyotsna Pai
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good to hear it is solved. I must admit I had no clue what the problem could have been.
 
Jyotsna Pai
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No worries Chris!

All we want to achieve is that the issue gets reoslved.
I am thankful to you for looking into it and helping me with it!

Thanks again!
Jyotsna Pai
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic