/*******************************************************************************
 * Copyright (c) 1998, 2008 Oracle. All rights reserved.
 * This program and the accompanying materials are made available under the 
 * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0 
 * which accompanies this distribution. 
 * The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
 * and the Eclipse Distribution License is available at 
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * Contributors:
 *     Oracle - initial API and implementation from Oracle TopLink
 ******************************************************************************/

package org.eclipse.persistence.platform.database.oracle.plsql;

//javase imports
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;
import java.util.Map;
import java.util.Set;
import java.util.Vector;
import static java.lang.Integer.MIN_VALUE;

// EclipseLink imports
import org.eclipse.persistence.exceptions.QueryException;
import org.eclipse.persistence.internal.databaseaccess.Accessor;
import org.eclipse.persistence.internal.helper.ComplexDatabaseType;
import org.eclipse.persistence.internal.helper.DatabaseField;
import org.eclipse.persistence.internal.helper.DatabaseType;
import org.eclipse.persistence.internal.helper.Helper;
import org.eclipse.persistence.internal.sessions.AbstractRecord;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.mappings.structures.ObjectRelationalDatabaseField;
import org.eclipse.persistence.queries.StoredProcedureCall;
import org.eclipse.persistence.sessions.DatabaseRecord;
import static org.eclipse.persistence.internal.helper.DatabaseType.DatabaseTypeHelper.databaseTypeHelper;
import static org.eclipse.persistence.internal.helper.Helper.INDENT;
import static org.eclipse.persistence.internal.helper.Helper.NL;
import static org.eclipse.persistence.platform.database.jdbc.JDBCTypes.getDatabaseTypeForCode;
import static org.eclipse.persistence.platform.database.oracle.plsql.OraclePLSQLType.PLSQLBoolean_IN_CONV;
import static org.eclipse.persistence.platform.database.oracle.plsql.OraclePLSQLType.PLSQLBoolean_OUT_CONV;
import static org.eclipse.persistence.platform.database.oracle.plsql.OraclePLSQLTypes.PLSQLBoolean;

/**
 * <b>Purpose</b>: 
 * Generates an Anonymous PL/SQL block to invoke the specified Stored Procedure
 * with arguments that may or may not have JDBC equivalents.
 * This handles conversion of PLSQL Record and Table types into SQL ARRAY (VARRAY) and STRUCT (OBJECT TYPE).
 * It also handles conversion of flat PLSQL Record types and PLSQL BOOLEAN and other basic types.
 */
@SuppressWarnings("unchecked")
public class PLSQLStoredProcedureCall extends StoredProcedureCall {

    // can't use Helper.cr(), Oracle PL/SQL parser only likes Unix-style newlines '\n'
    final static String BEGIN_DECLARE_BLOCK = NL + "DECLARE" + NL;
    final static String BEGIN_BEGIN_BLOCK = "BEGIN" + NL;
    final static String END_BEGIN_BLOCK = "END;";
    final static String PL2SQL_PREFIX = "EL_PL2SQL_";
    final static String SQL2PL_PREFIX = "EL_SQL2PL_";
    final static String BEGIN_DECLARE_FUNCTION = "FUNCTION ";
    final static String RTURN = "RETURN ";

    /**
     * List of procedure IN/OUT/INOUT arguments.
     */
    protected List<PLSQLargument> arguments = new ArrayList<PLSQLargument>();
    /**
     * Keeps track of the next procedure argument index.
     */
    protected int originalIndex = 0;
    /**
     * Translation row stored after translation on the call clone, used only for logging.
     */
    protected AbstractRecord translationRow;
    /**
     * Map of conversion function routines for converting complex PLSQL types.
     */
    protected Map<String, TypeInfo> typesInfo;
    /**
     * Id used to generate unique local functions.
     */
    protected int functionId = 0;

    public PLSQLStoredProcedureCall() {
        super();
        setIsCallableStatementRequired(true);
    }

    /**
     * PUBLIC:
     * Add a named IN argument to the stored procedure. The databaseType parameter classifies the
     * parameter (JDBCType vs. OraclePLSQLType, simple vs. complex)
     */
    public void addNamedArgument(String procedureParameterName, DatabaseType databaseType) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, IN, dt));
    }

    /**
     * PUBLIC:
     * Add a named IN argument to the stored procedure. The databaseType parameter classifies the
     * parameter (JDBCType vs. OraclePLSQLType, simple vs. complex). The extra length parameter
     * indicates that this parameter, when used in an Anonymous PL/SQL block, requires a length.
     */
    public void addNamedArgument(String procedureParameterName, DatabaseType databaseType,
        int length) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, IN, dt, length));
    }

    /**
     * PUBLIC:
     * Add a named IN argument to the stored procedure. The databaseType parameter classifies the
     * parameter (JDBCType vs. OraclePLSQLType, simple vs. complex). The extra scale and precision
     * parameters indicates that this parameter, when used in an Anonymous PL/SQL block, requires
     * scale and precision specification
     */
    public void addNamedArgument(String procedureParameterName, DatabaseType databaseType,
        int precision, int scale) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, IN, dt, precision, scale));
    }

    @Override
    public void addNamedArgument(String procedureParameterName, String argumentFieldName, int type) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, IN,
            getDatabaseTypeForCode(type))); // figure out databaseType from the sqlType
    }

    @Override
    public void addNamedArgument(String procedureParameterName, String argumentFieldName, int type,
        String typeName) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, IN,
            getDatabaseTypeForCode(type)));
    }

    /**
     * PUBLIC: Add a named IN OUT argument to the stored procedure. The databaseType parameter
     * classifies the parameter (JDBCType vs. OraclePLSQLType, simple vs. complex)
     */
    public void addNamedInOutputArgument(String procedureParameterName, DatabaseType databaseType) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, INOUT, dt));
    }

    /**
     * PUBLIC: Add a named IN OUT argument to the stored procedure. The databaseType parameter
     * classifies the parameter (JDBCType vs. OraclePLSQLType, simple vs. complex). The extra length
     * parameter indicates that this parameter, when used in an Anonymous PL/SQL block, requires a
     * length.
     */
    public void addNamedInOutputArgument(String procedureParameterName, DatabaseType databaseType,
        int length) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, INOUT, dt, length));
    }

    /**
     * PUBLIC: Add a named IN OUT argument to the stored procedure. The databaseType parameter
     * classifies the parameter (JDBCType vs. OraclePLSQLType, simple vs. complex). The extra scale
     * and precision parameters indicates that this parameter, when used in an Anonymous PL/SQL
     * block, requires scale and precision specification
     */
    public void addNamedInOutputArgument(String procedureParameterName, DatabaseType databaseType,
        int precision, int scale) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, INOUT, dt,
            precision, scale));
    }

    @Override
    public void addNamedInOutputArgument(String procedureParameterName, String inArgumentFieldName,
        String outArgumentFieldName, int type) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, INOUT,
            getDatabaseTypeForCode(type)));
    }

    @Override
    public void addNamedInOutputArgument(String procedureParameterName, String inArgumentFieldName,
        String outArgumentFieldName, int type, String typeName) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, INOUT,
            getDatabaseTypeForCode(type)));
    }

    @Override
    public void addNamedInOutputArgument(String procedureParameterName, String inArgumentFieldName,
        String outArgumentFieldName, int type, String typeName, Class classType) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, INOUT,
            getDatabaseTypeForCode(type)));
    }

    @Override
    public void addNamedInOutputArgument(String procedureParameterName, String inArgumentFieldName,
        String outArgumentFieldName, int type, String typeName, Class javaType,
        DatabaseField nestedType) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, INOUT,
            getDatabaseTypeForCode(type)));
    }

    /**
     * PUBLIC: Add a named OUT argument to the stored procedure. The databaseType parameter
     * classifies the parameter (JDBCType vs. OraclePLSQLType, simple vs. complex)
     */
    public void addNamedOutputArgument(String procedureParameterName, DatabaseType databaseType) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, OUT, dt));
    }

    /**
     * PUBLIC: Add a named OUT argument to the stored procedure. The databaseType parameter
     * classifies the parameter (JDBCType vs. OraclePLSQLType, simple vs. complex). The extra length
     * parameter indicates that this parameter, when used in an Anonymous PL/SQL block, requires a
     * length.
     */
    public void addNamedOutputArgument(String procedureParameterName, DatabaseType databaseType,
        int length) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, OUT, dt, length));
    }

    /**
     * PUBLIC: Add a named OUT argument to the stored procedure. The databaseType parameter
     * classifies the parameter (JDBCType vs. OraclePLSQLType, simple vs. complex). The extra scale
     * and precision parameters indicates that this parameter, when used in an Anonymous PL/SQL
     * block, requires scale and precision specification
     */
    public void addNamedOutputArgument(String procedureParameterName, DatabaseType databaseType,
        int precision, int scale) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, OUT, dt,
            precision, scale));
    }

    @Override
    public void addNamedOutputArgument(String procedureParameterName, String argumentFieldName,
        int jdbcType, String typeName, Class javaType) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, OUT,
            getDatabaseTypeForCode(jdbcType)));
    }

    @Override
    public void addNamedOutputArgument(String procedureParameterName, String argumentFieldName,
        int jdbcType, String typeName, Class javaType, DatabaseField nestedType) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, OUT,
            getDatabaseTypeForCode(jdbcType)));
    }

    @Override
    public void addNamedOutputArgument(String procedureParameterName, String argumentFieldName,
        int type, String typeName) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, OUT,
            getDatabaseTypeForCode(type)));
    }

    @Override
    public void addNamedOutputArgument(String procedureParameterName, String argumentFieldName,
        int type) {
        arguments.add(new PLSQLargument(procedureParameterName, originalIndex++, OUT,
            getDatabaseTypeForCode(type)));
    }

    // un-supported addXXX operations

    @Override
    public void addNamedArgument(String procedureParameterAndArgumentFieldName) {
        throw QueryException.addArgumentsNotSupported("named arguments without DatabaseType classification");
    }

    @Override
    public void addNamedArgumentValue(String procedureParameterName, Object argumentValue) {
        throw QueryException.addArgumentsNotSupported("named argument values without DatabaseType classification");
    }

    @Override
    public void addNamedArgument(String procedureParameterName, String argumentFieldName) {
        throw QueryException.addArgumentsNotSupported("named argument values without DatabaseType classification");
    }

    @Override
    public void addNamedInOutputArgument(String procedureParameterAndArgumentFieldName) {
        throw QueryException.addArgumentsNotSupported("named IN OUT argument without DatabaseType classification");
    }

    @Override
    public void addNamedInOutputArgument(String procedureParameterName, String argumentFieldName) {
        throw QueryException.addArgumentsNotSupported("named IN OUT arguments without DatabaseType classification");
    }

    @Override
    public void addNamedInOutputArgument(String procedureParameterName, String argumentFieldName,
        Class type) {
        throw QueryException.addArgumentsNotSupported("named IN OUT arguments without DatabaseType classification");
    }

    @Override
    public void addNamedInOutputArgument(String procedureParameterName, String inArgumentFieldName,
        String outArgumentFieldName, Class type) {
        throw QueryException.addArgumentsNotSupported("named IN OUT arguments without DatabaseType classification");
    }

    @Override
    public void addNamedInOutputArgumentValue(String procedureParameterName,
        Object inArgumentValue, String outArgumentFieldName, Class type) {
        throw QueryException.addArgumentsNotSupported("named IN OUT argument values without DatabaseType classification");
    }

    @Override
    public void addNamedOutputArgument(String procedureParameterAndArgumentFieldName) {
        throw QueryException.addArgumentsNotSupported("named OUT arguments without DatabaseType classification");
    }

    @Override
    public void addNamedOutputArgument(String procedureParameterName, String argumentFieldName) {
        throw QueryException.addArgumentsNotSupported("named OUT arguments without DatabaseType classification");
    }

    @Override
    public void addNamedOutputArgument(String procedureParameterName, String argumentFieldName,
        Class type) {
        throw QueryException.addArgumentsNotSupported("named OUT arguments without DatabaseType classification");
    }

    @Override
    public void useNamedCursorOutputAsResultSet(String argumentName) {
        throw QueryException.addArgumentsNotSupported("named OUT cursor arguments without DatabaseType classification");
    }

    // unlikely we will EVER support unnamed parameters
    @Override
    public void addUnamedArgument(String argumentFieldName, Class type) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedArgument(String argumentFieldName, int type, String typeName,
        DatabaseField nestedType) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedArgument(String argumentFieldName, int type, String typeName) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedArgument(String argumentFieldName, int type) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedArgument(String argumentFieldName) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedArgumentValue(Object argumentValue) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedInOutputArgument(String argumentFieldName, Class type) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedInOutputArgument(String inArgumentFieldName, String outArgumentFieldName,
        Class type) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedInOutputArgument(String inArgumentFieldName, String outArgumentFieldName,
        int type, String typeName, Class collection, DatabaseField nestedType) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedInOutputArgument(String inArgumentFieldName, String outArgumentFieldName,
        int type, String typeName, Class collection) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedInOutputArgument(String inArgumentFieldName, String outArgumentFieldName,
        int type, String typeName) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedInOutputArgument(String inArgumentFieldName, String outArgumentFieldName,
        int type) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedInOutputArgument(String argumentFieldName) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedInOutputArgumentValue(Object inArgumentValue, String outArgumentFieldName,
        Class type) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedOutputArgument(String argumentFieldName, Class type) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedOutputArgument(String argumentFieldName, int jdbcType, String typeName,
        Class javaType, DatabaseField nestedType) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedOutputArgument(String argumentFieldName, int jdbcType, String typeName,
        Class javaType) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedOutputArgument(String argumentFieldName, int type, String typeName) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedOutputArgument(String argumentFieldName, int type) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void addUnamedOutputArgument(String argumentFieldName) {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    @Override
    public void useUnnamedCursorOutputAsResultSet() {
        throw QueryException.unnamedArgumentsNotSupported();
    }

    /**
     * PUBLIC: Add a named OUT cursor argument to the stored procedure. The databaseType parameter
     * classifies the parameter (JDBCType vs. OraclePLSQLType, simple vs. complex).
     */
    public void useNamedCursorOutputAsResultSet(String argumentName, DatabaseType databaseType) {
        DatabaseType dt = databaseType.isComplexDatabaseType() ? 
            ((ComplexDatabaseType)databaseType).clone() : databaseType;
        PLSQLargument newArg = new PLSQLargument(argumentName, originalIndex++, OUT, dt);
        newArg.cursorOutput = true;
        arguments.add(newArg);
    }

    /**
     * INTERNAL compute the re-ordered indices - Do the IN args first, then the 'IN-half' of the
     * INOUT args next, the OUT args, then the 'OUT-half' of the INOUT args
     */
    protected void assignIndices() {
        List<PLSQLargument> inArguments = getArguments(arguments, IN);
        List<PLSQLargument> inOutArguments = getArguments(arguments, INOUT);
        inArguments.addAll(inOutArguments);
        int newIndex = 1;
        List<PLSQLargument> expandedArguments = new ArrayList<PLSQLargument>();
        // Must move any expanded types to the end, as they are assigned after in the BEGIN clause.
        for (ListIterator<PLSQLargument> inArgsIter = inArguments.listIterator(); inArgsIter.hasNext();) {
            PLSQLargument inArg = inArgsIter.next();
        	if (inArg.databaseType.isComplexDatabaseType() && (!((ComplexDatabaseType)inArg.databaseType).hasCompatibleType())) {
                expandedArguments.add(inArg);
                inArgsIter.remove();
            }
        }
        inArguments.addAll(expandedArguments);
        for (ListIterator<PLSQLargument> inArgsIter = inArguments.listIterator(); inArgsIter.hasNext();) {
            PLSQLargument inArg = inArgsIter.next();
            // delegate to arg's DatabaseType - ComplexTypes may expand arguments
            // use ListIterator so that computeInIndex can add expanded args
            newIndex = inArg.databaseType.computeInIndex(inArg, newIndex,
                inArgsIter);
        }
        for (PLSQLargument inArg : inArguments) {
            DatabaseType type = inArg.databaseType;
            if (!type.isComplexDatabaseType()) {
                super.addNamedArgument(inArg.name, inArg.name, type.getConversionCode());
            }
            else {
                ComplexDatabaseType complexType = (ComplexDatabaseType)type;
                if (inArg.inIndex != MIN_VALUE) {
                    if (type instanceof PLSQLCollection) {
                        DatabaseType nestedType = ((PLSQLCollection)type).getNestedType();
                        if (nestedType != null) {
                            ObjectRelationalDatabaseField field = 
                                new ObjectRelationalDatabaseField(inArg.name);
                            field.setSqlType(nestedType.getConversionCode());
                            if (nestedType.isComplexDatabaseType()) {
                                field.setSqlTypeName(((ComplexDatabaseType)nestedType).getCompatibleType());
                            }
                            super.addNamedArgument(inArg.name, inArg.name, type.getConversionCode(),
                                complexType.getCompatibleType(), field);
                        }
                        else {
                            super.addNamedArgument(inArg.name, inArg.name, type.getConversionCode(),
                                complexType.getCompatibleType());
                        }
                    }
                    else {
                        super.addNamedArgument(inArg.name, inArg.name, type.getConversionCode(),
                            complexType.getCompatibleType());
                    }
                }
            }
        }
        List<PLSQLargument> outArguments = getArguments(arguments, OUT);
        outArguments.addAll(inOutArguments);
        for (ListIterator<PLSQLargument> outArgsIter = outArguments.listIterator(); outArgsIter.hasNext();) {
            PLSQLargument outArg = outArgsIter.next();
            newIndex = outArg.databaseType.computeOutIndex(outArg, newIndex,
                outArgsIter);
        }
        for (PLSQLargument outArg : outArguments) {
        	DatabaseType type = outArg.databaseType;
            if (!type.isComplexDatabaseType()) {
                super.addNamedOutputArgument(outArg.name, outArg.name, type.getConversionCode());
            } else {
            	ComplexDatabaseType complexType = (ComplexDatabaseType)type;
                if (outArg.outIndex != MIN_VALUE) {
                	if (complexType instanceof PLSQLCollection) {
                		DatabaseType nestedType = ((PLSQLCollection)complexType).getNestedType();
                		if (nestedType != null) {
                			ObjectRelationalDatabaseField nestedField = new ObjectRelationalDatabaseField(outArg.name);
                			nestedField.setSqlType(nestedType.getConversionCode());
                			if (nestedType.isComplexDatabaseType()) {
                				ComplexDatabaseType complexNestedType = (ComplexDatabaseType)nestedType;
                    			nestedField.setType(complexNestedType.getJavaType());
                    			nestedField.setSqlTypeName(complexNestedType.getCompatibleType());
                			}
    	                    super.addNamedOutputArgument(outArg.name, outArg.name, type.getConversionCode(), 
    	                    		complexType.getCompatibleType(), complexType.getJavaType(), nestedField);                			
                		} else {
    	                    super.addNamedOutputArgument(outArg.name, outArg.name, type.getConversionCode(), complexType.getCompatibleType());
                		}
                	} else if (complexType.hasCompatibleType()) {
                		super.addNamedOutputArgument(outArg.name, outArg.name, type.getConversionCode(), complexType.getCompatibleType(), complexType.getJavaType());
                	} else {
                		// If there is no STRUCT type set, then the output is expanded, so one output for each field.
                		super.addNamedOutputArgument(outArg.name, outArg.name, type.getConversionCode());
                	}
                }
            }
        }
    }
    
    /**
     * INTERNAL
     * Generate portion of the Anonymous PL/SQL block that declares the temporary variables
     * in the DECLARE section
     * 
     * @param sb
     */
    protected void buildDeclareBlock(StringBuilder sb) {
        List<PLSQLargument> inArguments = getArguments(arguments, IN);
        List<PLSQLargument> inOutArguments = getArguments(arguments, INOUT);
        inArguments.addAll(inOutArguments);
        List<PLSQLargument> outArguments = getArguments(arguments, OUT);
        for (PLSQLargument arg : inArguments) {
            arg.databaseType.buildInDeclare(sb, arg);
        }
        for (PLSQLargument arg : outArguments) {
            arg.databaseType.buildOutDeclare(sb, arg);
        }
    }
    
    /**
     * INTERNAL
     * Add the nested function string required for the type and its subtypes. The functions
     * must be added in inverse order to resolve dependencies.
     */
    protected void addNestedFunctionsForArgument(List functions, PLSQLargument argument,
                DatabaseType databaseType, Set<DatabaseType> processed) {
        if ((databaseType == null) || !databaseType.isComplexDatabaseType()
            || databaseType.isJDBCType() || processed.contains(databaseType)) {
            return;
        }
        ComplexDatabaseType type = (ComplexDatabaseType)databaseType;
        if (!type.hasCompatibleType()) {
            return;
        }
        processed.add(type);
        if (type instanceof PLSQLCollection) {
            DatabaseType nestedType = ((PLSQLCollection)type).getNestedType();
            addNestedFunctionsForArgument(functions, argument, nestedType, processed);
        } else if (type instanceof PLSQLrecord) {
            for (PLSQLargument field : ((PLSQLrecord)type).getFields()) {
	            DatabaseType nestedType = field.databaseType;
                addNestedFunctionsForArgument(functions, argument, nestedType, processed);
            }
        }
        TypeInfo info = this.typesInfo.get(type.getTypeName());
        // If the info was not found in publisher, then generate it.
        if (info == null) {
            info = generateNestedFunction((ComplexDatabaseType)type);
        }
        if (argument.direction == IN) {
            if (!functions.contains(info.sql2PlConv)) {
                functions.add(info.sql2PlConv);
            }
        }
        else if (argument.direction == INOUT) {
            if (!functions.contains(info.sql2PlConv)) {
                functions.add(info.sql2PlConv);
            }
            if (!functions.contains(info.pl2SqlConv)) {
                functions.add(info.pl2SqlConv);
            }
        }
        else if (argument.direction == OUT) {
            if (!functions.contains(info.pl2SqlConv)) {
                functions.add(info.pl2SqlConv);
            }
        }
    }

    /**
     * INTERNAL: Generate the nested function to convert the PLSQL type to its compatible SQL type.
     */
    protected TypeInfo generateNestedFunction(ComplexDatabaseType type) {
        TypeInfo info = new TypeInfo();
        info.pl2SqlName = PL2SQL_PREFIX + (this.functionId++);
        info.sql2PlName = SQL2PL_PREFIX + (this.functionId++);
        if (type.isRecord()) {
            PLSQLrecord record = (PLSQLrecord)type;
            StringBuilder sb = new StringBuilder();
            sb.append(INDENT);
            sb.append(BEGIN_DECLARE_FUNCTION);
            sb.append(info.pl2SqlName);
            sb.append("(aPlsqlItem ");
            sb.append(record.getTypeName());
            sb.append(")");
            sb.append(NL);sb.append(INDENT);
            sb.append(RTURN);
            sb.append(record.getCompatibleType());
            sb.append(" IS");
            sb.append(NL);sb.append(INDENT);sb.append(INDENT);
            sb.append("aSqlItem ");
            sb.append(record.getCompatibleType());
            sb.append(";");
            sb.append(NL);sb.append(INDENT);
            sb.append(BEGIN_BEGIN_BLOCK);
            sb.append(INDENT);sb.append(INDENT);
            sb.append("aSqlItem := ");
            sb.append(record.getCompatibleType());
            sb.append("(");
            int size = record.getFields().size();
            for (int index = 0; index < size; index++) {
                sb.append("NULL");
                if ((index + 1) != size) {
                    sb.append(", ");
                }
            }
            sb.append(");"); 
            sb.append(NL);
            for (PLSQLargument argument : record.getFields()) {
                sb.append(INDENT);sb.append(INDENT);
                sb.append("aSqlItem.");
                sb.append(argument.name);
                if (argument.databaseType.isComplexDatabaseType()) {
                    sb.append(" := ");
                    sb.append(getPl2SQLName((ComplexDatabaseType)argument.databaseType));
                    sb.append("(aPlsqlItem.");
                    sb.append(argument.name);
                    sb.append(");");
                }
                else if (argument.databaseType.equals(PLSQLBoolean)) {
                    sb.append(" := ");
                    sb.append(PLSQLBoolean_OUT_CONV);
                    sb.append("(aPlsqlItem.");
                    sb.append(argument.name);
                    sb.append(");");
                }
                else {
                    sb.append(" := aPlsqlItem.");
                    sb.append(argument.name);
                    sb.append(";");
                }
                sb.append(NL);
            }
            sb.append(INDENT);sb.append(INDENT);
            sb.append(RTURN);
            sb.append("aSqlItem;");
            sb.append(NL);
            sb.append(INDENT);
            sb.append("END ");
            sb.append(info.pl2SqlName);
            sb.append(";");
            sb.append(NL);

            info.pl2SqlConv = sb.toString();

            sb = new StringBuilder();
            sb.append(INDENT);
            sb.append(BEGIN_DECLARE_FUNCTION);
            sb.append(info.sql2PlName);
            sb.append("(aSqlItem ");
            sb.append(record.getCompatibleType());
            sb.append(") ");
            sb.append(NL);sb.append(INDENT);
            sb.append(RTURN);
            sb.append(record.getTypeName());
            sb.append(" IS"); 
            sb.append(NL);sb.append(INDENT);sb.append(INDENT);
            sb.append("aPlsqlItem ");
            sb.append(record.getTypeName());
            sb.append(";");
            sb.append(NL);sb.append(INDENT);
            sb.append(BEGIN_BEGIN_BLOCK);
            for (PLSQLargument argument : record.getFields()) {
                sb.append(INDENT);sb.append(INDENT);
                sb.append("aPlsqlItem.");
                sb.append(argument.name);
                if (argument.databaseType.isComplexDatabaseType()) {
                    sb.append(" := ");
                    sb.append(getSQL2PlName((ComplexDatabaseType)argument.databaseType));
                    sb.append("(aSqlItem.");
                    sb.append(argument.name);
                    sb.append(");");
                }
                else if (argument.databaseType.equals(PLSQLBoolean)) {
                    sb.append(" := ");
                    sb.append(PLSQLBoolean_IN_CONV);
                    sb.append("(aSqlItem.");
                    sb.append(argument.name);
                    sb.append(");");
                }
                else {
                    sb.append(" := aSqlItem.");
                    sb.append(argument.name);
                    sb.append(";");
                }
                sb.append(NL);
            }
            sb.append(INDENT);sb.append(INDENT);
            sb.append(RTURN);
            sb.append("aPlsqlItem;");
            sb.append(NL);
            sb.append(INDENT);
            sb.append("END ");
            sb.append(info.sql2PlName);
            sb.append(";");
            sb.append(NL);

            info.sql2PlConv = sb.toString();
        }
        else if (type.isCollection()) {
            PLSQLCollection collection = (PLSQLCollection)type;
            StringBuilder sb = new StringBuilder();
            sb.append(INDENT);
            sb.append(BEGIN_DECLARE_FUNCTION);
            sb.append(info.pl2SqlName);
            sb.append("(aPlsqlItem ");
            sb.append(collection.getTypeName());
            sb.append(")");
            sb.append(NL);sb.append(INDENT);
            sb.append(RTURN);
            sb.append(collection.getCompatibleType());
            sb.append(" IS");
            sb.append(NL);sb.append(INDENT);sb.append(INDENT);
            sb.append("aSqlItem ");
            sb.append(collection.getCompatibleType());
            sb.append(";");
            sb.append(NL);sb.append(INDENT);
            sb.append(BEGIN_BEGIN_BLOCK);
            sb.append(INDENT);sb.append(INDENT);
            sb.append("aSqlItem := ");
            sb.append(collection.getCompatibleType());
            sb.append("();");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);
            sb.append("aSqlItem.EXTEND(aPlsqlItem.COUNT);");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);
            sb.append("IF aPlsqlItem.COUNT > 0 THEN");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);
            sb.append("FOR I IN aPlsqlItem.FIRST..aPlsqlItem.LAST LOOP"); 
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);sb.append(INDENT);
            sb.append("aSqlItem(I + 1 - aPlsqlItem.FIRST) := ");
            if ((collection.nestedType != null) && collection.nestedType.isComplexDatabaseType()) {
                sb.append(getPl2SQLName((ComplexDatabaseType)collection.nestedType));
                sb.append("(aPlsqlItem(I));");
            }
            else if (collection.nestedType.equals(PLSQLBoolean)) {
                sb.append(PLSQLBoolean_OUT_CONV);
                sb.append("(aPlsqlItem(I));");
            }
            else {
                sb.append("aPlsqlItem(I);");
            }
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);sb.append(INDENT);
            sb.append("END LOOP;");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);
            sb.append("END IF;");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);
            sb.append(RTURN);
            sb.append("aSqlItem;");
            sb.append(NL);sb.append(INDENT);
            sb.append("END ");
            sb.append(info.pl2SqlName);
            sb.append(";");
            sb.append(NL);

            info.pl2SqlConv = sb.toString();

            sb = new StringBuilder();
            sb.append(INDENT);
            sb.append(BEGIN_DECLARE_FUNCTION);
            sb.append(info.sql2PlName);
            sb.append("(aSqlItem ");
            sb.append(collection.getCompatibleType());
            sb.append(")");
            sb.append(NL);
            sb.append(INDENT);
            sb.append(RTURN);
            sb.append(collection.getTypeName());
            sb.append(" IS");
            sb.append(NL);sb.append(INDENT);sb.append(INDENT);
            sb.append("aPlsqlItem ");
            sb.append(collection.getTypeName());
            sb.append(";");
            sb.append(NL);sb.append(INDENT);
            sb.append(BEGIN_BEGIN_BLOCK);
            sb.append(INDENT);sb.append(INDENT);
            sb.append("IF aSqlItem.COUNT > 0 THEN");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);sb.append(INDENT);
            sb.append("FOR I IN 1..aSqlItem.COUNT LOOP");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);sb.append(INDENT);sb.append(INDENT);
            if ((collection.nestedType != null) && collection.nestedType.isComplexDatabaseType()) {
                sb.append("aPlsqlItem(I) := ");
                sb.append(getSQL2PlName((ComplexDatabaseType)collection.nestedType));
                sb.append("(aSqlItem(I));");
            }
            else if (collection.nestedType.equals(PLSQLBoolean)) {
                sb.append("aSqlItem(I + 1 - aPlsqlItem.FIRST) := ");
                sb.append(PLSQLBoolean_IN_CONV);
                sb.append("(aPlsqlItem(I));");
            }
            else {
                sb.append("aPlsqlItem(I) := aSqlItem(I);");
            }
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);sb.append(INDENT);
            sb.append("END LOOP;");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);
            sb.append("END IF;");
            sb.append(NL);
            sb.append(INDENT);sb.append(INDENT);
            sb.append(RTURN);
            sb.append("aPlsqlItem;");
            sb.append(NL);
            sb.append(INDENT);
            sb.append("END ");
            sb.append(info.sql2PlName);
            sb.append(";");
            sb.append(NL);

            info.sql2PlConv = sb.toString();
        }
        this.typesInfo.put(type.getTypeName(), info);
        return info;
    }

    /**
     * INTERNAL
     * Generate portion of the Anonymous PL/SQL block with PL/SQL conversion routines as
     * nested functions
     * 
     * @param sb
     */
    protected void buildNestedFunctions(StringBuilder stream) {
        List<String> nestedFunctions = new ArrayList<String>();
        Set<DatabaseType> processed = new HashSet<DatabaseType>();
        for (PLSQLargument arg : this.arguments) {
            DatabaseType type = arg.databaseType;
            addNestedFunctionsForArgument(nestedFunctions, arg, type, processed);
        }
        if (!nestedFunctions.isEmpty()) {
            for (String function : nestedFunctions) {
                stream.append(function);
            }
        }
    }

    /**
     * INTERNAL Generate portion of the Anonymous PL/SQL block that assigns fields at the beginning
     * of the BEGIN block (before invoking the target procedure)
     * 
     * @param sb
     */
    protected void buildBeginBlock(StringBuilder sb) {

        List<PLSQLargument> inArguments = getArguments(arguments, IN);
        inArguments.addAll(getArguments(arguments, INOUT));
        for (PLSQLargument arg : inArguments) {
            arg.databaseType.buildBeginBlock(sb, arg, this);
        }
    }

    /**
     * INTERNAL Generate portion of the Anonymous PL/SQL block that invokes the target procedure
     * 
     * @param sb
     */
    protected void buildProcedureInvocation(StringBuilder sb) {

        sb.append("  ");
        sb.append(getProcedureName());
        sb.append("(");
        int size = arguments.size(), idx = 1;
        for (PLSQLargument arg : arguments) {
            sb.append(arg.name);
            sb.append("=>");
            sb.append(databaseTypeHelper.buildTarget(arg));
            if (idx < size) {
                sb.append(", ");
                idx++;
            }
        }
        sb.append(");");
        sb.append(NL);
    }

    /**
     * INTERNAL Generate portion of the Anonymous PL/SQL block after the target procedures has been
     * invoked and OUT parameters must be handled
     * 
     * @param sb
     */
    protected void buildOutAssignments(StringBuilder sb) {
        List<PLSQLargument> outArguments = getArguments(arguments, OUT);
        outArguments.addAll(getArguments(arguments, INOUT));
        for (PLSQLargument arg : outArguments) {
            arg.databaseType.buildOutAssignment(sb, arg, this);
        }
    }

    /**
     * Generate the Anonymous PL/SQL block
     */
    @Override
    protected void prepareInternal(AbstractSession session) {
        // build any and all required type conversion routines for
        // complex PL/SQL types in packages
        this.typesInfo = new HashMap<String, TypeInfo>();
        // create a copy of the arguments re-ordered with different indices
        assignIndices();
        // build the Anonymous PL/SQL block in sections
        StringBuilder sb = new StringBuilder();
        sb.append(BEGIN_DECLARE_BLOCK);
        buildDeclareBlock(sb);
        buildNestedFunctions(sb);
        sb.append(BEGIN_BEGIN_BLOCK);
        buildBeginBlock(sb);
        buildProcedureInvocation(sb);
        buildOutAssignments(sb);
        sb.append(END_BEGIN_BLOCK);
        setSQLStringInternal(sb.toString());
        super.prepareInternalParameters(session);
    }

    /**                                                            
     * Translate the PLSQL procedure translation row, into the row 
     * expected by the SQL procedure.                              
     * This handles expanding and re-ordering parameters.          
     */                                                            
    @Override
    public void translate(AbstractRecord translationRow, AbstractRecord modifyRow,
        AbstractSession session) {
        // re-order elements in translationRow to conform to re-ordered indices
        AbstractRecord copyOfTranslationRow = (AbstractRecord)translationRow.clone();
        int len = copyOfTranslationRow.size();
        Vector copyOfTranslationFields = copyOfTranslationRow.getFields();
        translationRow.clear();
        Vector translationRowFields = translationRow.getFields();
        translationRowFields.setSize(len);
        Vector translationRowValues = translationRow.getValues();
        translationRowValues.setSize(len);
        for (PLSQLargument arg : arguments) {
            if (arg.direction == IN || arg.direction == INOUT) {
                arg.databaseType.translate(arg, translationRow,
                    copyOfTranslationRow, copyOfTranslationFields, translationRowFields,
                    translationRowValues, this);
            }
        }
        this.translationRow = translationRow; // save a copy for logging
        super.translate(translationRow, modifyRow, session);
    }

    /**                                                    
     * Translate the SQL procedure output row, into the row
     * expected by the PLSQL procedure.                    
     * This handles re-ordering parameters.                
     */                                                    
    @Override
    public AbstractRecord buildOutputRow(CallableStatement statement) throws SQLException {

        AbstractRecord outputRow = super.buildOutputRow(statement);
        if (!shouldBuildOutputRow) {
            outputRow.put("", 1); // fake-out Oracle executeUpdate rowCount, always 1
            return outputRow;
        }
        // re-order elements in outputRow to conform to original indices
        Vector outputRowFields = outputRow.getFields();
        Vector outputRowValues = outputRow.getValues();
        DatabaseRecord newOutputRow = new DatabaseRecord();
        List<PLSQLargument> outArguments = getArguments(arguments, OUT);
        outArguments.addAll(getArguments(arguments, INOUT));
        Collections.sort(outArguments, new Comparator<PLSQLargument>() {
            public int compare(PLSQLargument o1, PLSQLargument o2) {
                return o1.originalIndex - o2.originalIndex;
            }
        });
        for (PLSQLargument outArg : outArguments) {
            outArg.databaseType.buildOutputRow(outArg, outputRow,
                newOutputRow, outputRowFields, outputRowValues);
        }
        return newOutputRow;
    }

    /**                                  
     * INTERNAL:                         
     * Build the log string for the call.
     */                                  
    @Override
    public String getLogString(Accessor accessor) {

        StringBuilder sb = new StringBuilder(getSQLString());
        sb.append(Helper.cr());
        sb.append(INDENT);
        sb.append("bind => [");
        List<PLSQLargument> inArguments = getArguments(arguments, IN);
        inArguments.addAll(getArguments(arguments, INOUT));
        Collections.sort(inArguments, new Comparator<PLSQLargument>() {
            public int compare(PLSQLargument o1, PLSQLargument o2) {
                return o1.inIndex - o2.inIndex;
            }
        });
        for (Iterator<PLSQLargument> i = inArguments.iterator(); i.hasNext();) {
            PLSQLargument inArg = i.next();
            inArg.databaseType.logParameter(sb, IN, inArg, translationRow, 
                getQuery().getSession().getPlatform());
            if (i.hasNext()) {
                sb.append(", ");
            }
        }
        List<PLSQLargument> outArguments = getArguments(arguments, OUT);
        outArguments.addAll(getArguments(arguments, INOUT));
        Collections.sort(outArguments, new Comparator<PLSQLargument>() {
            public int compare(PLSQLargument o1, PLSQLargument o2) {
                return o1.outIndex - o2.outIndex;
            }
        });
        if (!inArguments.isEmpty() && !outArguments.isEmpty()) {
            sb.append(", ");
        }
        for (Iterator<PLSQLargument> i = outArguments.iterator(); i.hasNext();) {
            PLSQLargument outArg = i.next();
            outArg.databaseType.logParameter(sb, OUT, outArg, translationRow,
                getQuery().getSession().getPlatform());
            if (i.hasNext()) {
                sb.append(", ");
            }
        }
        sb.append("]");
        return sb.toString();
    }

    /**
     * INTERNAL
     * 
     * @param args
     * @param direction
     * @return list of arguments with the specified direction
     */
    protected static List<PLSQLargument> getArguments(List<PLSQLargument> args, Integer direction) {
        List<PLSQLargument> inArgs = new ArrayList<PLSQLargument>();
        for (PLSQLargument arg : args) {
            if (arg.direction == direction) {
                inArgs.add(arg);
            }
        }
        return inArgs;
    }

    /**
     * INTERNAL:
     * Helper structure used to store the PLSQL type conversion routines.
     */
    class TypeInfo {
        String sql2PlName;
        String sql2PlConv;
        String pl2SqlName;
        String pl2SqlConv;
        @Override
        public String toString() {
            StringBuilder sb = new StringBuilder(NL);
            sb.append(sql2PlName == null ? "" : sql2PlConv);
            sb.append(pl2SqlName == null ? "" : pl2SqlConv);
            return sb.toString();
        }
    }

    /**
     * Return the conversion function name, generate the function if missing.
     */
    public String getSQL2PlName(ComplexDatabaseType type) {
        if (typesInfo == null) {
            return null;
        }
        TypeInfo info = typesInfo.get(type.getTypeName());
        if (info == null) {
            info = generateNestedFunction(type);
        }
        return info.sql2PlName;
    }

    /**
     * Return the conversion function name, generate the function if missing.
     */
    public String getPl2SQLName(ComplexDatabaseType type) {
        if (typesInfo == null) {
            return null;
        }
        TypeInfo info = typesInfo.get(type.getTypeName());
        if (info == null) {
            info = generateNestedFunction(type);
        }
        return info.pl2SqlName;
    }
}
