Oracle
- Jen Bourey
- Andrew Petro
- Laura McCord
- Eric Dalquist
About Oracle
Oracle is a proprietary database available from http://www.oracle.com.
The Oracle FAQ is available from http://www.orafaq.com.
Obtaining a 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=ojdbc14 -Dversion=10.2.0.3.0 -Dpackaging=jar -DgeneratePom=true -Dfile=ojdbc14-10.2.0.3.0.jar
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.
Opening /uPortal/pom.xml there is a section about 92 lines down that reads:
<!-- The JDBC Driver used by uPortal -->
<jdbc.groupId>hsqldb</jdbc.groupId>
<jdbc.artifactId>hsqldb</jdbc.artifactId>
<jdbc.version>${hsqldb.version}</jdbc.version>
We will add the Oracle driver here using the group, artifact and version information from the mvn install:install-file command above.
<!-- The JDBC Driver used by uPortal -->
<jdbc.groupId>com.oracle</jdbc.groupId>
<jdbc.artifactId>ojdbc14</jdbc.artifactId>
<jdbc.version>10.2.0.3.0</jdbc.version>
See Issues section about possible issues using this driver.
JDBC Configuration
Edit /uPortal/uportal-impl/src/main/resources/properties/rdbm.properties and uncomment the lines for Oracle Modify the URL, username and password as appropriate:
##### Oracle 10g - example hibernate.connection.driver_class=oracle.jdbc.OracleDriver hibernate.connection.url=jdbc:oracle:thin:@my.school.edu:1521:PROJECTS hibernate.connection.username=test hibernate.connection.password=mypass hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
Testing The Configuration
Running the dbtest ant target will tell you if you have configured the database connection properly.
ant dbtest
If it works correct you should see something like:
[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
Execute "ant initportal" to build the database tables and copy files to your servlet container
Start your servlet container.
Loading the Database
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 rdbm.properties file
- 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.
Additional Notes
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.