This week's book giveaway is in the Programmer Certification forum.
We're giving away four copies of OCP Oracle Certified Professional Java SE 11 Programmer I Study Guide: Exam 1Z0-815 and have Jeanne Boyarsky & Scott Selikoff on-line!
See this thread for details.
Win a copy of OCP Oracle Certified Professional Java SE 11 Programmer I Study Guide: Exam 1Z0-815 this week in the Programmer Certification forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Paweł Baczyński
  • Piet Souris
  • Vijitha Kumara

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

 
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

 
Sheriff
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • 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 VI Editor Ubuntu
  • 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
 
Look ma! I'm selling my stuff!
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!