Home / Questions / 113697
Accepted
Ricardo Gabriel
Asked: 2020-11-01 05:31:10 +0800 CST 2020-11-01 05:31:10 +0800 CST

How to get the last value inserted in the Oracle XE DB?

  • 772

I am working in JEE, with WebLogic and OracleXE as database engine, I am using a DataSource (NOT JPA), in this database sequences must be created to generate autoincremental values, I have already done that and it works fine for me when I do it. I execute since it also creates a trigger to generate the value automatically, although that is another topic.

From my application I can insert data to the database, but I need to obtain the value of that ID that I mentioned in the previous paragraph.

I already executed this example that I took from the internet and it doesn't work for me, it doesn't even insert the values:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate....) VALUES (1, 1...) RETURNING OrderID INTO :last_insert_id

Any other suggestions?

Here a bit of code

  > PreparedStatement sttmt = conn.prepareStatement("INSERT INTO fabricantes (NOMBRE, PAIS) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);

    String nombre = "Colo Colo";
    String pais = "Chile";

    sttmt.setString(1, nombre);
    sttmt.setString(2, pais);


    int resultado = sttmt.executeUpdate();

    // ResultSet rs = sttmt.getGeneratedKeys();
    Long id = null;
    ResultSet rs = sttmt.getGeneratedKeys();

        if (rs.next()) {
            id = rs.getLong(1);
            // Long id = generatedKeys.getLong(1);
        }

    return id;

The table also has a data COD_FABRICANTEthat is not seen in the embed code because it is generated automatically, it is the primary key that I want to obtain precisely. The value of COD_FABRICANTEis generated autoincrementally through a sequence that I create, in the Oracle 11g database there is no value IDENTITYor value SERIALlike in other Databases. Due to WebLogic Server restriction, I am using JDK 6, the platform does not support higher JDKs.

java

2 Answers

  1. Best Answer

    One way that should work correctly is by surrounding your statement INSERTwith BEGIN ... END;so that it becomes a PL/SQL block. This should allow the use of the clause RETURNING.

    Then you use a CallableStatementto be able to register an output parameter.

    Code example:

    CallableStatement st = conn.prepareCall("BEGIN INSERT INTO fabricantes (NOMBRE, PAIS) VALUES (:nombre,:pais) RETURNING cod_fabricante INTO :cod_fabricante; END;");
st.setString("nombre", nombre);
st.setString("pais", pais);
st.registerOutParameter("cod_fabricante", java.sql.Types.BIGINT);
st.execute();
long id = st.getLong("cod_fabricante");
    • 1

  2. By jdbcand with one insertwithout returning:

    try(PreparedStatement st= connection.prepareStatement(miInsert, Statement.RETURN_GENERATED_KEYS)){
    st.setInt(1,XXX);
    ...
    st.executeUpdate();

    try (ResultSet rs = st.getGeneratedKeys()) {
        if (rs.next()) {
            Long id = rs.getLong(1);
        }
    }
}
    • 0