Oracle

Owned by Jen Bourey
Last updated: Aug 02, 2018 by Jim HelwigVersion comment
7 min read

About Oracle

Oracle is a proprietary database available from http://www.oracle.com.
The Oracle FAQ is available from http://www.orafaq.com.

Step 1: Obtain the Driver

Since the Oracle JDBC driver is not available in the central Maven repository it must be placed into the local repository on each machine you wish to build uPortal on. An alternative to installing the JAR on each machine you can setup a maven repository for use by multiple machines.

The first step is determine if you have the Oracle client and Oracle JDBC drivers installed on your local machine. If Oracle is installed on your local machine it will be located at $ORACLE_HOME. Try to locate the driver using the following command:

ls $ORACLE_HOME/lib/

If you cannot locate it download the correct Oracle JDBC driver for your database. Once you have the jar it needs to be installed into your local maven repository using the following command:

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6_g -Dversion=11.2.0.3 -Dpackaging=jar -DgeneratePom=true -Dfile=<path to odbc6_g.jar file>

The -DgroupId, -DartifactId and -Dversion specified in this command are up to you but they should match the JAR vendor, name and version to avoid confusion down the road.
The -Dfile is the Oracel JDBC driver. You should include the working directory path in the event the driver is not in the directory you are running maven from.

Oracle 12c is supported with uPortal 4.2.1. With uPortal 4.2.0, you can update the main pom.xml in the root source directory to use hibernate 4.2.19Final and Oracle 12C should work (e.g. <hibernate.version>4.2.19.Final</hibernate.version>). You should also insure you are using SimpleContentPortlet v1.2.1 or 2.0.1 due to CMSPLT-54 - Getting issue details... STATUS . For additional information, see http://jasig.275507.n4.nabble.com/Anyone-using-or-transitioning-to-Oracle-12c-td4665785.html.

 

Step 2: Configure the Database Filter     

     1.  In the filters folder, locate the default local.properties file under uPortal-4.1.x/filters/local.properties and configure the Database Connection Settings

uPortal/filters/local.properties
# HSQL Configuration
environment.build.hsql.port=8887

# Database Connection Settings (Uncomment the Maven Filters section in rdbm.properties)
environment.build.hibernate.connection.driver_class=oracle.jdbc.OracleDriver
environment.build.hibernate.connection.url=jdbc:oracle:thin:@//my.school.edu:1521:PROJECTS
environment.build.hibernate.connection.username=user
environment.build.hibernate.connection.password=password
environment.build.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
environment.build.hibernate.connection.validationQuery=select 1 from dual

Step 3: Add the database driver 

  1. NEW: Open uportal-db/pom.xml file, uncomment the Oracle driver below and modify as needed 

  2. Add the appropriate version properties to the root pom.xml file or enter the appropriate version below
uportal-db/pom.xml
... 
<dependencies>
        <!-- Add any db drivers that are applicable to *any* of your environments -->
	    <dependency>
	        <groupId>org.hsqldb</groupId>
	        <artifactId>hsqldb</artifactId>
	        <version>${hsqldb.version}</version>
	        <scope>compile</scope>
	    </dependency>
        <!--
         | The following db drivers should be uncommented and/or modified as needed for server 
         | deployments.  (Add all thaat are needed.)  Don't forget to add appropriate  .version 
         | properties to the root pom.xml, or simply enter the appropriate version below.
         +-->
		<!--
	    <dependency>
            <groupId>postgresql</groupId>
	        <artifactId>postgresql</artifactId>
	        <version>${postgres.version}</version>
	        <scope>compile</scope>
	    </dependency>
	    <dependency>
	        <groupId>com.ibm.db2</groupId>
	        <artifactId>db2-jdbc</artifactId>
	        <version>${db2.version}</version>
	        <scope>compile</scope>
	    </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>${mssql.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
		-->
        
		<dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6_g</artifactId>
            <version>${oracle.version}</version>
        </dependency>
		
 		<!--
        <dependency>
            <groupId>org.sybase</groupId>
            <artifactId>sybase-jconnect</artifactId>
            <version>${sybase.version}</version>
        </dependency>
	    -->
    </dependencies>

Step 4: Test The Configuration

Running the dbtest ant target will tell you if you have configured the database connection properly.

ant clean dbtest

If it works correctly, you should see something like the following:

[java]     Connected To: jdbc:oracle:thin:@yourserver:1521:uportal
     [java]     Supports:
     [java]         Outer Joins:          true
     [java]         Transactions:         true
     [java]         {ts metasyntax:       true
     [java]         TO_DATE():            false

     [java] Database name:    'Oracle'
     [java] Database version: 'Oracle Database 11g Release 11.1.0.0.0 - Production'
     [java] Driver name:      'Oracle JDBC driver'
     [java] Driver version:   '10.2.0.3.0'
     [java] Driver class:     'Oracle JDBC driver'
     [java] Connection URL:   'jdbc:oracle:thin:@yourserver:1521:uportal'
     [java] User:             'USERNAME'

     [java] Type Mappings: [Type[genericType=TIMESTAMP,local=DATE]]

     [java] supportsANSI92EntryLevelSQL: true
     [java] supportsANSI92FullSQL:       false
     [java] supportsCoreSQLGrammar:     true
     [java] supportsExtendedSQLGrammar: true

     [java] supportsTransactions:         true
     [java] supportsMultipleTransactions: true
     [java] supportsOpenCursorsAcrossCommit:      false
     [java] supportsOpenCursorsAcrossRollback:    false
     [java] supportsOpenStatementsAcrossCommit:   false
     [java] supportsOpenStatementsAcrossRollback: false

     [java] supportsStoredProcedures:     true
     [java] supportsOuterJoins:           true
     [java] supportsFullOuterJoins:       true
     [java] supportsLimitedOuterJoins:    true
     [java] supportsBatchUpdates:         true
     [java] supportsColumnAliasing:       true
     [java] supportsExpressionsInOrderBy: true
     [java] supportsOrderByUnrelated:     true
     [java] supportsPositionedDelete:     false
     [java] supportsSelectForUpdate:      true
     [java] supportsUnion:                true
     [java] supportsUnionAll:             true

     [java] getMaxColumnNameLength: 30
     [java] getMaxColumnsInIndex:   32
     [java] getMaxColumnsInOrderBy: 0
     [java] getMaxColumnsInSelect:  0
     [java] getMaxColumnsInTable:   1000
     [java] getMaxConnections:      0
     [java] getMaxCursorNameLength: 0
     [java] getMaxIndexLength:      0
     [java] getMaxRowSize:          2000
     [java] getMaxStatements:       0
     [java] getMaxTableNameLength:  30
     [java] getMaxTablesInSelect:   0
     [java] getMaxUserNameLength:   30
     [java] getSearchStringEscape:  //
     [java] getStringFunctions:     ASCII,CHAR,CONCAT,LCASE,LENGTH,LTRIM,REPLACE,RTRIM,SOUNDEX,SUBSTRING,UCASE
     [java] getSystemFunctions:     USER
     [java] getTimeDateFunctions:   HOUR,MINUTE,SECOND,MONTH,YEAR

     [java] Table Types: SYNONYM,TABLE,VIEW
     [java] SQL Types:   INTERVALDS,INTERVALYM,TIMESTAMP WITH LOCAL TIME ZONE,TIMESTAMP WITH TIME ZONE,
NUMBER,NUMBER,NUMBER,LONG RAW,RAW,LONG,CHAR,NUMBER,NUMBER,NUMBER,FLOAT,REAL,VARCHAR2,DATE,DATE,
TIMESTAMP,STRUCT,ARRAY,BLOB,CLOB,REF

BUILD SUCCESSFUL

Step 5: Build and Deploy 

Following a successful test, you can execute the command below to build the database tables and copy the files to your servlet container. 

Note: Executing the command "ant clean initportal" will drop and recreate the database tables and all existing data will be lost. This will result in a clean uPortal database structure. If you want to keep the contents of your existing database, use "ant clean deploy-war"

ant clean initportal

Step 6: Restart Tomcat

 

Database Preparations

Database Privileges

uPortal requires CREATE SEQUENCE privileges.

Loading the database requires a couple of steps

  • Create the database: Refer to the Oracle database documentation for instructions regarding how to create an Oracle database instances. The database name (or in this case, the Oracle SID), port number, and driver information must reflect what was defined in the local.properties file located in the filters/ directory.
  • Create the tables and populate the database: From the uPortal build directory, run "ant db" to execute the ant target which loads the database. The ant target uses the Dbloader tool.

Issues and Known Bugs

Some people have encountered problems with the Oracle JDBC driver with certain web application environments if the classes zip file is used as-is with the .zip file extension. Simply renaming the file to a .jar file seems to fix the problem. Alternatively, unzipping the classes file into a directory structure, then using the jar command to repackage the classes into a jar file works as well.

Additional References

Having problems with these instructions?

Please send us feedback at uportal-user@lists.ja-sig.org