• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Attractive, successful people love this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic