Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am using H2 database for testing but for production I am using Oracle. I am trying to use following query.

SELECT ROWNUM as id, name, state FROM info;
with following configuration
public class DatabaseConfiguration {
    private final static DriverManagerDataSource dataSource = initializeDataSource();
    @Bean
    @Profile("test")
    public DataSource dataSource() {
         return dataSource;
    private static DriverManagerDataSource initializeDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;MODE=Oracle;");
        dataSource.setUsername("sa");
        dataSource.setPassword("sa");
        return dataSource;
But I am getting java.util.NoSuchElementException. Does H2 support ROWNUM?
                You don't specify how you intend to use the row number, but since the most common usage in Oracle was to page queries, be aware that H2 supports LIMIT and OFFSET for that purpose. See SELECT and Result Sets in the docs.
– jpmc26
                Dec 26, 2018 at 18:16
No, H2 does not support the syntax ROWNUM. It provides the ROWNUM() function, which can be used to achieve the same ends, but it is not identical either syntactically or behaviorally.
  
I am using H2 database for testing but for production I am using Oracle.
The best answer to your question is don't use a different RDBMS for development. A non-exhaustive, not detailed list of possible options for using Oracle for development:
It might be possible to download and install the database locally under the Oracle Technology Network License. If I'm reading this correctly, you can use the database for development purposes without purchasing a license, but you should double check my understanding. Specifically, it states:
  
Oracle grants You a nonexclusive, nontransferable, limited license to internally use the Programs, subject to the restrictions stated in this Agreement, only for the purpose of developing, testing, prototyping, and demonstrating Your application and only as long as Your application has not been used for any data processing, business, commercial, or production purposes, and not for any other purpose.
Use Oracle Express locally. It's not fully feature complete (For example, it doesn't have the full Oracle Spatial.), but if your usage is limited to the available features, it may be enough. Also, it appears that only 11g is currently available for Windows installation, meaning you'd need a VM to host 18.
Set up an Amazon RDS instance for development only. The cheapest instances in the US regions are less than $350 per year, and you're likely to be able to make do just fine with these cheaper instances if you were seriously considering an in-memory DB as a replacement. That's almost certainly less than you're paid for a couple of 8-hour work days. Likely well worth it compared to lost time on dealing with two different RDBMSes (or even over using Express with its limitations). Your company might even be able to shave some off that by having the DB off-line at night or something.
Ask for a separate database and instance on an existing Oracle server and dedicate it to development. This would require ensuring that enough hardware is available and ensuring that it's configured with hard limits on processing and disk space to prevent it from eating up too many resources.
There is some kind of developer arrangement for licensing, but the terms are really weird, apparently.
Or consider switching to a different RDBMS that better supports development if the project is still early. MS SQL Server is absolutely fine on this front with their Express edition, and PostgreSQL and MySQL are even better since they're free everywhere. It's also much easier to find free documentation and help with those databases than with Oracle.
Regardless, find a way to actually use the same database technology for both development and production. Developer time is probably one of your company's most expensive resources, and building/maintaining an H2 database that's never going to prod is not a good use of that resource (or QA resources or anyone's time and effort).
If your question is incomplete and you're actually using H2 for something other than just development, then set up your code to use separate queries for each RDBMS. Every RDBMS has it's own dialect of SQL. It is an enormous mistake to attempt to use the same query for different RDBMSes. Even if the syntax works in both, they'll have different available functions and the performance characteristics are unlikely to be the same for anything but the simplest queries. Use separate queries.
As with ROWID the ROWNUM is specific thing just for Oracle.
But you can achieve the same result using:
SELECT rownum(), name, state FROM info
rownum() is system function H2. See it here
                Problem is I am selecting from view and does not have unique identifier even if I select multiple columns as key.
– nicholasnet
                Dec 26, 2018 at 4:03
                Sorry I didn’t quiet get that. Are you suggesting to use ROWID instead of ROWNUM? If so then I cannot use ROWID because I selecting from view.
– nicholasnet
                Dec 26, 2018 at 4:11
                I looked at the docs and I noticed that it supports ROWNUM() function but it is not quite same as ROWNUM in Oracle. So, I am wondering whether H2 supports it or not.
– nicholasnet
                Dec 26, 2018 at 4:14
                I am not sure whether it is Oracle specific or not but for sure it’s not present in MySQL or PostgreSQL. ROWNUM is pseudo column. I can use ROWNUM here because all I have to do is select no update or insert.
– nicholasnet
                Dec 26, 2018 at 4:18
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.