org.pentaho.di.core.database
Class OracleDatabaseMeta

java.lang.Object
  org.pentaho.di.core.database.BaseDatabaseMeta
      org.pentaho.di.core.database.OracleDatabaseMeta

All Implemented Interfaces:

Cloneable, DatabaseInterface

public class OracleDatabaseMeta
extends BaseDatabaseMeta
implements DatabaseInterface

Contains Oracle specific information through static final members

Since:

11-mrt-2005

Author:

Matt

Field Summary

Fields inherited from class org.pentaho.di.core.database.BaseDatabaseMeta

ATTRIBUTE_CLUSTER_DBNAME_PREFIX, ATTRIBUTE_CLUSTER_HOSTNAME_PREFIX, ATTRIBUTE_CLUSTER_PASSWORD_PREFIX, ATTRIBUTE_CLUSTER_PORT_PREFIX, ATTRIBUTE_CLUSTER_USERNAME_PREFIX, ATTRIBUTE_FORCE_IDENTIFIERS_TO_LOWERCASE, ATTRIBUTE_FORCE_IDENTIFIERS_TO_UPPERCASE, ATTRIBUTE_INITIAL_POOL_SIZE, ATTRIBUTE_IS_CLUSTERED, ATTRIBUTE_MAXIMUM_POOL_SIZE, ATTRIBUTE_MSSQL_DOUBLE_DECIMAL_SEPARATOR, ATTRIBUTE_POOLING_PARAMETER_PREFIX, ATTRIBUTE_PORT_NUMBER, ATTRIBUTE_PREFERRED_SCHEMA_NAME, ATTRIBUTE_PREFIX_EXTRA_OPTION, ATTRIBUTE_QUOTE_ALL_FIELDS, ATTRIBUTE_SQL_CONNECT, ATTRIBUTE_SUPPORTS_BOOLEAN_DATA_TYPE, ATTRIBUTE_USE_POOLING, ATTRIBUTE_USE_RESULT_STREAMING, AUTOINCREMENT_SQL_FOR_BATCH_ID, poolingParameters, SELECT_COUNT_STATEMENT, SEQUENCE_FOR_BATCH_ID

Constructor Summary

OracleDatabaseMeta()

Method Summary

boolean	checkIndexExists(Database database, String schemaName, String tableName, String[] idx_fields) Verifies on the specified database connection if an index exists on the fields with the specified name.
int[]	getAccessTypeList() Get the list of possible access types for a database.
String	getAddColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon) Generates the SQL statement to add a column to the specified table
int	getDefaultDatabasePort()
String	getDriverClass() Obtain the name of the JDBC driver class that we need to use!
String	getDropColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon) Generates the SQL statement to drop a column from the specified table
String	getExtraOptionsHelpText()
String	getFieldDefinition(ValueMetaInterface v, String tk, String pk, boolean use_autoinc, boolean add_fieldname, boolean add_cr) Describe a Value as a field in the database.
String	getLimitClause(int nrRows)
int	getMaxColumnsInIndex()
String	getModifyColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon) Generates the SQL statement to modify a column in the specified table
String[]	getReservedWords()
String	getSQLColumnExists(String columnname, String tablename)
String	getSQLCurrentSequenceValue(String sequenceName) Get the current value of a database sequence
String	getSQLListOfProcedures()
String	getSQLListOfSequences()
String	getSQLLockTables(String[] tableNames)
String	getSQLNextSequenceValue(String sequenceName) Get the SQL to get the next value of a sequence.
String	getSQLQueryColumnFields(String columnname, String tableName)
String	getSQLQueryFields(String tableName) Returns the minimal SQL to launch in order to determine the layout of the resultset for a given database table
String	getSQLSequenceExists(String sequenceName) Check if a sequence exists.
String	getSQLTableExists(String tablename)
String	getSQLUnlockTables(String[] tableNames)
String	getTablespaceDDL(VariableSpace variables, DatabaseMeta databaseMeta, String tablespace) Returns an empty string as most databases do not support tablespaces.
String	getURL(String hostname, String port, String databaseName)
String[]	getUsedLibraries()
boolean	needsToLockAllTables()
String	quoteSQLString(String string)
boolean	releaseSavepoint() Returns a false as Oracle does not allow for the releasing of savepoints.
boolean	requiresCreateTablePrimaryKeyAppend()
boolean	supportsAutoInc()
boolean	supportsErrorHandlingOnBatchUpdates()
boolean	supportsOptionsInURL() Oracle doesn't support options in the URL, we need to put these in a Properties object at connection time...
boolean	supportsPreparedStatementMetadataRetrieval() Most databases allow you to retrieve result metadata by preparing a SELECT statement.
boolean	supportsSequences()
boolean	supportsSynonyms()
boolean	useSchemaNameForTableList()

Methods inherited from class org.pentaho.di.core.database.BaseDatabaseMeta

addExtraOption, clone, generateColumnAlias, getAccessType, getAttributes, getBackwardsCompatibleSchemaTableCombination, getBackwardsCompatibleTable, getConnectionPoolingProperties, getConnectSQL, getDatabaseFactoryName, getDatabaseName, getDatabasePortNumberString, getDataTablespace, getDataTablespaceDDL, getEndQuote, getExtraOptionIndicator, getExtraOptions, getExtraOptionSeparator, getExtraOptionValueSeparator, getFunctionAverage, getFunctionCount, getFunctionMaximum, getFunctionMinimum, getFunctionSum, getHostname, getIndexTablespace, getIndexTablespaceDDL, getInitialPoolSize, getMaximumPoolSize, getMaxTextFieldLength, getMaxVARCHARLength, getName, getNextBatchId, getNextBatchIdUsingAutoIncSQL, getNextBatchIdUsingLockTables, getNextBatchIdUsingSequence, getNotFoundTK, getObjectId, getPartitioningInformation, getPassword, getPluginId, getPluginName, getPreferredSchemaName, getSchemaTableCombination, getSelectCountStatement, getServername, getSQLInsertAutoIncUnknownDimensionRow, getSQLListOfSchemas, getStartQuote, getSynonymTypes, getTableTypes, getTruncateTableStatement, getUsername, getValueFromResultSet, getViewTypes, getXulOverlayFile, isChanged, isDefaultingToUppercase, isDisplaySizeTwiceThePrecision, isExplorable, isFetchSizeSupported, isForcingIdentifiersToLowerCase, isForcingIdentifiersToUpperCase, isMySQLVariant, isPartitioned, isQuoteAllFields, isRequiringTransactionsOnQueries, isStreamingResults, isSystemTable, isUsingConnectionPool, isUsingDoubleDecimalAsSchemaTableSeparator, needsPlaceHolder, parseStatements, quoteReservedWords, requiresCastToVariousForIsNull, setAccessType, setAttributes, setChanged, setConnectionPoolingProperties, setConnectSQL, setDatabaseName, setDatabasePortNumberString, setDataTablespace, setForcingIdentifiersToLowerCase, setForcingIdentifiersToUpperCase, setHostname, setIndexTablespace, setInitialPoolSize, setMaximumPoolSize, setName, setObjectId, setPartitioned, setPartitioningInformation, setPassword, setPluginId, setPluginName, setPreferredSchemaName, setQuoteAllFields, setServername, setStreamingResults, setSupportsBooleanDataType, setUsername, setUsingConnectionPool, setUsingDoubleDecimalAsSchemaTableSeparator, supportsBatchUpdates, supportsBitmapIndex, supportsBooleanDataType, supportsCatalogs, supportsEmptyTransactions, supportsErrorHandling, supportsFloatRoundingOnUpdate, supportsGetBlob, supportsNewLinesInSQL, supportsRepository, supportsResultSetMetadataRetrievalOnly, supportsSchemas, supportsSequenceNoMaxValueOption, supportsSetCharacterStream, supportsSetLong, supportsSetMaxRows, supportsTimeStampToDateConversion, supportsTransactions, supportsViews, useSafePoints

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface org.pentaho.di.core.database.DatabaseInterface

addExtraOption, clone, generateColumnAlias, getAccessType, getAttributes, getConnectionPoolingProperties, getConnectSQL, getDatabaseFactoryName, getDatabaseName, getDatabasePortNumberString, getDataTablespace, getDataTablespaceDDL, getEndQuote, getExtraOptionIndicator, getExtraOptions, getExtraOptionSeparator, getExtraOptionValueSeparator, getFunctionAverage, getFunctionCount, getFunctionMaximum, getFunctionMinimum, getFunctionSum, getHostname, getIndexTablespace, getIndexTablespaceDDL, getInitialPoolSize, getMaximumPoolSize, getMaxTextFieldLength, getMaxVARCHARLength, getName, getNextBatchId, getNotFoundTK, getObjectId, getPartitioningInformation, getPassword, getPluginId, getPluginName, getPreferredSchemaName, getSchemaTableCombination, getSelectCountStatement, getServername, getSQLInsertAutoIncUnknownDimensionRow, getSQLListOfSchemas, getStartQuote, getSynonymTypes, getTableTypes, getTruncateTableStatement, getUsername, getValueFromResultSet, getViewTypes, getXulOverlayFile, isChanged, isDefaultingToUppercase, isDisplaySizeTwiceThePrecision, isExplorable, isFetchSizeSupported, isForcingIdentifiersToLowerCase, isForcingIdentifiersToUpperCase, isMySQLVariant, isPartitioned, isQuoteAllFields, isRequiringTransactionsOnQueries, isStreamingResults, isSystemTable, isUsingConnectionPool, isUsingDoubleDecimalAsSchemaTableSeparator, needsPlaceHolder, parseStatements, quoteReservedWords, requiresCastToVariousForIsNull, setAccessType, setAttributes, setChanged, setConnectionPoolingProperties, setConnectSQL, setDatabaseName, setDatabasePortNumberString, setDataTablespace, setForcingIdentifiersToLowerCase, setForcingIdentifiersToUpperCase, setHostname, setIndexTablespace, setInitialPoolSize, setMaximumPoolSize, setName, setObjectId, setPartitioned, setPartitioningInformation, setPassword, setPluginId, setPluginName, setPreferredSchemaName, setQuoteAllFields, setServername, setStreamingResults, setSupportsBooleanDataType, setUsername, setUsingConnectionPool, setUsingDoubleDecimalAsSchemaTableSeparator, supportsBatchUpdates, supportsBitmapIndex, supportsBooleanDataType, supportsCatalogs, supportsEmptyTransactions, supportsErrorHandling, supportsFloatRoundingOnUpdate, supportsGetBlob, supportsNewLinesInSQL, supportsRepository, supportsResultSetMetadataRetrievalOnly, supportsSchemas, supportsSequenceNoMaxValueOption, supportsSetCharacterStream, supportsSetLong, supportsSetMaxRows, supportsTimeStampToDateConversion, supportsTransactions, supportsViews, useSafePoints

Constructor Detail

OracleDatabaseMeta

public OracleDatabaseMeta()

Method Detail

getAccessTypeList

public int[] getAccessTypeList()

Description copied from interface: DatabaseInterface

Get the list of possible access types for a database.

Specified by:

getAccessTypeList in interface DatabaseInterface

Specified by:

getAccessTypeList in class BaseDatabaseMeta

Returns:

the list of possible access types for a database.

getDefaultDatabasePort

public int getDefaultDatabasePort()

Specified by:

getDefaultDatabasePort in interface DatabaseInterface

Overrides:

getDefaultDatabasePort in class BaseDatabaseMeta

Returns:

the default database port number

supportsAutoInc

public boolean supportsAutoInc()

Specified by:

supportsAutoInc in interface DatabaseInterface

Overrides:

supportsAutoInc in class BaseDatabaseMeta

Returns:

Whether or not the database can use auto increment type of fields (pk)

getLimitClause

public String getLimitClause(int nrRows)

Specified by:

getLimitClause in interface DatabaseInterface

Overrides:

getLimitClause in class BaseDatabaseMeta

Parameters:

nrRows - The number of rows to which we want to limit the result of the query.

Returns:

the clause after a select statement to limit the number of rows

See Also:

DatabaseInterface.getLimitClause(int)

getSQLQueryFields

public String getSQLQueryFields(String tableName)

Returns the minimal SQL to launch in order to determine the layout of the resultset for a given database table

Specified by:

getSQLQueryFields in interface DatabaseInterface

Overrides:

getSQLQueryFields in class BaseDatabaseMeta

Parameters:

tableName - The name of the table to determine the layout for

Returns:

The SQL to launch.

getSQLTableExists

public String getSQLTableExists(String tablename)

Specified by:

getSQLTableExists in interface DatabaseInterface

Overrides:

getSQLTableExists in class BaseDatabaseMeta

Parameters:

tablename - The table to verify the existance for

Returns:

The SQL to execute to verify if the given table exists. If an Exception is thrown for this SQL, we don't have the table.

getSQLColumnExists

public String getSQLColumnExists(String columnname,
                                 String tablename)

Specified by:

getSQLColumnExists in interface DatabaseInterface

Overrides:

getSQLColumnExists in class BaseDatabaseMeta

tablename - The table to verify the existance for

Returns:

The SQL to execute to verify if the given table exists. If an Exception is thrown for this SQL, we don't have the column.

getSQLQueryColumnFields

public String getSQLQueryColumnFields(String columnname,
                                      String tableName)

needsToLockAllTables

public boolean needsToLockAllTables()

Specified by:

needsToLockAllTables in interface DatabaseInterface

Overrides:

needsToLockAllTables in class BaseDatabaseMeta

Returns:

true if the database needs all repository tables to be locked, not just one ref table (R_REPOSITORY_LOG)

getDriverClass

public String getDriverClass()

Description copied from interface: DatabaseInterface

Obtain the name of the JDBC driver class that we need to use!

Specified by:

getDriverClass in interface DatabaseInterface

Returns:

the name of the JDBC driver class for the specific database

getURL

public String getURL(String hostname,
                     String port,
                     String databaseName)
              throws KettleDatabaseException

Specified by:

getURL in interface DatabaseInterface

Parameters:

hostname - the hostname

port - the port as a string

databaseName - the database name

Returns:

the URL to use for connecting to the database.

Throws:

KettleDatabaseException - in case a configuration error is detected.

supportsOptionsInURL

public boolean supportsOptionsInURL()

Oracle doesn't support options in the URL, we need to put these in a Properties object at connection time...

Specified by:

supportsOptionsInURL in interface DatabaseInterface

Overrides:

supportsOptionsInURL in class BaseDatabaseMeta

Returns:

true if the database supports connection options in the URL, false if they are put in a Properties object.

supportsSequences

public boolean supportsSequences()

Specified by:

supportsSequences in interface DatabaseInterface

Overrides:

supportsSequences in class BaseDatabaseMeta

Returns:

true if the database supports sequences

getSQLSequenceExists

public String getSQLSequenceExists(String sequenceName)

Check if a sequence exists.

Specified by:

getSQLSequenceExists in interface DatabaseInterface

Overrides:

getSQLSequenceExists in class BaseDatabaseMeta

Parameters:

sequenceName - The sequence to check

Returns:

The SQL to get the name of the sequence back from the databases data dictionary

getSQLCurrentSequenceValue

public String getSQLCurrentSequenceValue(String sequenceName)

Get the current value of a database sequence

Specified by:

getSQLCurrentSequenceValue in interface DatabaseInterface

Overrides:

getSQLCurrentSequenceValue in class BaseDatabaseMeta

Parameters:

sequenceName - The sequence to check

Returns:

The current value of a database sequence

getSQLNextSequenceValue

public String getSQLNextSequenceValue(String sequenceName)

Get the SQL to get the next value of a sequence. (Oracle only)

Specified by:

getSQLNextSequenceValue in interface DatabaseInterface

Overrides:

getSQLNextSequenceValue in class BaseDatabaseMeta

Parameters:

sequenceName - The sequence name

Returns:

the SQL to get the next value of a sequence. (Oracle only)

useSchemaNameForTableList

public boolean useSchemaNameForTableList()

Specified by:

useSchemaNameForTableList in interface DatabaseInterface

Overrides:

useSchemaNameForTableList in class BaseDatabaseMeta

Returns:

true if we need to supply the schema-name to getTables in order to get a correct list of items.

supportsSynonyms

public boolean supportsSynonyms()

Specified by:

supportsSynonyms in interface DatabaseInterface

Overrides:

supportsSynonyms in class BaseDatabaseMeta

Returns:

true if the database supports synonyms

getAddColumnStatement

public String getAddColumnStatement(String tablename,
                                    ValueMetaInterface v,
                                    String tk,
                                    boolean use_autoinc,
                                    String pk,
                                    boolean semicolon)

Generates the SQL statement to add a column to the specified table

Specified by:

getAddColumnStatement in interface DatabaseInterface

Parameters:

tablename - The table to add

v - The column defined as a value

tk - the name of the technical key field

use_autoinc - whether or not this field uses auto increment

pk - the name of the primary key field

semicolon - whether or not to add a semi-colon behind the statement.

Returns:

the SQL statement to add a column to the specified table

getDropColumnStatement

public String getDropColumnStatement(String tablename,
                                     ValueMetaInterface v,
                                     String tk,
                                     boolean use_autoinc,
                                     String pk,
                                     boolean semicolon)

Generates the SQL statement to drop a column from the specified table

Specified by:

getDropColumnStatement in interface DatabaseInterface

Overrides:

getDropColumnStatement in class BaseDatabaseMeta

Parameters:

tablename - The table to add

v - The column defined as a value

tk - the name of the technical key field

use_autoinc - whether or not this field uses auto increment

pk - the name of the primary key field

semicolon - whether or not to add a semi-colon behind the statement.

Returns:

the SQL statement to drop a column from the specified table

getModifyColumnStatement

public String getModifyColumnStatement(String tablename,
                                       ValueMetaInterface v,
                                       String tk,
                                       boolean use_autoinc,
                                       String pk,
                                       boolean semicolon)

Generates the SQL statement to modify a column in the specified table

Specified by:

getModifyColumnStatement in interface DatabaseInterface

Parameters:

tablename - The table to add

v - The column defined as a value

tk - the name of the technical key field

use_autoinc - whether or not this field uses auto increment

pk - the name of the primary key field

semicolon - whether or not to add a semi-colon behind the statement.

Returns:

the SQL statement to modify a column in the specified table

getFieldDefinition

public String getFieldDefinition(ValueMetaInterface v,
                                 String tk,
                                 String pk,
                                 boolean use_autoinc,
                                 boolean add_fieldname,
                                 boolean add_cr)

Description copied from interface: DatabaseInterface

Describe a Value as a field in the database.

Specified by:

getFieldDefinition in interface DatabaseInterface

Parameters:

v - The value to describe

tk - The field that's going to be the technical key

pk - The field that's going to be the primary key

use_autoinc - Use autoincrement or not

add_fieldname - Add the fieldname to the definition or not

add_cr - Add a cariage return at the end of the definition or not.

Returns:

a value described as a field in this database.

getReservedWords

public String[] getReservedWords()

Specified by:

getReservedWords in interface DatabaseInterface

Overrides:

getReservedWords in class BaseDatabaseMeta

Returns:

an array of reserved words for the database type...

getSQLListOfProcedures

public String getSQLListOfProcedures()

Specified by:

getSQLListOfProcedures in interface DatabaseInterface

Overrides:

getSQLListOfProcedures in class BaseDatabaseMeta

Returns:

The SQL on this database to get a list of stored procedures.

getSQLLockTables

public String getSQLLockTables(String[] tableNames)

Specified by:

getSQLLockTables in interface DatabaseInterface

Overrides:

getSQLLockTables in class BaseDatabaseMeta

Parameters:

tableNames - The names of the tables to lock

Returns:

The SQL command to lock database tables for write purposes. null is returned in case locking is not supported on the target database. null is the default value

getSQLUnlockTables

public String getSQLUnlockTables(String[] tableNames)

Specified by:

getSQLUnlockTables in interface DatabaseInterface

Overrides:

getSQLUnlockTables in class BaseDatabaseMeta

Parameters:

tableNames - The names of the tables to unlock

Returns:

The SQL command to unlock database tables. null is returned in case locking is not supported on the target database. null is the default value

getExtraOptionsHelpText

public String getExtraOptionsHelpText()

Specified by:

getExtraOptionsHelpText in interface DatabaseInterface

Overrides:

getExtraOptionsHelpText in class BaseDatabaseMeta

Returns:

extra help text on the supported options on the selected database platform.

getUsedLibraries

public String[] getUsedLibraries()

Specified by:

getUsedLibraries in interface DatabaseInterface

Returns:

the required libraries (in libext) for this database connection.

checkIndexExists

public boolean checkIndexExists(Database database,
                                String schemaName,
                                String tableName,
                                String[] idx_fields)
                         throws KettleDatabaseException

Verifies on the specified database connection if an index exists on the fields with the specified name.

Specified by:

checkIndexExists in interface DatabaseInterface

Overrides:

checkIndexExists in class BaseDatabaseMeta

Parameters:

database - a connected database

schemaName -

tableName -

idxFields -

Returns:

true if the index exists, false if it doesn't.

Throws:

KettleException

KettleDatabaseException

requiresCreateTablePrimaryKeyAppend

public boolean requiresCreateTablePrimaryKeyAppend()

Specified by:

requiresCreateTablePrimaryKeyAppend in interface DatabaseInterface

Overrides:

requiresCreateTablePrimaryKeyAppend in class BaseDatabaseMeta

Returns:

true if we need to append the PRIMARY KEY block in the create table block after the fields, required for Caché.

supportsPreparedStatementMetadataRetrieval

public boolean supportsPreparedStatementMetadataRetrieval()

Most databases allow you to retrieve result metadata by preparing a SELECT statement.

Specified by:

supportsPreparedStatementMetadataRetrieval in interface DatabaseInterface

Overrides:

supportsPreparedStatementMetadataRetrieval in class BaseDatabaseMeta

Returns:

true if the database supports retrieval of query metadata from a prepared statement. False if the query needs to be executed first.

getMaxColumnsInIndex

public int getMaxColumnsInIndex()

Specified by:

getMaxColumnsInIndex in interface DatabaseInterface

Overrides:

getMaxColumnsInIndex in class BaseDatabaseMeta

Returns:

The maximum number of columns in a database, <=0 means: no known limit

getSQLListOfSequences

public String getSQLListOfSequences()

Specified by:

getSQLListOfSequences in interface DatabaseInterface

Overrides:

getSQLListOfSequences in class BaseDatabaseMeta

Returns:

The SQL on this database to get a list of sequences.

quoteSQLString

public String quoteSQLString(String string)

Specified by:

quoteSQLString in interface DatabaseInterface

Overrides:

quoteSQLString in class BaseDatabaseMeta

Parameters:

string -

Returns:

A string that is properly quoted for use in an Oracle SQL statement (insert, update, delete, etc)

releaseSavepoint

public boolean releaseSavepoint()

Returns a false as Oracle does not allow for the releasing of savepoints.

Specified by:

releaseSavepoint in interface DatabaseInterface

Overrides:

releaseSavepoint in class BaseDatabaseMeta

Returns:

getTablespaceDDL

public String getTablespaceDDL(VariableSpace variables,
                               DatabaseMeta databaseMeta,
                               String tablespace)

Returns an empty string as most databases do not support tablespaces. Subclasses can override this method to generate the DDL.

Overrides:

getTablespaceDDL in class BaseDatabaseMeta

Parameters:

VariableSpace - variables needed for variable substitution.

DatabaseMeta - databaseMeta needed for it's quoteField method. Since we are doing variable substitution we need to meta so that we can act on the variable substitution first and then the creation of the entire string that will be retuned.

String - tablespaceName name of the tablespace.

Returns:

String the TABLESPACE tablespaceName section of an Oracle CREATE DDL statement.

supportsErrorHandlingOnBatchUpdates

public boolean supportsErrorHandlingOnBatchUpdates()

Specified by:

supportsErrorHandlingOnBatchUpdates in interface DatabaseInterface

Overrides:

supportsErrorHandlingOnBatchUpdates in class BaseDatabaseMeta

Returns:

true if the database supports error handling (recovery of failure) while doing batch updates.