Oracle – How-to: Grails 3.0.2 + Oracle Database 12c

grailsjdbcoracle

I am making my first steps with Grails and try to create a hello world application with an Oracle Database 12c.

Unfortunately the tutorial documentation on the database specific part at www.grails.org seems not that comprehensive (in contrast for example to that of Ruby on Rails, which I used now several years) – especially when it comes to non-H2 databases.

Is there some recent tutorial on how to get a Grails 3.0.2 app up and running with an Oracle Database 12c?

Mainly I am interested on a) where to put the corresponding JDBC driver and b) how the database configuration should look like.

For a)

grails.org talks about a "lib" directory but does not explain, where it exists or should be created. Other sources say, that the "lib" directory is outdated since several Grails versions and that JDBC drivers should be loaded via dependencies from some repositories – which is obviously not possible with the closed-source Oracle JDBC driver.

For b)

Currently, I have created basic database configuration at <my-app>/grails-app/conf/DataSource.groovy for Oracle 12c:

dataSource {
        url = "jdbc:oracle:thin:@my-server:my-port:my-sid"
        driverClassName = "oracle.jdbc.OracleDriver"
        dialect = "Oracle10gDialect"
        username = "my-user"
        password = "my-pass"
}

Should that be enough for a simple hello world app?

Everything I have found so far at the web is outdated either on the Grails side (covering versions 2.x oder 1.x) and/or the Oracle side (covering versions 10* oder 11*).

Thanks for any hints!

Update (just for the record and to bring together the comments below):

Edit <myapp>/grails-app/conf/application.yml.

hibernate:
    jdbc:
        use_get_generated_keys: true
(...)
dataSource:
    pooled: true
    jmxExport: true
    driverClassName: oracle.jdbc.OracleDriver
    username: <myuser>
    password: <mypassword>
(...)
environments:
    development:
        dataSource:
            dbCreate: create
            url: jdbc:oracle:thin:@<myserver>:<myport>:<mysid>
(...)

Get the corresponding Oracle JDBC driver that fits your JDK and Oracle DB release. In my case (openJDK 7 + Oracle 12c) this was ojdbc7.jar release 12.1.0.2.0 from the Oracle Website: http://www.oracle.com/technetwork/database/features/jdbc/index.html

Place this JDBC driver in <myapp>/lib (create this directory, if it does not yet exists).

Add details of this JDBC driver to the dependencies section of <myapp>/build.gradle (modify if necessary to fit your environment/releases):

(...)
dependencies {
    (...)
    runtime "com.oracle.ojdbc7:12.1.0.2.0" 
    (...)
}
(...)

Create a Maven artifact (modify if necessary to fit your environment/releases):

mvn install:install-file -Dfile=lib/ojdbc7.jar -DgroupId=com.oracle -DartifactId=ojdbc7 -Dversion=12.1.0.2.0 -Dpackaging=jar -DgeneratePom=true

Add the path to the JDBC driver to your CLASSPATH: export CLASSPATH=/<somewhere>/<myapp>/lib/ojdbc7.jar

Enjoy …

Best Answer

yes this will be OK for meanwhile. But there are details missing. I'll post detailed instructions as soon as i get to the office. Actually there is a sample for mySql somewhere on the grails site, and from it it's pretty clear how to make same for an oracle DB.

I just have created a new app and compared it with myne for oracle 11g. Here it is:

./build.gradle, add following line to "dependencies" section:

runtime "com.oracle:ojdbc14:10.2.0.3.0"
./grails-app/conf/application.yml, add following to "hibernate" section:

jdbc:
    use_get_generated_keys: true

./grails-app/conf/application.yml, dataSource: section should be like:

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: oracle.jdbc.OracleDriver
    username: YOURUSERNAME
    password: yoursecret

./grails-app/conf/application.yml, environments: configuration, all three environments should be like:

environments:
development:
    dataSource:
        dbCreate: create
        url: jdbc:oracle:thin:@somehost.net:1521:YOURORAINSTANCE
test:
    dataSource:
        dbCreate: update
        url: jdbc:oracle:thin:@somehost.net:1521:YOURORAINSTANCE
production:
    dataSource:
        dbCreate: update
        url: jdbc:oracle:thin:@somehost.net:1521:YOURORAINSTANCE

And the Hibernate dialect for Oracle you have is pretty old one, I think you'd better remove it, without specifying it explicitly all works fine.

Related Solutions

Sql – Get list of all tables in Oracle

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name
  FROM all_tables

Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name
  FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

Sql – How to limit the number of rows returned by an Oracle query after ordering

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

Best Answer

Related Solutions

Sql – Get list of all tables in Oracle

Sql – How to limit the number of rows returned by an Oracle query after ordering

Related Topic