aspose file tools*
The moose likes Oracle/OAS and the fly likes execute block Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "execute block" Watch "execute block" New topic
Author

execute block

Megha Singhal
Ranch Hand

Joined: Feb 28, 2012
Posts: 182

how to take sql block in any string variable and execute a block using executeQuery() or how the block execute?
Vivek Kanodia
Greenhorn

Joined: Apr 27, 2012
Posts: 6
The below link might help you:
http://java-x.blogspot.com/2006/10/returning-data-from-anonymous-plsql.html.
Megha Singhal
Ranch Hand

Joined: Feb 28, 2012
Posts: 182

Vivek Kanodia wrote:The below link might help you:
http://java-x.blogspot.com/2006/10/returning-data-from-anonymous-plsql.html.


what is the error in following code

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

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?
Megha Singhal
Ranch Hand

Joined: Feb 28, 2012
Posts: 182

Martin Vajsar wrote: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 goo d 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?

i want to generate a serial number which is unique for the composite of year and ccno. for eg
srno year ccno
1 2011 234
2 2011 234
1 2010 456
2 2010 456
above is the entry in the trans table
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

Do you have any other requirements for the serial number?

If you do not need the generated numbers to be the same for distinct executions of the query, you can assign them on the fly using the analytic count function:

You will need to modify the order by date_created clause to specify a column (or columns) that will govern in which order the ssnos will be assigned. You can omit it entirely and it will be assigned in unspecified (more or less random) fashion.

If you need the generated numbers to remain unchanged once assigned, this approach is not possible, you'd need to store the numbers in the database.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: execute block
 
Similar Threads
return in thread.
a simple quetion,help
return of the try-catch
connection pooling issue in production environment -
Exception in finally