Package org.compiere.db

Class DB_Oracle

java.lang.Object

org.compiere.db.DB_Oracle

All Implemented Interfaces:

AdempiereDatabase

public class DB_Oracle
extends Object
implements AdempiereDatabase

Oracle Database Port

Version:

$Id: DB_Oracle.java,v 1.7 2006/09/22 23:35:19 jjanke Exp $ --- Modifications: Refactoring. Replaced Oracle Cache Manager with C3P0 connection pooling framework for better and more efficient connection handling

Author:

Jorg Janke, Ashley Ramdass (Posterita)

Field Summary

Fields

Modifier and Type	Field	Description
static final int	DEFAULT_CM_PORT	Default Connection Manager Port
static final int	DEFAULT_PORT	Default Port
static final String	DRIVER	Driver Class Name

Fields inherited from interface org.compiere.db.AdempiereDatabase

CMD_CREATE_DATABASE, CMD_CREATE_USER, CMD_DROP_DATABASE, LOCK_TIME_OUT, MAX_OBJECT_NAME_LENGTH

Constructor Summary

Constructors

Constructor	Description
DB_Oracle()	Oracle Database

Method Summary

Modifier and Type	Method	Description
String	addPagingSQL(String sql, int start, int end)	Implemented using the fetch first and offset feature. use 1 base index for start and end parameter
void	cleanup()	Clean up
void	close()	Close
String	convertStatement(String oraStatement)	Convert an individual Oracle Style statements to target database statement syntax.
boolean	createSequence(String name, int increment, int minvalue, int maxvalue, int start, String trxName)	Create Native Sequence
boolean	forUpdate(PO po, int timeout)	Lock PO for update
String	getAlternativeSQL(int reExNo, String msg, String sql)	Check and generate an alternative SQL
String	getBlobDataType()
Connection	getCachedConnection(CConnection connection, boolean autoCommit, int transactionIsolation)	Get Cached Connection
String	getCatalog()	Get JDBC Catalog
String	getCharacterDataType()
String	getClobDataType()
String[]	getCommands(int cmdType)	Get SQL Commands.
String	getConnectionURL(String dbHost, int dbPort, String dbName, String userName)	Get Connection URL.
String	getConnectionURL(String connectionURL, String userName)	Get Database Connection String
String	getConnectionURL(CConnection connection)	Get Database Connection String.
String	getConstraintType(Connection conn, String tableName, String IXName)	Get constraint type associated with the index
Convert	getConvert()	Get Convert implementation for this DB adapter
DataSource	getDataSource(CConnection connection)	Create DataSource
String	getDataType(String columnName, int displayType, int precision, boolean defaultValue)	Deprecated, for removal: This API element is subject to removal in a future version.
String	getDescription()	Get Database Description
Driver	getDriver()	Get and register Database Driver
Connection	getDriverConnection(String dbUrl, String dbUid, String dbPwd)	Get Driver Connection
Connection	getDriverConnection(CConnection connection)	Get Connection from Driver
String	getForeignKeyConstraint(Exception e)	The "child record found error" contains the foreign key constraint name after the second occurrence of the opening double quote: ["].
String	getJSONCast()
String	getJsonDataType()
String	getName()	Get Database Name
String	getNameOfUniqueConstraintError(Exception e)
int	getNextID(String name)	Get next sequence number in this Sequence
int	getNextID(String name, String trxName)	Get next sequence number in this Sequence
String	getNumericDataType()
String	getSchema()	Get JDBC Schema
String	getSQLAdd(MTable table, MColumn column)	Get SQL Add command
String	getSQLDDL(MColumn column)
String	getSQLModify(MTable table, MColumn column, boolean setNullOption)	Get SQL Modify command
int	getStandardPort()	Get Standard JDBC Port
String	getStatus()	Get Status
String	getSystemDatabase(String databaseName)	Get Name of System Database
String	getSystemUser()	Get Name of System User
ITablePartitionService	getTablePartitionService()	Get DB specific table partition support
String	getTimestampDataType()
String	getTimestampWithTimezoneDataType()
String	getUUIDDataType()
String	getVarcharDataType()
String	getVarcharLengthSuffix()
String	intersectClauseForCSV(String columnName, String csv)
String	intersectClauseForCSV(String columnName, String csv, boolean isNotClause)
SQLFragment	intersectFilterForCSV(String columnName, String csv)
SQLFragment	intersectFilterForCSV(String columnName, String csv, boolean isNotClause)
boolean	isPagingSupported()	Is the database have sql extension that return a subset of the query result
boolean	isQueryTimeout(SQLException ex)
boolean	isQueryTimeoutSupported()
boolean	isSupported(String sql)	Check if DBMS support the sql statement
String	subsetClauseForCSV(String columnName, String csv)
SQLFragment	subsetFilterForCSV(String columnName, String csv)
boolean	supportsBLOB()	Supports BLOB
String	TO_Blob(byte[] blob)	Convert blob to hex encoded string and return SQL function that will convert the hex encoded string back to blob
String	TO_CHAR(String columnName, int displayType, String AD_Language)	Create SQL for formatted Date, Number
String	TO_DATE(Timestamp time, boolean dayOnly)	Create SQL TO Date String from Timestamp
String	TO_JSON(String value)	Return string as JSON object for INSERT statements
String	TO_NUMBER(BigDecimal number, int displayType)	Return number as string for INSERT statements with correct precision
String	toString()	String Representation

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Methods inherited from interface org.compiere.db.AdempiereDatabase

getSQLCreate, isNativeMode, quoteColumnName

Field Details

DRIVER

public static final String DRIVER

Driver Class Name

See Also:

• Constant Field Values

DEFAULT_PORT

public static final int DEFAULT_PORT

Default Port

See Also:

• Constant Field Values

DEFAULT_CM_PORT

public static final int DEFAULT_CM_PORT

Default Connection Manager Port

See Also:

• Constant Field Values

Constructor Details

DB_Oracle

public DB_Oracle()

Oracle Database

Method Details

getName

public String getName()

Get Database Name

Specified by:

getName in interface AdempiereDatabase

Returns:

database short name

getDescription

public String getDescription()

Get Database Description

Specified by:

getDescription in interface AdempiereDatabase

Returns:

database long name and version

getStandardPort

public int getStandardPort()

Get Standard JDBC Port

Specified by:

getStandardPort in interface AdempiereDatabase

Returns:

standard port

getDriver

public Driver getDriver()
                 throws SQLException

Get and register Database Driver

Specified by:

getDriver in interface AdempiereDatabase

Returns:

Driver

Throws:

SQLException

getConnectionURL

public String getConnectionURL(CConnection connection)

Get Database Connection String.

  Timing:
  - CM with source_route not in address_list  = 28.5 sec
  - CM with source_route in address_list      = 58.0 sec
  - direct    = 4.3-8 sec  (no real difference if on other box)
  - bequeath  = 3.4-8 sec
  

Specified by:

getConnectionURL in interface AdempiereDatabase

Parameters:

connection - Connection Descriptor

Returns:

connection String

getConnectionURL

public String getConnectionURL(String dbHost,
 int dbPort,
 String dbName,
 String userName)

Get Connection URL. http://download-east.oracle.com/docs/cd/B14117_01/java.101/b10979/urls.htm#BEIDBFDF

Specified by:

getConnectionURL in interface AdempiereDatabase

Parameters:

dbHost - db Host

dbPort - db Port

dbName - db Name

userName - user name

Returns:

connection

getConnectionURL

public String getConnectionURL(String connectionURL,
 String userName)

Get Database Connection String

Specified by:

getConnectionURL in interface AdempiereDatabase

Parameters:

connectionURL - Connection URL

userName - user name

Returns:

connection String

getCatalog

public String getCatalog()

Get JDBC Catalog

Specified by:

getCatalog in interface AdempiereDatabase

Returns:

null - not used

getSchema

public String getSchema()

Get JDBC Schema

Specified by:

getSchema in interface AdempiereDatabase

Returns:

user name

supportsBLOB

public boolean supportsBLOB()

Supports BLOB

Specified by:

supportsBLOB in interface AdempiereDatabase

Returns:

true if BLOB is supported

toString

public String toString()

String Representation

Specified by:

toString in interface AdempiereDatabase

Overrides:

toString in class Object

Returns:

info

getStatus

public String getStatus()

Get Status

Specified by:

getStatus in interface AdempiereDatabase

Returns:

status info

convertStatement

public String convertStatement(String oraStatement)

Convert an individual Oracle Style statements to target database statement syntax.

Specified by:

convertStatement in interface AdempiereDatabase

Parameters:

oraStatement - oracle statement

Returns:

converted Statement oracle statement

isSupported

public boolean isSupported(String sql)

Check if DBMS support the sql statement

Specified by:

isSupported in interface AdempiereDatabase

Parameters:

sql - SQL statement

Returns:

true: yes

getConstraintType

public String getConstraintType(Connection conn,
 String tableName,
 String IXName)

Get constraint type associated with the index

Specified by:

getConstraintType in interface AdempiereDatabase

Parameters:

conn - connection

tableName - table name

IXName - Index name

Returns:

String[0] = 0: do not know, 1: Primary Key 2: Foreign Key String[1] - String[n] = Constraint Name

getSystemUser

public String getSystemUser()

Get Name of System User

Specified by:

getSystemUser in interface AdempiereDatabase

Returns:

system

getSystemDatabase

public String getSystemDatabase(String databaseName)

Get Name of System Database

Specified by:

getSystemDatabase in interface AdempiereDatabase

Parameters:

databaseName - database Name

Returns:

e.g. master or database Name

TO_DATE

public String TO_DATE(Timestamp time,
 boolean dayOnly)

Create SQL TO Date String from Timestamp

Specified by:

TO_DATE in interface AdempiereDatabase

Parameters:

time - Date to be converted

dayOnly - true if time set to 00:00:00

Returns:

TO_DATE('2001-01-30 18:10:20',''YYYY-MM-DD HH24:MI:SS') or TO_DATE('2001-01-30',''YYYY-MM-DD')

TO_CHAR

public String TO_CHAR(String columnName,
 int displayType,
 String AD_Language)

Create SQL for formatted Date, Number

Specified by:

TO_CHAR in interface AdempiereDatabase

Parameters:

columnName - the column name in the SQL

displayType - Display Type

AD_Language - 6 character language setting (from Env.LANG_*)

Returns:

TRIM(TO_CHAR(columnName,'999G999G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')) or TRIM(TO_CHAR(columnName,'TM9')) depending on DisplayType and Language

See Also:

• DisplayType
• Env

TO_NUMBER

public String TO_NUMBER(BigDecimal number,
 int displayType)

Return number as string for INSERT statements with correct precision

Specified by:

TO_NUMBER in interface AdempiereDatabase

Parameters:

number - number

displayType - display Type

Returns:

number as string

getJSONCast

public String getJSONCast()

Specified by:

getJSONCast in interface AdempiereDatabase

Returns:

string with right casting for JSON inserts

TO_JSON

public String TO_JSON(String value)

Return string as JSON object for INSERT statements

Specified by:

TO_JSON in interface AdempiereDatabase

Parameters:

value -

Returns:

value as json

getCommands

public String[] getCommands(int cmdType)

Get SQL Commands. The following variables are resolved:

Specified by:

getCommands in interface AdempiereDatabase

Parameters:

cmdType - CMD_*

Returns:

array of commands to be executed

getDataSource

public DataSource getDataSource(CConnection connection)

Description copied from interface: AdempiereDatabase

Create DataSource

Specified by:

getDataSource in interface AdempiereDatabase

Parameters:

connection - connection

Returns:

data source

getCachedConnection

public Connection getCachedConnection(CConnection connection,
 boolean autoCommit,
 int transactionIsolation)
                               throws Exception

Get Cached Connection

Specified by:

getCachedConnection in interface AdempiereDatabase

Parameters:

connection - connection

autoCommit - auto commit

transactionIsolation - trx isolation

Returns:

Connection

Throws:

Exception

getDriverConnection

public Connection getDriverConnection(CConnection connection)
                               throws SQLException

Get Connection from Driver

Specified by:

getDriverConnection in interface AdempiereDatabase

Parameters:

connection - info

Returns:

connection or null

Throws:

SQLException

getDriverConnection

public Connection getDriverConnection(String dbUrl,
 String dbUid,
 String dbPwd)
                               throws SQLException

Get Driver Connection

Specified by:

getDriverConnection in interface AdempiereDatabase

Parameters:

dbUrl - URL

dbUid - user

dbPwd - password

Returns:

connection

Throws:

SQLException

close

public void close()

Close

Specified by:

close in interface AdempiereDatabase

cleanup

public void cleanup()

Clean up

getDataType

@Deprecated(since="13",
            forRemoval=true)
public String getDataType(String columnName,
 int displayType,
 int precision,
 boolean defaultValue)

Deprecated, for removal: This API element is subject to removal in a future version.

Get Data Type

Parameters:

columnName -

displayType - display type

precision - precision

defaultValue - if true adds default value

Returns:

data type

getAlternativeSQL

public String getAlternativeSQL(int reExNo,
 String msg,
 String sql)

Check and generate an alternative SQL

Specified by:

getAlternativeSQL in interface AdempiereDatabase

Parameters:

reExNo - number of re-execution

msg - previous execution error message

sql - previous executed SQL

Returns:

String, the alternative SQL, null if no alternative

getConvert

public Convert getConvert()

Description copied from interface: AdempiereDatabase

Get Convert implementation for this DB adapter

Specified by:

getConvert in interface AdempiereDatabase

Returns:

Convert instance

getNextID

public int getNextID(String name)

Description copied from interface: AdempiereDatabase

Get next sequence number in this Sequence

Specified by:

getNextID in interface AdempiereDatabase

Parameters:

name - Sequence name

getNextID

public int getNextID(String name,
 String trxName)

Description copied from interface: AdempiereDatabase

Get next sequence number in this Sequence

Specified by:

getNextID in interface AdempiereDatabase

Parameters:

name - Sequence name

trxName - Transaction name

createSequence

public boolean createSequence(String name,
 int increment,
 int minvalue,
 int maxvalue,
 int start,
 String trxName)

Description copied from interface: AdempiereDatabase

Create Native Sequence

Specified by:

createSequence in interface AdempiereDatabase

Parameters:

name - Sequence Name

isQueryTimeoutSupported

public boolean isQueryTimeoutSupported()

Specified by:

isQueryTimeoutSupported in interface AdempiereDatabase

Returns:

true if jdbc driver support statement timeout

addPagingSQL

public String addPagingSQL(String sql,
 int start,
 int end)

Implemented using the fetch first and offset feature. use 1 base index for start and end parameter

Specified by:

addPagingSQL in interface AdempiereDatabase

Parameters:

sql -

start -

end -

Returns:

SQL with added paging clause

isPagingSupported

public boolean isPagingSupported()

Description copied from interface: AdempiereDatabase

Is the database have sql extension that return a subset of the query result

Specified by:

isPagingSupported in interface AdempiereDatabase

Returns:

true if DB support paging SQL

forUpdate

public boolean forUpdate(PO po,
 int timeout)

Description copied from interface: AdempiereDatabase

Lock PO for update

Specified by:

forUpdate in interface AdempiereDatabase

Returns:

true if lock is granted

getNameOfUniqueConstraintError

public String getNameOfUniqueConstraintError(Exception e)

Specified by:

getNameOfUniqueConstraintError in interface AdempiereDatabase

Returns:

unique constraint name

getForeignKeyConstraint

public String getForeignKeyConstraint(Exception e)

Description copied from interface: AdempiereDatabase

The "child record found error" contains the foreign key constraint name after the second occurrence of the opening double quote: ["].

Example:

ERROR: update or delete on table "m_product_category" violates foreign key constraint "mprodcategory_mdiscountsbreak" on table "m_discountschemabreak" Detail: Key (m_product_category_id)=(50000) is still referenced from table "m_discountschemabreak".

Specified by:

getForeignKeyConstraint in interface AdempiereDatabase

Returns:

constraint name

subsetClauseForCSV

public String subsetClauseForCSV(String columnName,
 String csv)

Specified by:

subsetClauseForCSV in interface AdempiereDatabase

csv - comma separated value

Returns:

subset sql clause

subsetFilterForCSV

public SQLFragment subsetFilterForCSV(String columnName,
 String csv)

Specified by:

subsetFilterForCSV in interface AdempiereDatabase

Returns:

sql filter for subset

intersectClauseForCSV

public String intersectClauseForCSV(String columnName,
 String csv)

Specified by:

intersectClauseForCSV in interface AdempiereDatabase

csv - comma separated value

Returns:

intersect sql clause

intersectClauseForCSV

public String intersectClauseForCSV(String columnName,
 String csv,
 boolean isNotClause)

Specified by:

intersectClauseForCSV in interface AdempiereDatabase

csv - comma separated value

Returns:

intersect sql clause

intersectFilterForCSV

public SQLFragment intersectFilterForCSV(String columnName,
 String csv)

Specified by:

intersectFilterForCSV in interface AdempiereDatabase

Returns:

sql filter for intersect

intersectFilterForCSV

public SQLFragment intersectFilterForCSV(String columnName,
 String csv,
 boolean isNotClause)

Specified by:

intersectFilterForCSV in interface AdempiereDatabase

Returns:

sql filter for intersect

getNumericDataType

public String getNumericDataType()

Specified by:

getNumericDataType in interface AdempiereDatabase

Returns:

numeric data type name

getCharacterDataType

public String getCharacterDataType()

Specified by:

getCharacterDataType in interface AdempiereDatabase

Returns:

fixed length character data type name

getVarcharDataType

public String getVarcharDataType()

Specified by:

getVarcharDataType in interface AdempiereDatabase

Returns:

variable length character data type name

getVarcharLengthSuffix

public String getVarcharLengthSuffix()

Specified by:

getVarcharLengthSuffix in interface AdempiereDatabase

Returns:

variable length character data type suffix

getBlobDataType

public String getBlobDataType()

Specified by:

getBlobDataType in interface AdempiereDatabase

Returns:

binary large object data type name

getClobDataType

public String getClobDataType()

Specified by:

getClobDataType in interface AdempiereDatabase

Returns:

character large object data type name

getJsonDataType

public String getJsonDataType()

Specified by:

getJsonDataType in interface AdempiereDatabase

Returns:

json object data type name

getTimestampDataType

public String getTimestampDataType()

Specified by:

getTimestampDataType in interface AdempiereDatabase

Returns:

time stamp data type name

getTimestampWithTimezoneDataType

public String getTimestampWithTimezoneDataType()

Specified by:

getTimestampWithTimezoneDataType in interface AdempiereDatabase

Returns:

timestamp with time zone type name

getUUIDDataType

public String getUUIDDataType()

Specified by:

getUUIDDataType in interface AdempiereDatabase

Returns:

UUID type name

getSQLDDL

public String getSQLDDL(MColumn column)

Specified by:

getSQLDDL in interface AdempiereDatabase

Returns:

DDL SQL statement for column

getSQLAdd

public String getSQLAdd(MTable table,
 MColumn column)

Get SQL Add command

Specified by:

getSQLAdd in interface AdempiereDatabase

Parameters:

table - table

Returns:

sql

getSQLModify

public String getSQLModify(MTable table,
 MColumn column,
 boolean setNullOption)

Get SQL Modify command

Specified by:

getSQLModify in interface AdempiereDatabase

Parameters:

table - table

setNullOption - generate null / not null statement

Returns:

sql separated by ;

isQueryTimeout

public boolean isQueryTimeout(SQLException ex)

Specified by:

isQueryTimeout in interface AdempiereDatabase

Returns:

true if ex is caused by query timeout

getTablePartitionService

public ITablePartitionService getTablePartitionService()

Description copied from interface: AdempiereDatabase

Get DB specific table partition support

Specified by:

getTablePartitionService in interface AdempiereDatabase

Returns:

ITablePartitionService instance

TO_Blob

public String TO_Blob(byte[] blob)

Description copied from interface: AdempiereDatabase

Convert blob to hex encoded string and return SQL function that will convert the hex encoded string back to blob

Specified by:

TO_Blob in interface AdempiereDatabase

Returns:

SQL statement