The anonymous PL/SQL block doesn't produce a cursor, so it cannot be processed as a
ResultSet (at least not directly). You would process it as a
CallableStatement and register output parameter to get the resulting value out.
It is not easy to remedy your code. The
SELECT INTO in the PL/SQL block seems to be able to return more than one record, which would cause a PL/SQL exception. If you had a query that would be guaranteed to return a single row, you could use the
SELECT INTO, but you'd have to declare the
sr_no variable in the PL/SQL block and assign that into the output parameter at the end of the block. It is shown in the example you've been given, though that code is a bit more complicated that necessary if you don't need to pass custom types between
Java and PL/SQL.
What you have now would most easily be replaced as normal
PreparedStatement with a
SELECT in Java, where you would also handle the case of zero returned rows. If you need to process a
ResultSet in Java, the anonymous PL/SQL block is not a good approach. There are other cases where it can be useful, and if you always get a fixed number of results from such a block, it can be easily done. What are your motives to use the PL/SQL blocks?
