/*
 * JBoss, Home of Professional Open Source.
 * See the COPYRIGHT.txt file distributed with this work for information
 * regarding copyright ownership.  Some portions may be licensed
 * to Red Hat, Inc. under one or more contributor license agreements.
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 * Modified by
 * @author  Data Virtuality GmbH
 * @date    $Date$
 *
 */
// https://issues.jboss.org/browse/TEIID-2576
// https://issues.jboss.org/browse/TEIID-3282
// https://issues.jboss.org/browse/TEIID-3414
// https://issues.jboss.org/browse/TEIID-4008
// https://issues.jboss.org/browse/TEIID-4631
// https://issues.jboss.org/browse/TEIID-3046
// https://issues.jboss.org/browse/TEIID-5161
// https://issues.jboss.org/browse/TEIID-2452
// https://issues.jboss.org/browse/TEIID-2281
// https://issues.jboss.org/browse/TEIID-2188
// https://issues.jboss.org/browse/TEIID-5327
// https://issues.jboss.org/browse/TEIID-2459
// https://issues.jboss.org/browse/TEIID-4134
// https://issues.jboss.org/browse/TEIID-3530
// https://issues.redhat.com/browse/TEIID-5313
// https://issues.redhat.com/browse/TEIID-4395
// https://github.com/teiid/teiid/commit/368862d9ea7612a0fba06b772fd17e4aabe2d677
// https://issues.redhat.com/browse/TEIID-2384
// https://issues.redhat.com/browse/TEIID-2348
// https://github.com/teiid/teiid/commit/fe313fa52c059b1d9ae3171cab557aba9fca200a
package org.teiid.translator.jdbc.oracle;

import com.datavirtuality.dv.core.interfaces.DVCaller;

import java.io.Reader;

import org.jboss.jca.adapters.jdbc.WrappedConnection;
import org.teiid.GeometryInputSource;
import org.teiid.core.types.BinaryType;
import org.teiid.core.types.DataTypeManager;
import org.teiid.language.Array;
import org.teiid.language.*;
import org.teiid.language.Comparison.Operator;
import org.teiid.language.Like.MatchMode;
import org.teiid.language.SQLConstants.Tokens;
import org.teiid.language.SetQuery.Operation;
import org.teiid.language.visitor.CollectorVisitor;
import org.teiid.language.visitor.SQLStringVisitor;
import org.teiid.logging.LogConstants;
import org.teiid.logging.LogManager;
import org.teiid.metadata.AbstractMetadataRecord;
import org.teiid.metadata.Column;
import org.teiid.metadata.MetadataFactory;
import org.teiid.translator.*;
import org.teiid.translator.TypeFacility.RUNTIME_CODES;
import org.teiid.translator.jdbc.*;
import org.teiid.translator.jdbc.JDBCMetadataProcessor.ITypeConverter;
import org.teiid.translator.jdbc.plugin.JDBCPlugin;

import java.lang.reflect.Method;
import java.sql.*;
import java.sql.Date;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.*;
import java.util.regex.Pattern;

import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.*;

@Translator(name="oracle", description="A translator for Oracle 9i Database or later")
public class OracleExecutionFactory extends JDBCExecutionFactory {
	private static final double DOUBLE_MIN = -9.99999999999999E+125;
	private static final double DOUBLE_MAX = 9.99999999999999E+125;
	public static final double UNDERFLOW_POS = 2.225E-307;
	public static final double UNDERFLOW_NEG = -2.225E-307;
	private static final String BINARY_FLOAT = "BINARY_FLOAT";
	private static final String BINARY_DOUBLE = "BINARY_DOUBLE";
	private static final String BINARY = "BINARY";
	private static final int BINARY_MAX = 2000;
	private static final String TO_NCHAR = "TO_NCHAR"; //$NON-NLS-1$

	// EXTENSION
	// ISSUE: DVCORE-2813
	// FIXED BY: Salvatore Raunich
	// DATE: 2015.06.18
	public OracleExecutionFactory() {
		//older oracle instances seem to have issues with large numbers of bindings
		setUseBindingsForDependentJoin(false);
		setSupportsCreateTableAsSelect(true);
		setSupportsMd5Function(true);
		setSupportsNativeQueries(true);
		setMaxStringSize(2000);
	}
	// END EXTENSION

	private static class OracleRelateModifier extends TemplateFunctionModifier {
		public OracleRelateModifier(String mask) {
			super("SDO_RELATE(", 0, ", ", 1, ", 'mask=" + mask + "')"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		}
	}

	// EXTENSION
	// ISSUE: DV-99
	// FIXED BY: Salvatore Raunich
	// Applied solution from TEIID-2477
	// DATE: 2013.11.08
	public static final Version NINE_0 = Version.getVersion("9.0"); //$NON-NLS-1$
    public static final Version NINE_2 = Version.getVersion("9.2"); //$NON-NLS-1$
	// END EXTENSION
	public static final Version ELEVEN = Version.getVersion("11.0");
	public static final Version ELEVEN_2 = Version.getVersion("11.2");
	public static final Version ELEVEN_2_0_4 = Version.getVersion("11.2.0.4"); //$NON-NLS-1$
	private static final Version TWELVE = Version.getVersion("12.0");

	private static final String TIME_FORMAT = "HH24:MI:SS"; //$NON-NLS-1$
	private static final String DATE_FORMAT = "YYYY-MM-DD"; //$NON-NLS-1$
	private static final String DATETIME_FORMAT = DATE_FORMAT + " " + TIME_FORMAT; //$NON-NLS-1$
	private static final String TIMESTAMP_FORMAT = DATETIME_FORMAT + ".FF";  //$NON-NLS-1$

    public final static String HINT_PREFIX = "/*+"; //$NON-NLS-1$
    public static final String HINT_SUFFIX = "*/";  //$NON-NLS-1$
    public final static String DUAL = "DUAL"; //$NON-NLS-1$
    public final static String ROWNUM = "ROWNUM"; //$NON-NLS-1$
    public final static String SEQUENCE = ":SEQUENCE="; //$NON-NLS-1$
	/*
	 * Spatial Functions
	 */
	public static final String RELATE = "sdo_relate"; //$NON-NLS-1$
	public static final String NEAREST_NEIGHBOR = "sdo_nn"; //$NON-NLS-1$
	public static final String FILTER = "sdo_filter"; //$NON-NLS-1$
	public static final String WITHIN_DISTANCE = "sdo_within_distance"; //$NON-NLS-1$
	public static final String NEAREST_NEIGHBOR_DISTANCE = "sdo_nn_distance"; //$NON-NLS-1$
	public static final String ORACLE_SDO = "Oracle-SDO"; //$NON-NLS-1$

    private static final Pattern INTERVAL_YM_REGEX = Pattern.compile("interval\\s+year\\s*\\(\\s*\\d\\s*\\)\\s*to\\s+month", Pattern.CASE_INSENSITIVE);
    private static final Pattern INTERVAL_DS_REGEX = Pattern.compile("interval\\s+day\\s*\\(\\s*\\d\\s*\\)\\s*to\\s+second\\s*\\(\\s*\\d\\s*\\)", Pattern.CASE_INSENSITIVE);
	private static final int MAX_INTERVALYM_BYTE_SIZE = 16; /* fix the size by oracle's standard to 5 bytes */
	private static final int MAX_INTERVALDS_BYTE_SIZE = 32; /* fix the size by oracle's standard to 11 bytes */

	// BUG: hour()/minute()/second() functions fail in oracle for DATE columns
	// ISSUE: DV-78
	// SOLUTION: wrapped value to double convert
	// FIXED BY: Roman Pedchenko
	// DATE: 2013.06.17,2013.06.28
	// Adopted from https://issues.jboss.org/browse/TEIID-2522
	private final class DateAwareExtract extends ExtractFunctionModifier {
		@Override
		public List<?> translate(Function function) {
			Expression ex = function.getParameters().get(0);
			if ((ex instanceof ColumnReference && "date".equalsIgnoreCase(((ColumnReference)ex).getMetadataObject().getNativeType())) //$NON-NLS-1$
					|| (!(ex instanceof ColumnReference) && !(ex instanceof Literal) && !(ex instanceof Function))) {
				ex = ConvertModifier.createConvertFunction(getLanguageFactory(), function.getParameters().get(0), TypeFacility.RUNTIME_NAMES.TIMESTAMP);
				function.getParameters().set(0, ex);
			}
			return super.translate(function);
		}
	}
	// END BUG

	// EXTENSION
	// ISSUE: DVCORE-532, DVCORE-731, DVCORE-744, DVCORE-1197, DVCORE-1208
	// FIXED BY: Roman Pedchenko, Salvatore Raunich
	// DATE: 2013.07.09,2013.07.10,2013.10.21,2013.12.18,2014.05.13,2014.07.02
	// workaround for Oracle types based on Oracle8Platform class data
	public ITypeConverter getTypeConverter() {
		return new ITypeConverter() {
			@Override
			public String getRuntimeType(int type, String typeName, int precision, int scale) {
				if ("FLOAT".equals(typeName)) {
					switch (precision) {
                        case 63:
                            return DataTypeManager.DefaultDataTypes.FLOAT;
                        case 126:
                            return DataTypeManager.DefaultDataTypes.DOUBLE;
					}
				}
				if (BINARY_FLOAT.equals(typeName)) {
					return DataTypeManager.DefaultDataTypes.FLOAT;
				}
				if (BINARY_DOUBLE.equals(typeName)) {
					return DataTypeManager.DefaultDataTypes.DOUBLE;
				}
				if ("DATE".equals(typeName)) {
					if (dateAsTimestamp())
						return DataTypeManager.DefaultDataTypes.TIMESTAMP;
					return DataTypeManager.DefaultDataTypes.DATE;
				}
				if (typeName != null && typeName.startsWith("TIMESTAMP(")) {
					return DataTypeManager.DefaultDataTypes.TIMESTAMP;
				}
				if ("NVARCHAR2".equals(typeName))
					return DataTypeManager.DefaultDataTypes.STRING;
				if ("NCHAR".equals(typeName) || "CHAR".equals(typeName)) {
					if (precision == 1)
						return DataTypeManager.DefaultDataTypes.CHAR;
					return DataTypeManager.DefaultDataTypes.STRING;
				}
				if ("NCLOB".equals(typeName))
					return DataTypeManager.DefaultDataTypes.CLOB;
				if (typeName != null && typeName.startsWith("INTERVAL")) {
					if (intervalMatchesRegex(INTERVAL_YM_REGEX, typeName)) {
						return DataTypeManager.DefaultDataTypes.STRING;
					}
					if (intervalMatchesRegex(INTERVAL_DS_REGEX, typeName)) {
						return DataTypeManager.DefaultDataTypes.STRING;
					}
				}
				return null;
			}

			@Override
			protected String usualNumericCheck(String typeName, int precision, int scale) {
				String type = noBooleanNumericCheck(typeName, precision, scale);
				if (type != null)
					return type;
				return super.usualNumericCheck(typeName, precision, scale);
			}

			@Override
			public void tuneColumn(Column column, int type, String typeName, int precision, int scale) {
				if (type == Types.DECIMAL || type == Types.NUMERIC) {
					if (precision == 0 && scale == -127) {
						column.setPrecision(266);
						column.setScale(140);
					} else if (scale < 0)
						column.setScale(0);
				}
				if (BINARY_FLOAT.equals(typeName)) {
					column.setProperty(BINARY, BINARY_FLOAT);
				}
				if (BINARY_DOUBLE.equals(typeName)) {
					column.setProperty(BINARY, BINARY_DOUBLE);
				}
                if (typeName != null && typeName.startsWith("INTERVAL")) {
					if (intervalMatchesRegex(INTERVAL_YM_REGEX, typeName)) {
						int columnSize = MAX_INTERVALYM_BYTE_SIZE;
						column.setPrecision(columnSize);
						column.setLength(columnSize);
					}
					if (intervalMatchesRegex(INTERVAL_DS_REGEX, typeName)) {
						int columnSize1 = MAX_INTERVALDS_BYTE_SIZE;
						column.setPrecision(columnSize1);
						column.setLength(columnSize1);
					}
                }
				super.tuneColumn(column, type, typeName, precision, scale);
			}
		};
	}
	// END EXTENSION

	public boolean intervalMatchesRegex(Pattern pattern, String typeName) {
		return pattern.matcher(typeName).matches();
	}

	@Override
	public Object retrieveValue(ResultSet results, int columnIndex, Class<?> expectedType) throws SQLException {
		if (Date.class == expectedType) {
			final Object d = super.retrieveValue(results, columnIndex, expectedType);
			if (!(d instanceof Date))
				return d;
			final Calendar cal = getDatabaseCalendar();
			cal.setTime((Date) d);
			cal.set(Calendar.HOUR_OF_DAY, 0);
			cal.set(Calendar.MINUTE, 0);
			cal.set(Calendar.SECOND, 0);
			cal.set(Calendar.MILLISECOND, 0);
			return new Date(cal.getTime().getTime());
		}
		if (Object.class == expectedType) {
			Object obj = results.getObject(columnIndex);
			if (obj != null) {
				String className = obj.getClass().getCanonicalName();
				if (className.equals("oracle.sql.TIMESTAMP")) {
					try {
						Method method = obj.getClass().getMethod("timestampValue");
						return method.invoke(obj);
					} catch (Exception e) {
						return Timestamp.valueOf(obj.toString());
					}
				} else if (className.equals("oracle.sql.TIMESTAMPTZ")) {
					try {
						// TIMESTAMPTZ needs connection in the timestampValue method
						Method method = obj.getClass().getMethod("timestampValue", Connection.class);
						Connection connection = results.getStatement().getConnection();
						return method.invoke(obj, connection.unwrap(WrappedConnection.class).getUnderlyingConnection());
					} catch (Exception e) {
						return Timestamp.valueOf(obj.toString());
					}
				} else if (className.equals("oracle.sql.TIMESTAMPLTZ")) {
					try {
						Method method = obj.getClass().getMethod("timestampValue", Connection.class, Calendar.class);
						Connection connection = results.getStatement().getConnection();
						return method.invoke(obj, connection.unwrap(WrappedConnection.class).getUnderlyingConnection(), getDatabaseCalendar());
					} catch (Exception e) {
						return Timestamp.valueOf(obj.toString());
					}
				}
			}
		}
		return super.retrieveValue(results, columnIndex, expectedType);
	}

	/*
	 * Handling for cursor return values
	 */
    static final class RefCursorType {
    }

	static int CURSOR_TYPE = -10;
	//static final String REF_CURSOR = "REF CURSOR"; //$NON-NLS-1$

	/*
	 * handling for char bindings
	 */
    static final class FixedCharType {
    }

	static int FIXED_CHAR_TYPE = 999;

	private boolean oracleSuppliedDriver = true;

    public void start() throws TranslatorException {
        super.start();

        registerFunctionModifier(SourceSystemFunctions.CHAR, new AliasModifier("chr")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.LCASE, new AliasModifier("lower")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("upper")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("nvl")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("ln")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.CEILING, new AliasModifier("ceil")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.LOG10, new Log10FunctionModifier(getLanguageFactory()));
    	// BUG: hour()/minute()/second() functions fail in oracle for DATE columns
    	// ISSUE: DV-78
    	// SOLUTION: wrapped value to double convert
    	// FIXED BY: Roman Pedchenko
    	// DATE: 2013.06.17,2013.06.28
    	// Adopted from https://issues.jboss.org/browse/TEIID-2522
//        registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractFunctionModifier());
        registerFunctionModifier(SourceSystemFunctions.HOUR, new DateAwareExtract());
    	// END BUG
        registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier());
    	// BUG: hour()/minute()/second() functions fail in oracle for DATE columns
    	// ISSUE: DV-78
    	// SOLUTION: wrapped value to double convert
    	// FIXED BY: Roman Pedchenko
    	// DATE: 2013.06.17,2013.06.28
    	// Adopted from https://issues.jboss.org/browse/TEIID-2522
//        registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractFunctionModifier());
//        registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractFunctionModifier());
        registerFunctionModifier(SourceSystemFunctions.MINUTE, new DateAwareExtract());
        registerFunctionModifier(SourceSystemFunctions.SECOND, new DateAwareExtract());
    	// END BUG
        registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier());
        registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractFunctionModifier());
        registerFunctionModifier(SourceSystemFunctions.MONTHNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Month"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.DAYNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Day"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.WEEK, new DayWeekQuarterFunctionModifier("IW"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.QUARTER, new DayWeekQuarterFunctionModifier("Q"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new DayWeekQuarterFunctionModifier("D"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new DayWeekQuarterFunctionModifier("DDD"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory(), "INSTR", true)); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory()));
        registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory()));
        registerFunctionModifier(SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory()));
		registerFunctionModifier(SourceSystemFunctions.CONCAT2, new AliasModifier("||")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.COT, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				function.setName(SourceSystemFunctions.TAN);
				return Arrays.asList(getLanguageFactory().createFunction(SourceSystemFunctions.DIVIDE_OP, new Expression[] {new Literal(1, TypeFacility.RUNTIME_TYPES.INTEGER), function}, TypeFacility.RUNTIME_TYPES.DOUBLE));
			}
		});

        //spatial functions
        registerFunctionModifier(OracleExecutionFactory.RELATE, new OracleSpatialFunctionModifier());
        registerFunctionModifier(OracleExecutionFactory.NEAREST_NEIGHBOR, new OracleSpatialFunctionModifier());
        registerFunctionModifier(OracleExecutionFactory.FILTER, new OracleSpatialFunctionModifier());
        registerFunctionModifier(OracleExecutionFactory.WITHIN_DISTANCE, new OracleSpatialFunctionModifier());

        registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new OracleFormatFunctionModifier("TO_TIMESTAMP(")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.FORMATTIMESTAMP, new OracleFormatFunctionModifier("TO_CHAR(")); //$NON-NLS-1$

        registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("DBMS_RANDOM.VALUE")); //$NON-NLS-1$

		registerFunctionModifier(SourceSystemFunctions.TIMESTAMPADD, new TimestampAddModifier());

		// EXTENSION
		// ISSUE: DV-96
		// FIXED BY: Salvatore Raunich
		// DATE: 2013.11.13
        /*
        FunctionModifier timestampAddModifier = (FunctionModifier) DVCaller.call(29, 1);
        if (timestampAddModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.TIMESTAMPADD, timestampAddModifier);
        }
        */
        FunctionModifier timestampDiffModifier = (FunctionModifier) DVCaller.call(29, 2);
        if (timestampDiffModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.TIMESTAMPDIFF, timestampDiffModifier);
        }
		// END EXTENSION
        
		FunctionModifier curdateModifier = (FunctionModifier) DVCaller.call(131);
        if (curdateModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.CURDATE, curdateModifier);
        	registerFunctionModifier(SourceSystemFunctions.NOW, curdateModifier);
        }

        //add in type conversion
        ConvertModifier convertModifier = new ConvertModifier();
		convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.CHAR, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				if (isNonAscii(function.getParameters().get(0))) {
					((Literal)function.getParameters().get(1)).setValue("nchar(1)"); //$NON-NLS-1$
				} else {
					((Literal)function.getParameters().get(1)).setValue("char(1)"); //$NON-NLS-1$
				}
				return null;
			}
		});
    	convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); //$NON-NLS-1$
    	convertModifier.addTypeMapping("date", FunctionModifier.DATE, FunctionModifier.TIME); //$NON-NLS-1$
    	convertModifier.addTypeMapping("timestamp", FunctionModifier.TIMESTAMP); //$NON-NLS-1$
    	convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new FunctionModifier() {
    		@Override
    		public List<?> translate(Function function) {
    			return Arrays.asList("case when ", function.getParameters().get(0), " is null then null else to_date('1970-01-01 ' || to_char(",function.getParameters().get(0),", 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    		}
    	});
    	convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				return Arrays.asList("trunc(cast(",function.getParameters().get(0)," AS date))"); //$NON-NLS-1$ //$NON-NLS-2$
			}
		});
    	convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", DATE_FORMAT)); //$NON-NLS-1$ 
    	convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", TIME_FORMAT)); //$NON-NLS-1$
    	convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				//if column and type is date, just use date format
				Expression ex = function.getParameters().get(0);
				String format = TIMESTAMP_FORMAT; 
				if (ex instanceof ColumnReference && "date".equalsIgnoreCase(((ColumnReference)ex).getMetadataObject().getNativeType())) { //$NON-NLS-1$
					format = DATETIME_FORMAT; 
				} else if (!(ex instanceof Literal) && !(ex instanceof Function)) {
					//this isn't needed in every case, but it's simpler than inspecting the expression more
					ex = ConvertModifier.createConvertFunction(getLanguageFactory(), function.getParameters().get(0), TypeFacility.RUNTIME_NAMES.TIMESTAMP);
				}
				return Arrays.asList("to_char(", ex, ", '", format, "')"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			}
		});
    	convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.DATE, new ConvertModifier.FormatModifier("to_date", DATE_FORMAT)); //$NON-NLS-1$ 
    	convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIME, new ConvertModifier.FormatModifier("to_date", TIME_FORMAT)); //$NON-NLS-1$ 
    	convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier("to_timestamp", TIMESTAMP_FORMAT)); //$NON-NLS-1$ 
		convertModifier.addTypeConversion(new FunctionModifier() {

			ConvertModifier.FormatModifier toChar = new ConvertModifier.FormatModifier("to_char"); //$NON-NLS-1$
			ConvertModifier.FormatModifier toNChar = new ConvertModifier.FormatModifier(TO_NCHAR);

			@Override
			public List<?> translate(Function function) {
				if (isNonAscii(function.getParameters().get(0))) {
					return toNChar.translate(function);
				}
                if (function.getParameters().get(0).getType() == TypeFacility.RUNTIME_TYPES.CLOB) {
                    return Arrays.asList(function.getParameters().get(0).toString());
                }
				return toChar.translate(function);
			}
		}, FunctionModifier.STRING);

    	//NOTE: numeric handling in Oracle is split only between integral vs. floating/decimal types
    	convertModifier.addTypeConversion(new ConvertModifier.FormatModifier("to_number"), //$NON-NLS-1$
    			FunctionModifier.FLOAT, FunctionModifier.DOUBLE, FunctionModifier.BIGDECIMAL);
    	convertModifier.addTypeConversion(new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				if (Number.class.isAssignableFrom(function.getParameters().get(0).getType())) {
					return Arrays.asList("trunc(", function.getParameters().get(0), ")"); //$NON-NLS-1$ //$NON-NLS-2$
				}
				return Arrays.asList("trunc(to_number(", function.getParameters().get(0), "))"); //$NON-NLS-1$ //$NON-NLS-2$
			}
		}, 
		FunctionModifier.BYTE, FunctionModifier.SHORT, FunctionModifier.INTEGER, FunctionModifier.LONG,	FunctionModifier.BIGINTEGER);
    	convertModifier.addNumericBooleanConversions();
    	convertModifier.setWideningNumericImplicit(true);
    	registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier);
    	
    	addPushDownFunction(ORACLE_SDO, RELATE, STRING, STRING, STRING, STRING);
    	addPushDownFunction(ORACLE_SDO, RELATE, STRING, OBJECT, OBJECT, STRING);
    	addPushDownFunction(ORACLE_SDO, RELATE, STRING, STRING, OBJECT, STRING);
    	addPushDownFunction(ORACLE_SDO, RELATE, STRING, OBJECT, STRING, STRING);
    	addPushDownFunction(ORACLE_SDO, NEAREST_NEIGHBOR, STRING, STRING, OBJECT, STRING, INTEGER);
    	addPushDownFunction(ORACLE_SDO, NEAREST_NEIGHBOR, STRING, OBJECT, OBJECT, STRING, INTEGER);
    	addPushDownFunction(ORACLE_SDO, NEAREST_NEIGHBOR, STRING, OBJECT, STRING, STRING, INTEGER);
    	addPushDownFunction(ORACLE_SDO, NEAREST_NEIGHBOR_DISTANCE, INTEGER, INTEGER);
    	addPushDownFunction(ORACLE_SDO, WITHIN_DISTANCE, STRING, OBJECT, OBJECT, STRING);
    	addPushDownFunction(ORACLE_SDO, WITHIN_DISTANCE, STRING, STRING, OBJECT, STRING);
    	addPushDownFunction(ORACLE_SDO, WITHIN_DISTANCE, STRING, OBJECT, STRING, STRING);
    	addPushDownFunction(ORACLE_SDO, FILTER, STRING, OBJECT, STRING, STRING);
    	addPushDownFunction(ORACLE_SDO, FILTER, STRING, OBJECT, OBJECT, STRING);
    	addPushDownFunction(ORACLE_SDO, FILTER, STRING, STRING, OBJECT, STRING);
	    registerFunctionModifier(SourceSystemFunctions.ST_ASBINARY, new AliasModifier("SDO_UTIL.TO_WKBGEOMETRY")); //$NON-NLS-1$
	    registerFunctionModifier(SourceSystemFunctions.ST_ASTEXT, new AliasModifier("SDO_UTIL.TO_WKTGEOMETRY")); //$NON-NLS-1$
	    registerFunctionModifier(SourceSystemFunctions.ST_ASGML, new AliasModifier("SDO_UTIL.TO_GMLGEOMETRY")); //$NON-NLS-1$

	    // Used instead of SDO_UTIL functions because it allows SRID to be specified.
	    // we need to use to_blob and to_clob to disambiguate
	    registerFunctionModifier(SourceSystemFunctions.ST_GEOMFROMWKB, new AliasModifier("SDO_GEOMETRY") { //$NON-NLS-1$

		    @Override
		    public List<?> translate(Function function) {
			    Expression ex = function.getParameters().get(0);
			    if (ex instanceof Parameter || ex instanceof Literal) {
				    function.getParameters().set(0, new Function("TO_BLOB", Arrays.asList(ex), TypeFacility.RUNTIME_TYPES.BLOB)); //$NON-NLS-1$
			    }
			    return super.translate(function);
		    }
	    });
	    registerFunctionModifier(SourceSystemFunctions.ST_GEOMFROMTEXT, new AliasModifier("SDO_GEOMETRY") { //$NON-NLS-1$

		    @Override
		    public List<?> translate(Function function) {
			    Expression ex = function.getParameters().get(0);
			    if (ex instanceof Parameter || ex instanceof Literal) {
				    function.getParameters().set(0, new Function("TO_CLOB", Arrays.asList(ex), TypeFacility.RUNTIME_TYPES.CLOB)); //$NON-NLS-1$
			    }
			    return super.translate(function);
		    }
	    });

	    registerFunctionModifier(SourceSystemFunctions.ST_DISTANCE, new TemplateFunctionModifier("SDO_GEOM.DISTANCE(", 0, ", ", 1, ", 0.005)")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

	    // Disjoint mask cannot be used with SDO_RELATE (says docs).
	    registerFunctionModifier(SourceSystemFunctions.ST_DISJOINT, new TemplateFunctionModifier("SDO_GEOM.RELATE(", 0, ", 'disjoint', ", 1,", 0.005)")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

	    registerFunctionModifier(SourceSystemFunctions.ST_CONTAINS, new OracleRelateModifier("contains")); //$NON-NLS-1$
	    registerFunctionModifier(SourceSystemFunctions.ST_CROSSES, new OracleRelateModifier("overlapbydisjoint")); //$NON-NLS-1$
	    registerFunctionModifier(SourceSystemFunctions.ST_INTERSECTS, new OracleRelateModifier("anyinteract")); //$NON-NLS-1$
	    registerFunctionModifier(SourceSystemFunctions.ST_OVERLAPS, new OracleRelateModifier("overlapbydisjoint")); //$NON-NLS-1$
	    registerFunctionModifier(SourceSystemFunctions.ST_TOUCHES, new OracleRelateModifier("touch")); //$NON-NLS-1$
	    registerFunctionModifier(SourceSystemFunctions.ST_EQUALS, new OracleRelateModifier("EQUAL")); //$NON-NLS-1$
	    //registerFunctionModifier(SourceSystemFunctions.ST_WITHIN, new OracleRelateModifier("inside")); //$NON-NLS-1$
	    registerFunctionModifier(SourceSystemFunctions.ST_SRID, new TemplateFunctionModifier("nvl(", 0, ".sdo_srid, 0)")); //$NON-NLS-1$ //$NON-NLS-2$

        registerFunctionModifier(SourceSystemFunctions.DATE_TRUNC, new BaseDateTruncFunctionModifier("TIMESTAMP") {
			@Override
			protected String getFunName() {
				return "TRUNC";
			}

			@Override
			protected Object getFirstParam(List<Expression> args) {
				return args.get(1);
			}

			@Override
			protected Object getSecondParam(List<Expression> args) {
                String datePart = args.get(0).toString();
                switch (datePart.toLowerCase()) {
                    case "'millennium'":
                        // round to closest
                        // round to closest
                    case "'century'":
                        return "'CC'";
                    case "'decade'":
                    case "'year'":
                        return "'YEAR'";
                    case "'quarter'":
                        return "'Q'";
                    case "'week'": // ISO
                        return "'IW'";
                    case "'sunday'":
                        return "'WW'";
                    case "'day'":
                        return "'DDD'";
                    case "'hour'":
                        return "'HH'";
                    case "'minute'":
                    case "'minutes'":
                        return "'MI'";
                    // Unsupported -> instead of doing this, shortcircuit the pushdown and call DVServer Impl
                    case "'second'":
                    case "'seconds'":
                    case "'millisecond'":
                    case "'milliseconds'":
                    case "'microseconds'":
                    case "'microsecond'":
                    case "'nanosecond'":
                    case "'nanoseconds'":
                        return "'MI'";
			}

                return datePart;
            }
		});

    	FunctionModifier stringAggModifier = (FunctionModifier) DVCaller.call(90, this);
        if (stringAggModifier != null) {
        	registerFunctionModifier("string_agg", stringAggModifier);
        }
    	DVCaller.call(85, this);
    }

	protected static final String IMPORTER_EXCLUDE_SCHEMAS = "importer.excludeSchemas";

	@SuppressWarnings("deprecation")
	protected void setExcludeSchemas(MetadataFactory metadataFactory) {
		if (metadataFactory.getImportProperties().getProperty(IMPORTER_EXCLUDE_SCHEMAS) == null) {
    		metadataFactory.getImportProperties().setProperty(IMPORTER_EXCLUDE_SCHEMAS,
    				"APEX_PUBLIC_USER,DIP,FLOWS_040100,FLOWS_020100,FLOWS_FILES,MDDATA,ORACLE_OCM,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR,XS$NULL,BI,HR,OE,PM,IX,SH,SYS,SYSTEM,MDSYS,CTXSYS");
    	}
	}

	@Override
	public void getMetadata(MetadataFactory metadataFactory, Connection conn) throws TranslatorException {
    	setExcludeSchemas(metadataFactory);
    	super.getMetadata(metadataFactory, conn);
	}

	public void handleInsertSequences(Insert insert) throws TranslatorException {
        /* 
         * If a missing auto_increment column is modeled with name in source indicating that an Oracle Sequence 
         * then pull the Sequence name out of the name in source of the column.
         */
    	if (!(insert.getValueSource() instanceof ExpressionValueSource)) {
    		return;
    	}
    	ExpressionValueSource values = (ExpressionValueSource)insert.getValueSource();
    	List<Column> allElements = insert.getTable().getMetadataObject().getColumns();
    	if (allElements.size() == values.getValues().size()) {
    		return;
    	}
    	
    	int index = 0;
    	List<ColumnReference> elements = insert.getColumns();
    	
    	for (Column element : allElements) {
    		if (!element.isAutoIncremented()) {
    			continue;
    		}
    		String name = element.getNameInSource();
    		int seqIndex = name.indexOf(SEQUENCE);
    		if (seqIndex == -1) {
    			continue;
    		}
    		boolean found = false;
    		while (index < elements.size()) {
    			if (element.equals(elements.get(index).getMetadataObject())) {
    				found = true;
    				break;
    			}
    			index++;
    		}
    		if (found) {
    			continue;
    		}
    		
            String sequence = name.substring(seqIndex + SEQUENCE.length());
            
            int delimiterIndex = sequence.indexOf(Tokens.DOT);
            if (delimiterIndex == -1) {
            	 throw new TranslatorException(JDBCPlugin.Event.TEIID11017, JDBCPlugin.Util.gs(JDBCPlugin.Event.TEIID11017, SEQUENCE, name));
            }
            String sequenceGroupName = sequence.substring(0, delimiterIndex);
            String sequenceElementName = sequence.substring(delimiterIndex + 1);
                
            NamedTable sequenceGroup = this.getLanguageFactory().createNamedTable(sequenceGroupName, null, null);
            ColumnReference sequenceElement = this.getLanguageFactory().createColumnReference(sequenceElementName, sequenceGroup, null, element.getJavaType());
            insert.getColumns().add(index, this.getLanguageFactory().createColumnReference(element.getName(), insert.getTable(), element, element.getJavaType()));
            values.getValues().add(index, sequenceElement);
		}
    }

    @Override
    public List<?> translateCommand(Command command, ExecutionContext context) {
    	if (command instanceof Insert) {
    		try {
				handleInsertSequences((Insert)command);
			} catch (TranslatorException e) {
				throw new RuntimeException(e);
			}
    	}
    	
    	if (!(command instanceof QueryExpression)) {
    		return null;
    	}
		QueryExpression queryCommand = (QueryExpression)command;
		if (queryCommand.getLimit() == null) {
			return null;
    	}
		Limit limit = queryCommand.getLimit();
		queryCommand.setLimit(null);
		
		if (command instanceof Select) {
			Select select = (Select)command;
			
			TableReference tr = select.getFrom().get(0);
			if (tr instanceof NamedTable && isDual((NamedTable)tr)) {
				if (limit.getRowOffset() > 0 || limit.getRowLimit() == 0) {
					//no data
					select.setWhere(new Comparison(new Literal(1, TypeFacility.RUNTIME_TYPES.INTEGER), new Literal(0, TypeFacility.RUNTIME_TYPES.INTEGER), Operator.EQ));
					return null;
				}
				return null; //dual does not allow a limit
			}
		}
		
    	List<Object> parts = new ArrayList<Object>();

    	// BUG: Incorrect syntax error in MS SQL Server in queries containing WITH, UNION ALL and LIMIT clauses
		// ISSUE: DV-123
		// SOLUTION: applied patch from TEIID-2864
		// FIXED by Salvatore Raunich
		// date 2014.02.25
		if (queryCommand.getWith() != null) {
			With with = queryCommand.getWith();
			queryCommand.setWith(null);
			parts.add(with);
		}		
		// BUG END
    	
    	parts.add("SELECT "); //$NON-NLS-1$
    	/*
    	 * if all of the columns are aliased, assume that names matter - it actually only seems to matter for
    	 * the first query of a set op when there is a order by.  Rather than adding logic to traverse up,
    	 * we just use the projected names
    	 */
    	boolean allAliased = true;
    	for (DerivedColumn selectSymbol : queryCommand.getProjectedQuery().getDerivedColumns()) {
			if (selectSymbol.getAlias() == null) {
				allAliased = false;
				break;
			}
		}
    	if (allAliased) {
	    	String[] columnNames = queryCommand.getColumnNames();
	    	for (int i = 0; i < columnNames.length; i++) {
	    		if (i > 0) {
	    			parts.add(", "); //$NON-NLS-1$
	    		}
	    		// EXTENSION
	    		// ISSUE: DVCORE-532
	    		// FIXED BY: Roman Pedchenko
	    		// DATE: 2013.07.10
	    		// workaround for Oracle types based on Oracle8Platform class data
//	    		parts.add(columnNames[i]);
	    		parts.add("\"" + columnNames[i] + "\"");
	    		// END EXTENSION
			}
    	} else {
        	parts.add("*"); //$NON-NLS-1$
    	}
		if (limit.getRowOffset() > 0) {
			parts.add(" FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM ("); //$NON-NLS-1$
		} else {
			parts.add(" FROM ("); //$NON-NLS-1$
		}
		parts.add(queryCommand);
		if (limit.getRowOffset() > 0) {
			parts.add(") VIEW_FOR_LIMIT WHERE ROWNUM <= "); //$NON-NLS-1$
			parts.add(limit.getRowLimit() + limit.getRowOffset());
			parts.add(") WHERE ROWNUM_ > "); //$NON-NLS-1$
			parts.add(limit.getRowOffset());
		} else {
			parts.add(") WHERE ROWNUM <= "); //$NON-NLS-1$
			parts.add(limit.getRowLimit());
		}
		return parts;
    }
    
	private boolean isDual(NamedTable table) {
		String groupName = null;
		AbstractMetadataRecord groupID = table.getMetadataObject();
		if(groupID != null) {              
		    groupName = SQLStringVisitor.getRecordName(groupID);
		} else {
		    groupName = table.getName();
		}
		return DUAL.equalsIgnoreCase(groupName);
	}

    @Override
    public boolean useAsInGroupAlias(){
        return false;
    }
    
    @Override
    public String getSetOperationString(Operation operation) {
    	if (operation == Operation.EXCEPT) {
    		return "MINUS"; //$NON-NLS-1$
    	}
    	return super.getSetOperationString(operation);
    }
    
    @Override
    public String getSourceComment(ExecutionContext context, Command command) {
    	String comment = super.getSourceComment(context, command);
    	
    	boolean usingPayloadComment = false;
    	if (context != null) {
	    	// Check for db hints
		    Object payload = context.getCommandPayload();
		    if (payload instanceof String) {
		        String payloadString = (String)payload;
		        if (payloadString.startsWith(HINT_PREFIX)) {
		        	int i = payloadString.indexOf(HINT_SUFFIX);
		        	if (i > 0 && payloadString.substring(i + 2).trim().length() == 0) {
			            comment += payloadString + " "; //$NON-NLS-1$
			            usingPayloadComment = true;
		        	} else {
		        		String msg = JDBCPlugin.Util.gs(JDBCPlugin.Event.TEIID11003, "Execution Payload", payloadString); //$NON-NLS-1$ 
		        		context.addWarning(new TranslatorException(msg));
		        		LogManager.logWarning(LogConstants.CTX_CONNECTOR, msg);
		        	}
		        }
		    }
    	}
    	
    	if (!usingPayloadComment && context != null) {
    		String hint = null;
    		hint = context.getSourceHint();
    		if (hint == null) {
    			hint = context.getGeneralHint();
    		}
    		if (hint != null) {
    			//append a source hint
    			if (!hint.contains(HINT_PREFIX)) {
    				comment += HINT_PREFIX + ' ' + hint + ' ' + HINT_SUFFIX + ' ';
    			} else {
    				String msg = JDBCPlugin.Util.gs(JDBCPlugin.Event.TEIID11003, "Source Hint", hint); //$NON-NLS-1$
    				context.addWarning(new TranslatorException(msg));
	        		LogManager.logWarning(LogConstants.CTX_CONNECTOR, msg);
    			}
    		}
    	}
    	
		if (command instanceof Select) {
	        //
	        // This simple algorithm determines the hint which will be added to the
	        // query.
	        // Right now, we look through all functions passed in the query
	        // (returned as a collection)
	        // Then we check if any of those functions are sdo_relate
	        // If so, the ORDERED hint is added, if not, it isn't
	        Collection<Function> col = CollectorVisitor.collectObjects(Function.class, command);
	        for (Function func : col) {
	            if (func.getName().equalsIgnoreCase(OracleExecutionFactory.RELATE)) {
	                return comment + "/*+ ORDERED */ "; //$NON-NLS-1$
	            }
	        }
		}
    	return comment;
    }
    
    /**
     * Don't fully qualify elements if table = DUAL or element = ROWNUM or special stuff is packed into name in source value.
     *  
     * @see org.teiid.language.visitor.SQLStringVisitor#skipGroupInElement(java.lang.String, java.lang.String)
     * @since 5.0
     */
    @Override
    public String replaceElementName(String group, String element) {        

        // Check if the element was modeled as using a Sequence
        int useIndex = element.indexOf(SEQUENCE);
        if (useIndex >= 0) {
        	String name = element.substring(0, useIndex);
        	if (group != null) {
        		return group + Tokens.DOT + name;
        	}
        	return name;
        }

        // Check if the group name should be discarded
        if((group != null && DUAL.equalsIgnoreCase(group)) || element.equalsIgnoreCase(ROWNUM)) {
            // Strip group if group or element are pseudo-columns
            return element;
        }
        
        return null;
    }

    @Override
    public boolean hasTimeType() {
    	return false;
    }

    @Override
    public void bindValue(PreparedStatement stmt, Object param, Class<?> paramType, int i) throws SQLException {
		if (paramType == FixedCharType.class) {
			stmt.setObject(i, param, FIXED_CHAR_TYPE);
			return;
		}
		if (param instanceof Character) {
			stmt.setNString(i, String.valueOf((char) param));
			return;
		}
    	if (java.util.Date.class.isAssignableFrom(paramType) && param != null) {
    		java.util.Date date = (java.util.Date) param;
    		if (date.after(get10kMinusOneDate()))
    			param = null;
    	}
		if (param instanceof BinaryType) {
			final byte[] orig = ((BinaryType) param).getBytesDirect();
			final byte[] dest;
			if (orig.length <= BINARY_MAX)
				dest = orig;
			else
				dest = Arrays.copyOf(orig, 2000);
			stmt.setObject(i, dest);
			return;
		}
		if (paramType == Time.class && param instanceof Time) {
			stmt.setTimestamp(i, new Timestamp(((Time) param).getTime()), getDatabaseCalendar());
			return;
		}
		if (param instanceof String) {
			stmt.setNString(i, stringCutConvertFail((String) param));
			return;
		}
    	super.bindValue(stmt, param, paramType, i);
    }

	@Override
	protected Object pretranslateDouble(Object param) {
		Double d = (Double) param;
    	if (getVersion().compareTo(ELEVEN) < 0) {
			d = preventOverflowDouble(d, DOUBLE_MIN, DOUBLE_MAX);
			d = preventUnderflow(d, UNDERFLOW_POS, UNDERFLOW_NEG);
		}
		return super.pretranslateDouble(d);
	}

	private static DecimalFormat BINARY_DOUBLE_FORMAT = new DecimalFormat("0.################E0",
			new DecimalFormatSymbols(Locale.ENGLISH));

	public Object translateLiteralFloat(float obj) {
		if (getVersion().compareTo(ELEVEN) < 0)
			return obj;
		return BINARY_DOUBLE_FORMAT.format(obj) + "f";
	}

	public Object translateLiteralDouble(double obj) {
		if (getVersion().compareTo(ELEVEN) < 0)
			return obj;
		return BINARY_DOUBLE_FORMAT.format(obj) + "d";
	}

	@Override
    public boolean useStreamsForLobs() {
    	return true;
    }
    
    @Override
    public NullOrder getDefaultNullOrder() {
    	return NullOrder.HIGH;
    }
    
    @Override
    public boolean supportsOrderByNullOrdering() {
    	return true;
    }    
    
    @Override
    public SQLConversionVisitor getSQLConversionVisitor() {
    	return new OracleSQLConversionVisitor(false);
    }

	@Override
	public SQLConversionVisitor getIndexVisitor() {
		return new OracleSQLConversionVisitor(true);
	}

	private class OracleSQLConversionVisitor extends SQLConversionVisitor {
		public OracleSQLConversionVisitor(boolean forceUnicodePrefix) {
			super(OracleExecutionFactory.this);
			this.forceUnicodePrefix = forceUnicodePrefix;
		}

		@Override
		// EXTENSION
		// ISSUE: DVCORE-2441
		// FIXED BY: Roman Pedchenko
		// DATE: 2014.10.23
		// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
		public void visit(Select select) {
			if (select.getFrom() == null || select.getFrom().isEmpty()) {
				select.setFrom(Arrays.asList((TableReference) new NamedTable(DUAL, null, null)));
			}
			super.visit(select);
		}

		@Override
		public void visit(Call call) {
			if (oracleSuppliedDriver && call.getReturnType() == null && call.getResultSetColumnTypes().length > 0 && call.getMetadataObject() != null && call.getMetadataObject().getProperty(SQLConversionVisitor.TEIID_NATIVE_QUERY, false) == null) {
				//oracle returns the resultset as a parameter
				call.setReturnType(RefCursorType.class);
			}
			super.visit(call);
		}

		@Override
		public void visit(Like obj) {
			if (obj.getMode() == MatchMode.REGEX) {
				if (obj.isNegated()) {
					buffer.append("NOT("); //$NON-NLS-1$
				}
				buffer.append("REGEXP_LIKE(");  //$NON-NLS-1$
				append(obj.getLeftExpression());
				buffer.append(", ");  //$NON-NLS-1$
				append(obj.getRightExpression());
				buffer.append(")");  //$NON-NLS-1$
				if (obj.isNegated()) {
					buffer.append(")");  //$NON-NLS-1$
				}
			} else {
				super.visit(obj);
			}
		}

		@Override
		// END EXTENSION
		public void visit(Comparison obj) {
			if (isFixedChar(obj.getLeftExpression())) {
				if (obj.getRightExpression() instanceof Literal) {
					Literal l = (Literal) obj.getRightExpression();
					l.setType(FixedCharType.class);
				} else if (obj.getRightExpression() instanceof Parameter) {
					Parameter p = (Parameter) obj.getRightExpression();
					p.setType(FixedCharType.class);
				}
			}
			super.visit(obj);
		}

		@Override
		protected void appendRightComparison(Comparison obj) {
			if (obj.getRightExpression() instanceof Array) {
				//oracle needs rhs arrays nested in extra parens
				buffer.append(Tokens.LPAREN);
				super.appendRightComparison(obj);
				buffer.append(Tokens.RPAREN);
			} else {
				super.appendRightComparison(obj);
			}
		}

		private boolean isFixedChar(Expression obj) {
			if (!isOracleSuppliedDriver() || !(obj instanceof ColumnReference)) {
				return false;
			}
			ColumnReference cr = (ColumnReference) obj;
			return (cr.getType() == TypeFacility.RUNTIME_TYPES.STRING || cr.getType() == TypeFacility.RUNTIME_TYPES.CHAR)
					&& cr.getMetadataObject() != null
					&& ("CHAR".equalsIgnoreCase(cr.getMetadataObject().getNativeType()) //$NON-NLS-1$
					|| "NCHAR".equalsIgnoreCase(cr.getMetadataObject().getNativeType())); //$NON-NLS-1$
		}

		public void visit(In obj) {
			if (isFixedChar(obj.getLeftExpression())) {
				for (Expression exp : obj.getRightExpressions()) {
					if (exp instanceof Literal) {
						Literal l = (Literal) exp;
						l.setType(FixedCharType.class);
					} else if (exp instanceof Parameter) {
						Parameter p = (Parameter) exp;
						p.setType(FixedCharType.class);
					}
				}
			}
			super.visit(obj);
		}

		public void visit(NamedTable table) {
			stripDualAlias(table);
			super.visit(table);
		}

		private void stripDualAlias(NamedTable table) {
			if (table.getCorrelationName() != null) {
				if (isDual(table)) {
					table.setCorrelationName(null);
				}
			}
		}

		@Override
		public void visit(ColumnReference obj) {
			if (obj.getTable() != null) {
				stripDualAlias(obj.getTable());
			}
			super.visit(obj);
		}

		@Override
		public void visit(WithItem obj) {
			if (obj.getColumns() != null) {
				List<ColumnReference> cols = obj.getColumns();
				if (!obj.isRecusive()) {
					//oracle 10 does not support recursion nor a column list
					obj.setColumns(null);
					Select select = obj.getSubquery().getProjectedQuery();
					List<DerivedColumn> selectClause = select.getDerivedColumns();
					for (int i = 0; i < cols.size(); i++) {
						selectClause.get(i).setAlias(cols.get(i).getName());
					}
				}
			}
			super.visit(obj);
		}

		@Override
		public void visit(SearchedCase obj) {
			boolean i18n = false;
			if (OracleExecutionFactory.this.isNonAscii(obj.getElseExpression())) {
				i18n = true;
			}
			for (SearchedWhenClause clause : obj.getCases()) {
				if (OracleExecutionFactory.this.isNonAscii(clause.getResult())) {
					i18n = true;
				}
			}
			if (i18n) {
				if (obj.getElseExpression() != null && !OracleExecutionFactory.this.isNonAscii(obj.getElseExpression())) {
					obj.setElseExpression(toNChar(obj.getElseExpression()));
				}
				for (SearchedWhenClause clause : obj.getCases()) {
					Expression result = clause.getResult();
					if (!OracleExecutionFactory.this.isNonAscii(result)) {
						if (!isNull(result)) {
							clause.setResult(toNChar(result));
						}
					}
				}
			}
			super.visit(obj);
		}

		private Function toNChar(Expression ex) {
			return new Function(TO_NCHAR, Arrays.asList(ex), TypeFacility.RUNTIME_TYPES.STRING);
		}

		@Override
		public void visit(SetQuery obj) {
			for (int i = 0; i < obj.getColumnNames().length; i++) {
				DerivedColumn leftDerivedColumn = obj.getLeftQuery().getProjectedQuery().getDerivedColumns().get(i);
				boolean left_i18n = OracleExecutionFactory.this.isNonAscii(leftDerivedColumn.getExpression());
				DerivedColumn rightDerivedColumn = obj.getRightQuery().getProjectedQuery().getDerivedColumns().get(i);
				boolean right_i18n = OracleExecutionFactory.this.isNonAscii(rightDerivedColumn.getExpression());
				if (left_i18n ^ right_i18n) {
					if (!left_i18n) {
						leftDerivedColumn.setExpression(toNChar(leftDerivedColumn.getExpression()));
					} else if (!right_i18n) {
						rightDerivedColumn.setExpression(toNChar(rightDerivedColumn.getExpression()));
					}
				}
			}
			super.visit(obj);
		}
	}

	@Override
    protected boolean supportsGeneratedKeys(ExecutionContext context,
    		Command command) {
    	if (command instanceof Insert) {
    		Insert insert = (Insert)command;
    		if (insert.getParameterValues() != null) {
    			return false; //bulk inserts result in an exception if keys are flaged for return
    		}
    	}
    	return super.supportsGeneratedKeys(context, command);
    }
    
	@Override
    public List<String> getSupportedFunctions() {
        List<String> supportedFunctions = new ArrayList<String>();
        supportedFunctions.addAll(super.getSupportedFunctions());
        supportedFunctions.add("ABS"); //$NON-NLS-1$
        supportedFunctions.add("ACOS"); //$NON-NLS-1$
        supportedFunctions.add("ASIN"); //$NON-NLS-1$
        supportedFunctions.add("ATAN"); //$NON-NLS-1$
        supportedFunctions.add("ATAN2"); //$NON-NLS-1$
        supportedFunctions.add("COS"); //$NON-NLS-1$
        supportedFunctions.add(SourceSystemFunctions.COT); 
        supportedFunctions.add("EXP"); //$NON-NLS-1$
        supportedFunctions.add("FLOOR"); //$NON-NLS-1$
        supportedFunctions.add("CEILING"); //$NON-NLS-1$
        supportedFunctions.add("LOG"); //$NON-NLS-1$
        supportedFunctions.add("LOG10"); //$NON-NLS-1$
        supportedFunctions.add("MOD"); //$NON-NLS-1$
        supportedFunctions.add("POWER"); //$NON-NLS-1$
        supportedFunctions.add("SIGN"); //$NON-NLS-1$
        supportedFunctions.add("SIN"); //$NON-NLS-1$
        supportedFunctions.add("SQRT"); //$NON-NLS-1$
        supportedFunctions.add("TAN"); //$NON-NLS-1$
        supportedFunctions.add("ASCII"); //$NON-NLS-1$
        supportedFunctions.add("CHAR"); //$NON-NLS-1$
        supportedFunctions.add("CHR"); //$NON-NLS-1$
        supportedFunctions.add("CONCAT"); //$NON-NLS-1$
		supportedFunctions.add(SourceSystemFunctions.CONCAT2);
        supportedFunctions.add("||"); //$NON-NLS-1$
        supportedFunctions.add("INITCAP"); //$NON-NLS-1$
        supportedFunctions.add("LCASE"); //$NON-NLS-1$
        supportedFunctions.add("LENGTH"); //$NON-NLS-1$
        supportedFunctions.add("LEFT"); //$NON-NLS-1$
        supportedFunctions.add("LOCATE"); //$NON-NLS-1$
        supportedFunctions.add("LOWER"); //$NON-NLS-1$
        supportedFunctions.add("LPAD"); //$NON-NLS-1$
        supportedFunctions.add("LTRIM"); //$NON-NLS-1$
        supportedFunctions.add("REPLACE"); //$NON-NLS-1$
        supportedFunctions.add("RPAD"); //$NON-NLS-1$
        supportedFunctions.add("RIGHT"); //$NON-NLS-1$
        supportedFunctions.add("RTRIM"); //$NON-NLS-1$
		supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$
		supportedFunctions.add("TRANSLATE"); //$NON-NLS-1$
        supportedFunctions.add(SourceSystemFunctions.TRIM);
        supportedFunctions.add("UCASE"); //$NON-NLS-1$
        supportedFunctions.add("UPPER"); //$NON-NLS-1$
        supportedFunctions.add("HOUR"); //$NON-NLS-1$
        supportedFunctions.add("MONTH"); //$NON-NLS-1$
        supportedFunctions.add("MONTHNAME"); //$NON-NLS-1$
        supportedFunctions.add("YEAR"); //$NON-NLS-1$
        supportedFunctions.add("DAY"); //$NON-NLS-1$
        supportedFunctions.add("DAYNAME"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFMONTH"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFWEEK"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFYEAR"); //$NON-NLS-1$
        supportedFunctions.add("QUARTER"); //$NON-NLS-1$
        supportedFunctions.add("MINUTE"); //$NON-NLS-1$
        supportedFunctions.add("SECOND"); //$NON-NLS-1$
        supportedFunctions.add("QUARTER"); //$NON-NLS-1$
        supportedFunctions.add("WEEK"); //$NON-NLS-1$
        supportedFunctions.add(SourceSystemFunctions.FORMATTIMESTAMP); 
        supportedFunctions.add(SourceSystemFunctions.PARSETIMESTAMP);
        // EXTENSION
		// ISSUE: DV-96 
		// FIXED BY: Salvatore Raunich
		// DATE: 2013.11.13
        supportedFunctions.add(SourceSystemFunctions.TIMESTAMPADD);
        supportedFunctions.add(SourceSystemFunctions.TIMESTAMPDIFF);		
		// END EXTENSION
        supportedFunctions.add("CAST"); //$NON-NLS-1$
        supportedFunctions.add("CONVERT"); //$NON-NLS-1$
        supportedFunctions.add("IFNULL"); //$NON-NLS-1$
        supportedFunctions.add("NVL");      //$NON-NLS-1$ 
        supportedFunctions.add("COALESCE"); //$NON-NLS-1$
        supportedFunctions.add(SourceSystemFunctions.ROUND);
        supportedFunctions.add(RELATE);
        supportedFunctions.add(NEAREST_NEIGHBOR);
        supportedFunctions.add(NEAREST_NEIGHBOR_DISTANCE);
        supportedFunctions.add(WITHIN_DISTANCE);
        supportedFunctions.add(FILTER);
		supportedFunctions.add(SourceSystemFunctions.ST_ASBINARY);
		supportedFunctions.add(SourceSystemFunctions.ST_GEOMFROMWKB);
		supportedFunctions.add(SourceSystemFunctions.ST_GEOMFROMTEXT);
		supportedFunctions.add(SourceSystemFunctions.ST_ASTEXT);
		supportedFunctions.add(SourceSystemFunctions.ST_ASGML);
		supportedFunctions.add(SourceSystemFunctions.ST_CONTAINS);
		supportedFunctions.add(SourceSystemFunctions.ST_CROSSES);
		supportedFunctions.add(SourceSystemFunctions.ST_DISJOINT);
		supportedFunctions.add(SourceSystemFunctions.ST_DISTANCE);
		supportedFunctions.add(SourceSystemFunctions.ST_INTERSECTS);
		supportedFunctions.add(SourceSystemFunctions.ST_OVERLAPS);
		supportedFunctions.add(SourceSystemFunctions.ST_TOUCHES);
		supportedFunctions.add(SourceSystemFunctions.ST_SRID);
		supportedFunctions.add(SourceSystemFunctions.ST_EQUALS);
        if (getVersion().compareTo(ELEVEN_2) >= 0) {
        	supportedFunctions.add("STRING_AGG");
        }
        supportedFunctions.add(SourceSystemFunctions.RAND);
		supportedFunctions.add(SourceSystemFunctions.DATE_TRUNC);
        return supportedFunctions;
    }

	@Override
	public String translateLiteralTimestamp(Timestamp timestampValue) {
		if (timestampValue.getNanos() == 0) {
			String val = formatDateValue(timestampValue);
			val = val.substring(0, val.length() - 2);
			return "to_date('" + val + "', '" + DATETIME_FORMAT + "')"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		}
		return super.translateLiteralTimestamp(timestampValue);
	}

	@Override
    public boolean supportsInlineViews() {
        return true;
    }

    @Override
    public boolean supportsFunctionsInGroupBy() {
        return true;
    }    

    @Override
    public boolean supportsRowLimit() {
        return true;
    }

    @Override
    public boolean supportsRowOffset() {
        return true;
    }
    
    @Override
    public boolean supportsExcept() {
        return true;
    }
    
    @Override
    public boolean supportsIntersect() {
        return true;
    }
    
    @Override
    public boolean supportsAggregatesEnhancedNumeric() {
    	return true;
    }
    
    @Override
    public boolean supportsElementaryOlapOperations() {
    	return true;
    }
    
    @Override
    public boolean supportsLikeRegex() {
    	return true;
    }
    
    public void setOracleSuppliedDriver(boolean oracleNative) {
		this.oracleSuppliedDriver = oracleNative;
	}
    
	@TranslatorProperty(display="Oracle Native Driver", description="True if the driver is an Oracle supplied driver",advanced=true)
    public boolean isOracleSuppliedDriver() {
		return oracleSuppliedDriver;
	}

    @Override
    protected void registerSpecificTypeOfOutParameter(
    		CallableStatement statement, Class<?> runtimeType, int index)
    		throws SQLException {
    	if (oracleSuppliedDriver && index == 1 && runtimeType == RefCursorType.class) {
    		statement.registerOutParameter(1, CURSOR_TYPE);
    	} else {
    		super.registerSpecificTypeOfOutParameter(statement, runtimeType, index);
    	}
    }
    
    @Override
    public ResultSet executeStoredProcedure(CallableStatement statement,
                                            // EXTENSION
                                            // ISSUE: DVCORE-2440
                                            // FIXED BY: Roman Pedchenko
                                            // DATE: 2014.10.18
                                            // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2176,https://issues.jboss.org/browse/TEIID-2177
                                            // 	TranslatedCommand command, Class<?> returnType) throws SQLException {
                                            // ResultSet rs = super.executeStoredProcedure(statement, command, returnType);
                                            List<Argument> preparedValues, Class<?> returnType) throws SQLException {
        ResultSet rs = super.executeStoredProcedure(statement, preparedValues, returnType);
        // END EXTENSION
        if (!oracleSuppliedDriver || returnType != RefCursorType.class) {
            return rs;
        }
        return (ResultSet) statement.getObject(1);
    }
    
    @Override
    public boolean supportsOnlyFormatLiterals() {
    	return true;
    }
    
    @Override
    public boolean supportsFormatLiteral(String literal,
    		org.teiid.translator.ExecutionFactory.Format format) {
    	if (format == Format.NUMBER) {
    		return false;
    	}
    	return OracleFormatFunctionModifier.supportsLiteral(literal);
    }
    
    @Override
    public boolean supportsArrayType() {
    	return true;
    }

	@Override
	public MetadataProcessor<Connection> getMetadataProcessor() {
		return new OracleMetadataProcessor();
	}

	// EXTENSION
  	// ISSUE: DV-99
  	// FIXED BY: Salvatore Raunich
  	// Applied solution from TEIID-2477
  	// DATE: 2013.11.08
    @Override
    public boolean supportsCommonTableExpressions() {
        return getVersion().compareTo(NINE_2) >= 0;
    }
    // END EXTENSION

	@Override
	public boolean supportsRecursiveCommonTableExpressions() {
		return getVersion().compareTo(ELEVEN_2_0_4) >= 0;
	}
    
	// EXTENSION
	// ISSUE: DV-99
	// FIXED BY: Salvatore Raunich
	// Applied solution from TEIID-2344
	// DATE: 2013.11.08
	@Override
	protected boolean usesDatabaseVersion() {
		return true;
	}

	// END EXTENSION
    // EXTENSION
    // ISSUE: DVCORE-2441
    // FIXED BY: Roman Pedchenko
    // DATE: 2014.10.23
    // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
    @Override
    public boolean supportsSelectWithoutFrom() {
    	return true;
    }

    // END EXTENSION
	// EXTENSION
	// ISSUE: DV-84
	// FIXED BY: Roman Pedchenko
	// DATE: 2013.11.23
	@Override
	public boolean supportsWindowWithoutOrderBy() {
    	return false;
	}
	// END EXTENSION
	
	@Override
	public Boolean supportsStringAgg() {
		if (supportsStringAgg == null || supportsStringAgg == true)
			return !getVersion().equals(Version.DEFAULT_VERSION) && getVersion().compareTo(ELEVEN_2) >= 0;
		return false;
	}
	
	@Override
	public Boolean supportsDistinctInStringAgg() {
 		if (supportsDistinctInStringAgg == null)
 			return false;
 		return supportsDistinctInStringAgg;
 	}

	public ArrayEmulation supportsArrayEmulation() {
		return ArrayEmulation.BINARY;
	}

	@Override
	public String translateLiteralBinaryType(BinaryType obj) {
		return "HEXTORAW('" + obj + "')"; //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Override
	public boolean supportsConvert(int fromType, int toType) {
		//allow conversion from clob to string
		if (fromType == RUNTIME_CODES.OBJECT || fromType == RUNTIME_CODES.XML || fromType == RUNTIME_CODES.BLOB || toType == RUNTIME_CODES.CLOB || toType == RUNTIME_CODES.XML || toType == RUNTIME_CODES.BLOB) {
			return false;
		}
		return super.supportsConvert(fromType, toType);
	}

	@Override
	public boolean supportsConvertFromClobToString() {
    	if (supportsConvertFromClobToString == null)
			return true;
		return super.supportsConvertFromClobToString();
    }
	
	@Override
    protected boolean supportsBooleanExpressions() {
        return false;
    }

	@Override
	public boolean supportsSelectExpressionArrayType() {
		return false;
	}

	@Override
	public boolean supportsOnlyTimestampAddLiteral() {
		return true;
	}

	private boolean dateAsTimestamp = false;

	@TranslatorProperty(display="Treat native 'date' type as 'timestamp'", description="Treat native 'date' type as 'timestamp'", advanced=true)
	public boolean dateAsTimestamp() {
		return dateAsTimestamp;
	}

	public void setDateAsTimestamp(boolean dateAsTimestamp) {
		this.dateAsTimestamp = dateAsTimestamp;
	}


	@Override
	public boolean useUnicodePrefix() {
		return true;
	}

	@Override
	protected boolean isNonAsciiFunction(Function f) {
		return f.getName().equalsIgnoreCase(TO_NCHAR)
				|| (f.getType() == TypeFacility.RUNTIME_TYPES.CHAR && f.getName().equalsIgnoreCase(SourceSystemFunctions.CONVERT));
	}

	private boolean isNull(Expression expr) {
		if (expr instanceof Literal) {
			Literal literal = (Literal)expr;
			return literal.getValue() == null;
		}
		return false;
	}

	@Override
	public Expression translateGeometrySelect(Expression expr) {
		return new Function(SourceSystemFunctions.ST_ASGML, Arrays.asList(expr), TypeFacility.RUNTIME_TYPES.CLOB);
	}

	@Override
	public Object retrieveGeometryValue(ResultSet results, int paramIndex) throws SQLException {
		final Clob clob = results.getClob(paramIndex);
		if (clob != null) {
			return new GeometryInputSource() {

				@Override
				public Reader getGml() throws SQLException {
					return clob.getCharacterStream();
				}

			};
		}
		return null;
	}

	@Override
	public boolean useColumnNamesForGeneratedKeys() {
		return true;
	}
}
