Re: id of last insert

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 22 Dec 1999 07:06:08 -0500
Message-ID: <bhf16s8qcq2acrca80utv9577nucoqhih4@4ax.com>

A copy of this was sent to buurd_at_my-deja.com (if that email address didn't require changing) On Wed, 22 Dec 1999 08:31:06 GMT, you wrote:

> Hi!
>I've asked this question before and got the answer to use
>insert...returning. The problem is that I use JDBC and it doesn't
>support returning from DML. So how to solve the problem?? Can a look
>tables somehowe so i can do two queries undisturbed??
>
>Tia

Try this example:

• returning.sql ----------------------- drop table t; drop sequence t_seq;

create table t ( x int );
create sequence t_seq;

create or replace trigger t_trigg
before insert on t
for each row
begin

    select t_seq.nextval into :new.x from dual; end;
/

---------------------- end ----------------------------------


---------------- returning.java ---------------------------
import java.io.*;

import java.sql.*;
import java.util.*;

class Returning
{

public static void main(String args[])
{

    Statement statement = null;

    try
    {
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

    Connection connection =

      DriverManager.getConnection
        ("jdbc:oracle:thin:@slackdog:1521:oracle8", "scott", "tiger");

    connection.setAutoCommit(false);

    statement = connection.createStatement();

    CallableStatement cstmt = connection.prepareCall(

        "begin insert into t(x) values (0) return x into ?; end;" );

    cstmt.registerOutParameter( 1, java.sql.Types.INTEGER );     System.out.println( "prepared & registered" );     cstmt.executeUpdate();

    int newVal = cstmt.getInt( 1 );
    System.out.println( newVal + " assigned on input" );

    }
    catch( Exception e )
    {

        e.printStackTrace();
    }

}

}

---

$ java Returning
prepared & registered
1 assigned on input

$ java Returning
prepared & registered
2 assigned on input

.....

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 22 1999 - 06:06:08 CST