Re: howto? insert into with returning clause

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 26 May 2001 20:18:12 -0400
Message-ID: <nsh0ht8lgma69qamopdacuibvhpvd1mqui@4ax.com>

A copy of this was sent to kevinh_at_computersandcontrols.com (if that email address didn't require changing) On Fri, 25 May 2001 14:33:24 +0000 (UTC), you wrote:

>Say I have the following:
>
> create table kk ( id integer not null );
>
>I would like to execute an insert statement with an arbitrary value for id that
>returns the assigned value. So, the (Oracle) statement is:
>
> insert into kk (id) values (?) returning id into ?
>
>I'm using JDBC to execute the statement but I don't know which kind of Statement
>to use - PreparedStatement or CallableStatement. As far as I can see, it should
>be a Callable.
>Is the returned field an output parameter? This is what I've got so far...

public static void main (String args[]) throws SQLException {

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

    Connection con =

      DriverManager.getConnection
      ("jdbc:oracle:thin:@aria-dev:1521:ora816dev",
        "scott", "tiger");

    CallableStatement stmt =
        con.prepareCall("begin "+
                        "insert into t (id) values (?) "+
                        "returning id into ?;"+
                        "end;");

    stmt.setInt(1,1001);

    stmt.registerOutParameter(2, Types.INTEGER);     stmt.execute();
    System.out.println( "Returned value = " + stmt.getInt(2) ); }

you gotta use a plsql block for that.....

[snip]

>
>Any help would be appreciated!
>Thanks,
>Kevin

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat May 26 2001 - 19:18:12 CDT