Transition from Oracle to H2

[dzoettl]

Hi,

we are using H2 in development for six month now, our production
database is Oracle. Next year we would like to replace some old
servers using Oracle with new machines and H2.

Even though our code runs fine, there are still some functions missing
which are used in customization, e.g. 'Function "TO_DATE" not found' .

For using H2 as drop-in replacement we would like to add those Oracle
specific functions when running in Oracle mode. Maybe we could create
an add-on jar for this.

Here are my questions:
1. Who would like to join the effort of creating this drop-in
replacement for Oracle ?
2. Is there already someone doing such a thing ?
3. Is there already some a Wiki for H2 development ?

Internally we are using http://www.dokuwiki.org/dokuwiki - maybe we
can open it for extern access to others.

What we can provide:
- Coding and test code for Oracle specific functions.
- A database tool for copying from Oracle to H2 and back.
- A tool to view/monitor large files (> 2GB).
- A migration guide for transition from Oracle to H2 .

Our platform:
- Mac OS X + Win XP
- http://www.jetbrains.com/idea

Sincerely
Dirk Zöttl
Eckenfelder GmbH & Co.KG

[Noel Grandin]

Hi

The documentation is all at h2database.com

There is already an oracle compatibility mode for H2.
http://h2database.com/html/features.html#compatibility

I'm quite sure that if you submit patches for the things that you
need, Thomas will be happy to accept them.
The code is really pretty well-structured and easy to work with.
Set up Eclipse, install Subclipse, checkout the repository, code,
generate patch, and send to list.

You're also welcome to just ask nicely for features and some of us
might help out.

Regards, Noel Grandin

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

[Thomas Mueller]

Hi,

Even though our code runs fine, there are still some functions missing
which are used in customization, e.g. 'Function "TO_DATE" not found' .

There is a sample implementation in org/h2/test/todo/tools.sql:

-- TO_DATE
create alias TO_DATE as $$
java.util.Date toDate(String s) throws Exception {
    return new java.text.SimpleDateFormat("yyyy.MM.dd").parse(s);
}
$$;

-- TO_CHAR
drop alias if exists TO_CHAR;
create alias TO_CHAR as $$
String toChar(BigDecimal x, String pattern) throws Exception {
    return new java.text.DecimalFormat(pattern).format(x);
}

For using H2 as drop-in replacement we would like to add those Oracle
specific functions when running in Oracle mode. Maybe we could create
an add-on jar for this.

Yes, I also thought about such a feature. Currently there is no such 'plug' feature, but it does make sense to add it.

What about a class that is loaded depending on the mode, and initializes the functions. For example, a class org.h2.mode.Oracle with a static init(Connection conn) method. This method is called when the Oracle mode is enabled.

1. Who would like to join the effort of creating this drop-in
replacement for Oracle ?

My plan is not 100% compatibility (that would be extremely hard), but better compatibility would be nice.

3. Is there already some a Wiki for H2 development ?

Currently not. Let's see if this is required. I do like wikis, but they quickly tend to be outdated and not maintained. I prefer to just update the regular documentation instead.

> What we can provide

If it's OK for you to provide it as open source, then sure we can discuss about that.

Regards,

Thomas

[ZPavel]

Hi,

How can i use this sql adding TO_DATE with spring?

Thanks

вторник, 22 ноября 2011 г. 20:20:35 UTC+1 пользователь Thomas Mueller написал:

[ZPavel]

Sorry, i have solved it : 

INIT=create schema if not exists test\;runscript from 'classpath:scripts/schema.sql

in which i i put : 

drop alias if exists TO_DATE;

create alias TO_DATE as $$

java.util.Date toDate(String s, String format) throws Exception {

    return new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss.sss").parse(s);

}

$$;

you must have 2 parameters in java function as well.

+

среда, 21 марта 2012 г. 14:39:39 UTC+1 пользователь ZPavel написал:

[ZPavel]

me again,

what about alias for Oracle sequence MAXVALUE & MINVALUE?

When will 1.4 of h2 will be released?

thanks

среда, 21 марта 2012 г. 16:38:24 UTC+1 пользователь ZPavel написал:

[Thomas Mueller]

Hi,

what about alias for Oracle sequence MAXVALUE & MINVALUE?

Could you give more details please (a link to the documentation, or example statements)?

When will 1.4 of h2 will be released?

Sorry I can't say... This summer I guess.

Regards,

Thomas

[Steve McLeod]

There's Oracle docs for CREATE SEQUENCE here:

http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_6015.htm#i2067093

MINVALUE is equivalent to H2's CREATE SEQUENCE ... START WITH

As far as I can tell H2 has no equivalent to MAXVALUE. Or to be more precise, MAXVALUE in H2 is always Long.MAX_VALUE.

[mclovis]

Thomas,

[mclovis]

Thomas,

       I am unsure of the answer to this thread. I am looking for an in memory database (with a small footprint). That I can use for testing existing code written for Oracle DB. I do not wish to make changes to the code for the test DB. This includes stored procedures and triggers. To what degree and how could this best be accomplished with H2? Do you have other memory DBs that may be a better fit?

Thanks in advance,

Mike

[James Gunja]

Hi Mike,

Did you get any solution for this? Me too trying to test my code with in memory database which is written for Oracle DB.

Thanks,

James

[Noel Grandin]

H2 does not support Oracle stored procedures, so if you're trying to run
something that uses that directly against H2, it will not work.