Java Examples for oracle.sql.ARRAY

/**
    * Binds PHP array to Oracle PL/SQL array by name.
    *
    * oci_bind_array_by_name() binds the PHP array
    * varArray to the Oracle placeholder name, which
    * points to Oracle PL/SQL array. Whether it will
    * be used for input or output will be determined
    * at run-time. The maxTableLength parameter sets
    * the maximum length both for incoming and result
    * arrays. Parameter maxItemLength sets maximum
    * length for array items. If maxItemLength was
    * not specified or equals to -1,
    * oci_bind_array_by_name() will find the longest
    * element in the incoming array and will use it as
    * maximum length for array items. type parameter
    * should be used to set the type of PL/SQL array
    * items. See list of available types below.
    *
    * @param env the PHP executing environment
    * @param stmt the Oracle statement
    * @param name the Oracle placeholder
    * @param varArray the array to be binded
    * @param maxTableLength maximum table length
    * @param maxItemLength maximum item length
    * @param type one of the following types:
    * <br/>
    * SQLT_NUM - for arrays of NUMBER.
    * <br/>
    * SQLT_INT - for arrays of INTEGER
    * (Note: INTEGER it is actually a synonym for
    *  NUMBER(38), but SQLT_NUM type won't work in
    *  this case even though they are synonyms).
    * <br/>
    * SQLT_FLT - for arrays of FLOAT.
    * <br/>
    * SQLT_AFC - for arrays of CHAR.
    * <br/>
    * SQLT_CHR - for arrays of VARCHAR2.
    * <br/>
    * SQLT_VCS - for arrays of VARCHAR.
    * <br/>
    * SQLT_AVC - for arrays of CHARZ.
    * <br/>
    * SQLT_STR - for arrays of STRING.
    * <br/>
    * SQLT_LVC - for arrays of LONG VARCHAR.
    * <br/>
    * SQLT_ODT - for arrays of DATE.
    *
    * @return true on success of false on failure
    */
public static boolean oci_bind_array_by_name(Env env, @NotNull OracleStatement stmt, @NotNull String name, @NotNull ArrayValue varArray, @NotNull int maxTableLength, @Optional("0") int maxItemLength, @Optional("0") int type) {
    try {
        // JDBC underlying connection
        Connection conn = stmt.getJavaConnection();
        // Oracle underlying statement
        PreparedStatement oracleStmt = stmt.getPreparedStatement();
        // Create an oracle.sql.ARRAY object to hold the values
        // oracle.sql.ArrayDescriptor arrayDesc =
        //   oracle.sql.ArrayDescriptor.createDescriptor("number_varray", conn);
        Class clArrayDescriptor = Class.forName("oracle.sql.ArrayDescriptor");
        Method method = clArrayDescriptor.getDeclaredMethod("createDescriptor", new Class[] { String.class, Connection.class });
        Object arrayDesc = method.invoke(clArrayDescriptor, new Object[] { "NUMBER_VARRAY", conn });
        // int arrayValues[] = {123, 234};
        Value[] valueArray = varArray.valuesToArray();
        // {"aaa", "bbb", "ccc"};
        Object[] objectArray = new Object[5];
        for (int i = 0; i < valueArray.length; i++) {
            Object obj = valueArray[i].toJavaObject();
            objectArray[i] = obj;
        }
        //       oracle.sql.ARRAY array
        //         = new oracle.sql.ARRAY(arrayDesc, conn, arrayValues);
        Class clARRAY = Class.forName("oracle.sql.ARRAY");
        Constructor constructor = clARRAY.getDeclaredConstructor(new Class[] { clArrayDescriptor, Connection.class, Object.class });
        Array oracleArray = (Array) constructor.newInstance(new Object[] { arrayDesc, conn, objectArray });
        if (name == null) {
            return false;
        }
        if (!name.startsWith(":")) {
            name = ":" + name;
        }
        if (name.length() < 2) {
            return false;
        }
        // method.invoke(oracleStmt, new Object[] {name, oracleArray});
        Integer index = stmt.getBindingVariable(name);
        if (index == null) {
            return false;
        }
        int i = index.intValue();
        Object object = varArray.toJavaObject();
        if (object instanceof OracleOciCollection) {
            oracleArray = ((OracleOciCollection) object).getCollection();
            oracleStmt.setArray(i, oracleArray);
        } else if (varArray instanceof ArrayValueImpl) {
            // oracleStmt.setObject(i, varArray.getKeyArray());
            // Object objectArray[] = new Object[] {"aaa", "bbb", "ccc"};
            // oracleStmt.setObject(i, objectArray);
            oracleStmt.setArray(i, oracleArray);
        } else {
            oracleStmt.setObject(i, object);
        }
        return true;
    } catch (Exception e) {
        log.log(Level.FINE, e.toString(), e);
        return false;
    }
}

/**
     * The implementation for this specific type. This method is called internally by the
     * Spring Framework during the out parameter processing and it's not accessed by application
     * code directly.
     * @see org.springframework.jdbc.core.support.AbstractSqlTypeValue
     */
protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
    if (typeName == null && defaultTypeName == null) {
        throw new InvalidDataAccessApiUsageException("The typeName is null in this context. Consider setting the defaultTypeName.");
    }
    ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName != null ? typeName : defaultTypeName, conn);
    ARRAY array = new ARRAY(arrayDescriptor, conn, values);
    return array;
}

/**
	 * Function to return EventSet from visit information
	 * 
	 * @param encounterNumList
	 * @param detailFlag
	 * @param blobFlag
	 * @param statusFlag
	 * @return EventSet
	 * @throws I2B2DAOException
	 */
public EventSet getVisitsByEncounterNum(List<String> encounterNumList, boolean detailFlag, boolean blobFlag, boolean statusFlag) throws I2B2DAOException {
    EventSet eventSet = new EventSet();
    RPDRPdoFactory.EventBuilder eventBuilder = new RPDRPdoFactory.EventBuilder(detailFlag, blobFlag, statusFlag);
    System.out.println("input encounter list size " + encounterNumList.size());
    Connection conn = null;
    PreparedStatement query = null;
    String tempTableName = "";
    try {
        conn = getDataSource().getConnection();
        // create type StudentIdArrayType as varray(1000) of integer;
        String selectClause = getSelectClause(detailFlag, blobFlag, statusFlag);
        String joinClause = getLookupJoinClause(detailFlag, blobFlag, statusFlag);
        String serverType = dataSourceLookup.getServerType();
        if (serverType.equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
            //(oracle.jdbc.driver.OracleConnection) ((WrappedConnection) conn)
            oracle.jdbc.driver.OracleConnection conn1 = null;
            //				.getUnderlyingConnection();
            String finalSql = "SELECT " + selectClause + " FROM " + getDbSchemaName() + "visit_dimension visit \n" + joinClause + " WHERE visit.encounter_num IN (SELECT * FROM TABLE (cast (? as QT_PDO_QRY_STRING_ARRAY)))";
            log.debug("Executing sql[" + finalSql + "]");
            query = conn1.prepareStatement(finalSql);
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor("QT_PDO_QRY_STRING_ARRAY", conn1);
            oracle.sql.ARRAY paramArray = new oracle.sql.ARRAY(desc, conn1, encounterNumList.toArray(new String[] {}));
            query.setArray(1, paramArray);
        } else if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER) || serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
            log.debug("creating temp table");
            java.sql.Statement tempStmt = conn.createStatement();
            tempTableName = getDbSchemaName() + SQLServerFactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE;
            try {
                tempStmt.executeUpdate("drop table " + tempTableName);
            } catch (SQLException sqlex) {
                ;
            }
            uploadTempTable(tempStmt, tempTableName, encounterNumList);
            String finalSql = "SELECT " + selectClause + " FROM " + getDbSchemaName() + "visit_dimension visit \n" + joinClause + " WHERE visit.encounter_num IN (select distinct CAST(coalesce(char_param1, '0') as integer) FROM " + tempTableName + " ) order by encounter_num,patient_num ";
            log.debug("Executing [" + finalSql + "]");
            query = conn.prepareStatement(finalSql);
        }
        long startTimeSql = System.currentTimeMillis();
        ResultSet resultSet = query.executeQuery();
        long endTimeSql = System.currentTimeMillis();
        long totalTimeSql = endTimeSql - startTimeSql;
        log.debug("********* Total time for visit sql ****" + totalTimeSql);
        long startTime = System.currentTimeMillis();
        while (resultSet.next()) {
            EventType event = eventBuilder.buildEventSet(resultSet, "i2b2", this.metaDataParamList);
            eventSet.getEvent().add(event);
        }
        long endTime = System.currentTimeMillis();
        long totalTime = endTimeSql - startTimeSql;
        log.debug("********* Total time for visit objects ****" + totalTime);
    } catch (SQLException sqlEx) {
        log.error("", sqlEx);
        throw new I2B2DAOException("sql exception", sqlEx);
    } catch (IOException ioEx) {
        log.error("", ioEx);
        throw new I2B2DAOException("IO exception", ioEx);
    } finally {
        if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
            PdoTempTableUtil tempUtil = new PdoTempTableUtil();
            tempUtil.deleteTempTableSqlServer(conn, tempTableName);
        }
        try {
            JDBCUtil.closeJdbcResource(null, query, conn);
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        }
    }
    return eventSet;
}

/**
     * {@inheritDoc}
     */
public QueryParameters processInput(Statement stmt, QueryParameters params) throws SQLException {
    QueryParameters result = new QueryParameters(params);
    Object value = null;
    Object convertedValue = null;
    Connection conn = stmt.getConnection();
    for (String parameterName : params.keySet()) {
        value = params.getValue(parameterName);
        convertedValue = null;
        try {
            if (params.getType(parameterName) == MjdbcTypes.ARRAY) {
                if (value instanceof Object[]) {
                    //convertedValue = oraConn.createARRAY(convertJavaClassToSqlType(value.getClass().getComponentType().getSimpleName()), value);
                    convertedValue = MappingUtils.invokeFunction(conn, "createARRAY", new Class[] { String.class, Object.class }, new Object[] { convertJavaClassToSqlType(value.getClass().getComponentType().getSimpleName()), value });
                } else if (value instanceof Collection) {
                    Object[] valueArray = ((Collection) value).toArray();
                    //convertedValue = oraConn.createARRAY(convertJavaClassToSqlType(valueArray.getClass().getComponentType().getSimpleName()), valueArray);
                    convertedValue = MappingUtils.invokeFunction(conn, "createARRAY", new Class[] { String.class, Object.class }, new Object[] { convertJavaClassToSqlType(valueArray.getClass().getComponentType().getSimpleName()), valueArray });
                } else {
                    convertedValue = value;
                }
            } else if (params.getType(parameterName) == MjdbcTypes.BLOB) {
                Object blob = null;
                Class blobClazz = Class.forName("oracle.sql.BLOB");
                int blobSessionDuration = (Integer) MappingUtils.returnStaticField(blobClazz, "DURATION_SESSION");
                // creating object only if it is one of 3 supported types
                if (value instanceof String) {
                    //blob = oracle.sql.BLOB.createTemporary(conn, false, oracle.sql.BLOB.DURATION_SESSION);
                    blob = MappingUtils.invokeStaticFunction(blobClazz, "createTemporary", new Class[] { Connection.class, boolean.class, int.class }, new Object[] { conn, false, blobSessionDuration });
                    convertedValue = TypeHandlerUtils.convertBlob(blob, (String) value);
                } else if (value instanceof InputStream) {
                    //blob = oracle.sql.BLOB.createTemporary(conn, false, oracle.sql.BLOB.DURATION_SESSION);
                    blob = MappingUtils.invokeStaticFunction(blobClazz, "createTemporary", new Class[] { Connection.class, boolean.class, int.class }, new Object[] { conn, false, blobSessionDuration });
                    convertedValue = TypeHandlerUtils.convertBlob(blob, (InputStream) value);
                } else if (value instanceof byte[]) {
                    //blob = oracle.sql.BLOB.createTemporary(conn, false, oracle.sql.BLOB.DURATION_SESSION);
                    blob = MappingUtils.invokeStaticFunction(blobClazz, "createTemporary", new Class[] { Connection.class, boolean.class, int.class }, new Object[] { conn, false, blobSessionDuration });
                    convertedValue = TypeHandlerUtils.convertBlob(blob, (byte[]) value);
                } else {
                    convertedValue = value;
                }
            } else if (params.getType(parameterName) == MjdbcTypes.CLOB) {
                Object clob = null;
                Class clobClazz = Class.forName("oracle.sql.CLOB");
                int clobSessionDuration = (Integer) MappingUtils.returnStaticField(clobClazz, "DURATION_SESSION");
                if (value instanceof String) {
                    //clob = oracle.sql.CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
                    clob = MappingUtils.invokeStaticFunction(clobClazz, "createTemporary", new Class[] { Connection.class, boolean.class, int.class }, new Object[] { conn, false, clobSessionDuration });
                    convertedValue = TypeHandlerUtils.convertClob(clob, (String) value);
                } else if (value instanceof InputStream) {
                    //clob = oracle.sql.CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
                    clob = MappingUtils.invokeStaticFunction(clobClazz, "createTemporary", new Class[] { Connection.class, boolean.class, int.class }, new Object[] { conn, false, clobSessionDuration });
                    convertedValue = TypeHandlerUtils.convertClob(clob, (InputStream) value);
                } else if (value instanceof byte[]) {
                    //clob = oracle.sql.CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
                    clob = MappingUtils.invokeStaticFunction(clobClazz, "createTemporary", new Class[] { Connection.class, boolean.class, int.class }, new Object[] { conn, false, clobSessionDuration });
                    convertedValue = TypeHandlerUtils.convertClob(clob, (byte[]) value);
                } else {
                    convertedValue = value;
                }
            } else if (params.getType(parameterName) == MjdbcTypes.SQLXML) {
                // as far as I know - it is not completely supported by Oracle JDBC Driver.
                // below you will find example usage of XMLType.
                // Other option would be using TO_CLOB on SQLXML(XMLType) type in PL/SQL.
                // oracle.xdb.XMLType
                // You also need to include the xdb.jar and xmlparserv2.jar files in the
                // classpath environment variable to use SQLXML type data, if they are
                // not already present in the classpath.
                /*
				if (value instanceof String) {
					XMLType xmlType = oracle.xdb.XMLType.createXML(conn, (String) value);
					convertedValue = xmlType;
				} else if (value instanceof InputStream) {
					XMLType xmlType = oracle.xdb.XMLType.createXML(conn, (InputStream) value);
					convertedValue = xmlType;
				} else if (value instanceof byte[]) {
					XMLType xmlType = oracle.xdb.XMLType.createXML(conn, new String((byte[]) value));
					convertedValue = xmlType;
				} else {
					convertedValue = value;
				}
				*/
                convertedValue = value;
            } else if (params.getType(parameterName) == MjdbcTypes.VARCHAR && TypeHandlerUtils.isJDBC3(overrider) == true && value instanceof Reader) {
                convertedValue = TypeHandlerUtils.toString((Reader) value);
            } else if (params.getType(parameterName) == MjdbcTypes.VARBINARY && TypeHandlerUtils.isJDBC3(overrider) == true && value instanceof InputStream) {
                convertedValue = TypeHandlerUtils.toByteArray((InputStream) value);
            } else {
                convertedValue = value;
            }
        } catch (MjdbcException ex) {
            logger.warning("Failed to process resource: " + parameterName + ". Might lead to error during query execution!!");
            convertedValue = value;
        } catch (ClassNotFoundException e) {
            logger.warning("Failed to process resource: " + parameterName + " due to ClassNotFoundException. Please check if Oracle JDBC Driver is present in class path");
            convertedValue = value;
        }
        // any other type processing can be added to DataBase specific TypeHandler implementation.
        result.updateValue(parameterName, convertedValue);
    }
    return result;
}

/**
     * Enable several Oracle roles at once
     */
protected final void setRoles(final String[] pRoles) throws SQLException {
    final String dbStatement = " begin " + "   sys.dbms_application_info.set_module( module_name => ? , action_name => ? ); " + "   app_user.pk_set_app_user_roles.pr_enable_roles( p_role_list => ? );" + " end;  ";
    OracleCallableStatement dbCstmt = null;
    try {
        LOGGER.debug("Set db role");
        dbCstmt = (OracleCallableStatement) getConnection().prepareCall(dbStatement);
        LOGGER.debug("DB statement prepared -> {}", dbStatement);
        /* transform the pRoles string array into a table of Oracle objects */
        STRUCT[] dbRoles = new STRUCT[pRoles.length];
        LOGGER.debug("Role STRUCT[] built : size -> {}", pRoles.length);
        StructDescriptor tyString = StructDescriptor.createDescriptor("APP_UTILITY.TY_STRING", getConnection());
        LOGGER.debug("ty_string structure retrieved");
        ArrayDescriptor ttyString = ArrayDescriptor.createDescriptor("APP_UTILITY.TTY_STRING", getConnection());
        LOGGER.debug("tty_string structure retrieved");
        for (int i = 0; i < dbRoles.length; i++) {
            String[] role = { pRoles[i] };
            dbRoles[i] = new STRUCT(tyString, getConnection(), role);
        }
        /* if we've got this far our ARRAY is almost prepared */
        dbCstmt.setString(1, "PK_SET_APP_USER_ROLES");
        dbCstmt.setString(2, "PR_ENABLE_ROLES");
        dbCstmt.setARRAY(3, new ARRAY(ttyString, getConnection(), dbRoles));
        LOGGER.debug("DB params registered");
        dbCstmt.execute();
        LOGGER.debug("DB statement executed");
    } catch (SQLException sqle) {
        LOGGER.error("SQL Exception whilst setting role -> {}; role -> {}; error code -> {}; sql state -> {}", new Object[] { sqle.getMessage(), pRoles, sqle.getErrorCode(), sqle.getSQLState() });
        throw sqle;
    } finally {
        CloseResource.close(dbCstmt);
    }
}

public static void createAndEvaluateReport(final String statement, final String methodName, final BLOB template, final ARRAY arguments, final ARRAY[] result) throws SQLException, IOException {
    final Report report;
    boolean isStatementBased = statement != null && statement.trim().length() != 0;
    boolean isFunctionBased = methodName != null && methodName.trim().length() != 0;
    final InputStream $template = template == null ? null : template.getBinaryStream();
    if (isStatementBased && !isFunctionBased)
        report = reportEngine.createReportFromStatement(statement, $template);
    else if (isFunctionBased && !isStatementBased)
        report = reportEngine.createReport(methodName, $template, extractVargs(arguments));
    else {
        if ($template != null)
            $template.close();
        throw new RuntimeException("A report must either be statement or function based!");
    }
    result[0] = convertListOfCellsToOracleArray(report.evaluateWorkbook());
}

public void testMetadataDIMInfo() throws Exception {
    if (conn == null)
        return;
    Statement st = conn.createStatement();
    st.execute("SELECT srid,diminfo FROM USER_SDO_GEOM_METADATA where TABLE_NAME = 'ORA_TEST_LINES'");
    ResultSet set = st.getResultSet();
    assertTrue(set.next());
    int srid = set.getInt(1);
    CoordinateReferenceSystem crs = data.determineCRS(srid);
    ARRAY array = (ARRAY) set.getObject(2);
    Datum data[] = array.getOracleArray();
    double minx = Double.NaN;
    double miny = Double.NaN;
    double maxx = Double.NaN;
    double maxy = Double.NaN;
    for (int i = 0; i < data.length; i++) {
        Datum datum = data[i];
        System.out.println(datum.getClass());
        STRUCT diminfo = (STRUCT) datum;
        Datum info[] = diminfo.getOracleAttributes();
        String ord = info[0].stringValue();
        double min = info[1].doubleValue();
        double max = info[2].doubleValue();
        // TODO use this for accurate JTS PercisionModel!
        double precision = info[3].doubleValue();
        if ("X".equalsIgnoreCase(ord)) {
            minx = min;
            maxx = max;
        }
        if ("Y".equalsIgnoreCase(ord)) {
            miny = min;
            maxy = max;
        }
    }
    Envelope extent = new Envelope(minx, maxx, miny, maxy);
    ReferencedEnvelope ref = new ReferencedEnvelope(extent, crs);
    assertFalse(ref.isNull());
}

protected void flush() {
    statistics.get(batch).startTimer(DataWriterStatisticConstants.DATABASEMILLIS);
    try {
        if (rowArrays.size() > 0) {
            JdbcSqlTransaction jdbcTransaction = (JdbcSqlTransaction) transaction;
            Connection c = jdbcTransaction.getConnection();
            Connection oracleConnection = jdbcExtractor.getNativeConnection(c);
            Column[] columns = targetTable.getColumns();
            StringBuilder questions = new StringBuilder();
            for (int i = 0; i <= columns.length; i++) {
                questions.append("?, ");
            }
            questions.replace(questions.length() - 2, questions.length(), "");
            String sql = String.format("{ call %s(%s) }", buildProcedureName("i", targetTable), questions);
            OracleCallableStatement stmt = (OracleCallableStatement) oracleConnection.prepareCall(sql);
            for (int i = 0; i < columns.length; i++) {
                Column column = columns[i];
                ArrayDescriptor type = ArrayDescriptor.createDescriptor(getTypeName(column.getMappedTypeCode()), oracleConnection);
                List<Object> columnData = rowArrays.get(i);
                ARRAY array = new ARRAY(type, oracleConnection, columnData.toArray(new Object[columnData.size()]));
                stmt.setObject(i + 1, array);
            }
            int errorIndex = columns.length + 1;
            stmt.registerOutParameter(errorIndex, OracleTypes.ARRAY, getTypeName(Types.INTEGER));
            stmt.execute();
            ARRAY errorsArray = stmt.getARRAY(errorIndex);
            int[] errors;
            if (errorsArray != null) {
                errors = errorsArray.getIntArray();
            } else {
                errors = new int[0];
            }
            if (errors.length > 0) {
                // set the statement count so the failed row number get reported correctly
                statistics.get(batch).set(DataWriterStatisticConstants.STATEMENTCOUNT, errors[0]);
                throw new BulkSqlException(errors, lastEventType.toString(), sql);
            }
        }
    } catch (SQLException ex) {
        throw platform.getSqlTemplate().translate(ex);
    } finally {
        lastEventType = null;
        rowArrays.clear();
        statistics.get(batch).stopTimer(DataWriterStatisticConstants.DATABASEMILLIS);
    }
}

/**
     * Convert provided SDO_GEOMETRY to JTS Geometry.
     * <p>
     * Will return <code>null</code> as <code>null</code>.
     * </p>
     * 
     * @param sdoGeometry datum STRUCT to be converted to a double[]
     * 
     * @return JTS <code>Geometry</code> representing the provided <code>datum</code>
     * @throws SQLException
     * 
     * @see net.refractions.jspatial.Converter#toObject(oracle.sql.STRUCT)
     */
public Geometry asGeometry(STRUCT sdoGeometry) throws SQLException {
    // Note Returning null for null Datum
    if (sdoGeometry == null)
        return null;
    Datum data[] = sdoGeometry.getOracleAttributes();
    final int GTYPE = asInteger(data[0], 0);
    final int SRID = asInteger(data[1], SDO.SRID_NULL);
    final double POINT[] = asDoubleArray((STRUCT) data[2], Double.NaN);
    final int ELEMINFO[] = asIntArray((ARRAY) data[3], 0);
    final double ORDINATES[] = asDoubleArray((ARRAY) data[4], Double.NaN);
    ;
    return SDO.create(geometryFactory, GTYPE, SRID, POINT, ELEMINFO, ORDINATES);
}

public int insertMovie(Movies movie, int physique, int digitale) {
    try {
        ArrayDescriptor tabVarchar = ArrayDescriptor.createDescriptor("VARCHAR2_T", con);
        ArrayDescriptor tabBlob = ArrayDescriptor.createDescriptor("BLOB_T", con);
        /*Identifiant du film*/
        /**********************************************/
        csmt.setInt(1, movie.getId());
        /*Imdb id du film*/
        /**********************************************/
        csmt.setString(2, movie.getImdb_id());
        /*Nom du film*/
        /**********************************************/
        csmt.setString(3, movie.getName());
        /*Résumé du film*/
        /**********************************************/
        csmt.setString(4, movie.getOverview());
        /*Note du film*/
        /**********************************************/
        csmt.setFloat(5, (movie.getRating() == -1 ? -1 : movie.getRating()));
        /*Date de sortie du film*/
        /**********************************************/
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.sql.Date dateSql = null;
        java.util.Date dateJava = null;
        try {
            dateJava = sdf.parse(movie.getReleased());
            dateSql = new java.sql.Date(dateJava.getTime());
        } catch (ParseException ex) {
            Logger.getLogger(OracleAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        csmt.setDate(6, dateSql);
        /*Adresse de la bande annonce du film*/
        /**********************************************/
        csmt.setString(7, movie.getTrailer());
        /*Est-ce que le film est traduit ?*/
        /**********************************************/
        csmt.setBoolean(8, movie.isTranslated());
        /*Nombre de vote pour la popularité du film*/
        /**********************************************/
        csmt.setInt(9, (movie.getVotes() == -1 ? null : movie.getVotes()));
        /*Liste des acteurs du film*/
        /**********************************************/
        String[] nomActeurs = getTabCast(movie.getCast(), "Actor");
        if (nomActeurs == null) {
            csmt.setNull(10, OracleTypes.ARRAY, "VARCHAR2_T");
        } else {
            Array sqlArray = new ARRAY(tabVarchar, con, nomActeurs);
            csmt.setArray(10, sqlArray);
            sqlArray.free();
            sqlArray = null;
        }
        /*Liste des personnages joué par les acteurs*/
        /**********************************************/
        LinkedList<String> characters = null;
        if (nomActeurs != null) {
            for (int i = 0; i < nomActeurs.length; i++) {
                String name = getCharacter(nomActeurs[i], movie);
                if (characters == null)
                    characters = new LinkedList<>();
                characters.add(name);
            }
        }
        String[] nomCharacters = null;
        if (characters != null) {
            int cpt = 0;
            nomCharacters = new String[characters.size()];
            for (String name : characters) {
                nomCharacters[cpt] = name;
                cpt++;
            }
            Array sqlArray = new ARRAY(tabVarchar, con, nomCharacters);
            csmt.setArray(11, sqlArray);
            sqlArray.free();
            sqlArray = null;
            nomActeurs = null;
        } else {
            csmt.setNull(11, OracleTypes.ARRAY, "VARCHAR2_T");
        }
        /*Liste des réalisateurs du film*/
        /**********************************************/
        String[] nomRealisateur = getTabCast(movie.getCast(), "Director");
        if (nomRealisateur == null) {
            csmt.setNull(12, OracleTypes.ARRAY, "VARCHAR2_T");
        } else {
            Array sqlArray = new ARRAY(tabVarchar, con, nomRealisateur);
            csmt.setArray(12, sqlArray);
            sqlArray.free();
            sqlArray = null;
            nomActeurs = null;
        }
        /*Liste des pays qui ont produit le film*/
        /**********************************************/
        String[] nomCountries = getTabCountries(movie.getCountries());
        if (nomCountries == null) {
            csmt.setNull(13, OracleTypes.ARRAY, "VARCHAR2_T");
        } else {
            Array sqlArray = new ARRAY(tabVarchar, con, nomCountries);
            csmt.setArray(13, sqlArray);
            sqlArray.free();
            sqlArray = null;
            nomActeurs = null;
        }
        /*Copie physique et digitale.*/
        /**********************************************/
        csmt.setInt(14, physique);
        csmt.setInt(15, digitale);
        /*Liste des studios qui ont produit le film*/
        /**********************************************/
        String[] nomStudios = getTabStudios(movie.getStudios());
        if (nomStudios == null) {
            csmt.setNull(16, OracleTypes.ARRAY, "VARCHAR2_T");
        } else {
            Array sqlArray = new ARRAY(tabVarchar, con, nomStudios);
            csmt.setArray(16, sqlArray);
            sqlArray.free();
            sqlArray = null;
            nomActeurs = null;
        }
        /*Liste des genres du film*/
        /**********************************************/
        String[] nomGenres = getTabGenres(movie.getGenres());
        if (nomGenres == null) {
            csmt.setNull(17, OracleTypes.ARRAY, "VARCHAR2_T");
        } else {
            Array sqlArray = new ARRAY(tabVarchar, con, nomGenres);
            csmt.setArray(17, sqlArray);
            sqlArray.free();
            sqlArray = null;
            nomActeurs = null;
        }
        /**********************************************/
        if (movie.getCertification() == null) {
            csmt.setNull(18, OracleTypes.VARCHAR);
        } else {
            String certification = getCertification(movie.getCertification());
            csmt.setString(18, certification);
        }
        /*Liste des langues du film*/
        /**********************************************/
        String[] nomLangues = getTabLangues(movie.getLanguages_spoken());
        if (nomLangues == null) {
            csmt.setNull(19, OracleTypes.ARRAY, "VARCHAR2_T");
        } else {
            Array sqlArray = new ARRAY(tabVarchar, con, nomLangues);
            csmt.setArray(19, sqlArray);
            sqlArray.free();
            sqlArray = null;
            nomActeurs = null;
        }
        BLOB[] tabBlobs = getTabImage(movie.getId());
        if (tabBlobs == null) {
            csmt.setNull(20, OracleTypes.ARRAY, "BLOB_T");
        } else {
            Array sqlArray = new ARRAY(tabBlob, con, tabBlobs);
            csmt.setArray(20, sqlArray);
            sqlArray.free();
            sqlArray = null;
            nomActeurs = null;
        }
        csmt.setInt(21, movie.getRuntime());
    } catch (SQLException ex) {
        Logger.getLogger(OracleAccess.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        /*Execution de la procédure*/
        csmt.execute();
        //getImage(movie.getId(), movie.getBackdrops());
        /*On ferme la transaction*/
        csmt.close();
        finish("commit");
        return 1;
    } catch (SQLException ex) {
        finish("rollback");
        Logger.getLogger(OracleAccess.class.getName()).log(Level.SEVERE, null, ex);
        return -1;
    }
}

private void registerOutput(Connection conn, CallableStatement callStmt, CallArgument argument, List<Out> outputs) throws SQLException {
    if (argument == null) {
        return;
    }
    AdapterMapping outputAdapter = getOutputAdapter(argument, outputs);
    int argumentIndex = argument.getSequence();
    int argumentType = argument.getSqlType();
    String argumentTypeName = getArgumentTypeName(outputAdapter, argument.getTypeName());
    if (isStructType(argument)) {
        argumentType = Types.STRUCT;
        callStmt.registerOutParameter(argumentIndex, argumentType, argumentTypeName);
    } else if (isArrayOfStructType(argument)) {
        argumentType = Types.ARRAY;
        callStmt.registerOutParameter(argumentIndex, argumentType, argumentTypeName);
    } else {
        callStmt.registerOutParameter(argumentIndex, argumentType);
    }
}

private static void init() throws SQLException {
    if (!initialized) {
        try {
            Class<?> arrayDescriptorClass = Class.forName("oracle.sql.ArrayDescriptor");
            arrayDescriptorConstructor = arrayDescriptorClass.getConstructor(String.class, Connection.class);
            Class<?> arrayClass = Class.forName("oracle.sql.ARRAY");
            arrayConstructor = arrayClass.getConstructor(arrayDescriptorClass, Connection.class, Object.class);
        } catch (Exception e) {
            throw new SQLException(e.toString());
        }
        initialized = true;
    }
}

/**
	 * Reads a {@link Geometry} representing the MDSYS.GEOMETRY
	 * provided in the STRUCT. The type of geometry created 
	 * depends on the Geometry type specified within the STRUCT.
	 * The SRID of the created geometry is set to be the same as the input SRID.
	 *
	 * @param struct The MDSYS.GEOMETRY Object to decode
	 * @return the Geometry if one could be created, null otherwise
	 * 
	 * @throws SQLException if a read error occurs while accessing the struct
   * @throws IllegalArgumentException if an unsupported geometry type or encoding error is found
	 */
public Geometry read(STRUCT struct) throws SQLException {
    // Return null for null input
    if (struct == null)
        return null;
    Datum data[] = struct.getOracleAttributes();
    int gType = OraUtil.toInteger(data[0], 0);
    int SRID = OraUtil.toInteger(data[1], OraGeom.SRID_NULL);
    double point[] = OraUtil.toDoubleArray((STRUCT) data[2], Double.NaN);
    int elemInfo[] = OraUtil.toIntArray((ARRAY) data[3], 0);
    double ordinates[] = OraUtil.toDoubleArray((ARRAY) data[4], Double.NaN);
    OraGeom oraGeom = new OraGeom(gType, SRID, point, elemInfo, ordinates);
    Geometry geom = read(oraGeom);
    // Set SRID of created Geometry to be the same as input (regardless of geomFactory SRID)
    if (geom != null)
        geom.setSRID(SRID);
    return geom;
}

/**
     * 
     * @param rs result set of the dimension info query
     * @param column column of the dimension info
     * @return the envelope out of the dimension info 
     *         (assumption: x before y or longitude before latitude)
     *         or null, if no data is in the specified column
     * @throws SQLException if dimension info can not be parsed
     * @author Hendrik Peilke
     */
private Envelope decodeDiminfoEnvelope(ResultSet rs, int column) throws SQLException {
    ARRAY returnArray = (ARRAY) rs.getObject(column);
    if (returnArray == null) {
        throw new SQLException("no data inside the specified column");
    }
    Datum data[] = returnArray.getOracleArray();
    if (data.length < 2) {
        throw new SQLException("too little dimension information found in sdo_geom_metadata");
    }
    Datum[] xInfo = ((STRUCT) data[0]).getOracleAttributes();
    Datum[] yInfo = ((STRUCT) data[1]).getOracleAttributes();
    Double minx = xInfo[1].doubleValue();
    Double maxx = xInfo[2].doubleValue();
    Double miny = yInfo[1].doubleValue();
    Double maxy = yInfo[2].doubleValue();
    return new Envelope(minx, maxx, miny, maxy);
}

/**
     * INTERNAL:
     * Platforms that support java.sql.Array may override this method.
     * @return Array
     */
@Override
public Array createArray(String elementDataTypeName, Object[] elements, Connection connection) throws SQLException {
    return new oracle.sql.ARRAY(new oracle.sql.ArrayDescriptor(elementDataTypeName, connection), connection, elements);
}

@Override
public ARRAY getARRAY(int arg0) throws SQLException {
    // TODO Auto-generated method stub
    return null;
}