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 Details

  • 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:

    • 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 public String getDataType(String columnName, int displayType, int precision, boolean defaultValue)
      Deprecated.
      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

    • 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

    • 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