Problem with Oracle queries

From: Pablo J. Pedemonte <ppedemonte_at_luminaamericas.com.ar>
Date: Wed, 26 Sep 2001 16:00:20 GMT
Message-ID: <bXms7.83135$T35.1361562@news.easynews.com>

Hi there! I've the following problem:

I'd like to perform an insertion (where, by means of an insert trigger, an indentity value extracted from a sequence is set to a column of row being inserted) followed by a select returning the computed identity value (i.e. the sequence's currval). That would look like this

  insert into Customer (code,name,oracleId) values (...,...,0); -- the identity column is set by the trigger
  select customer_id_seq.currval from dual;

When I separately execute these two queries everything goes fine, but I couldn't find a way to execute them together. Particularly, when I try to do it from Java like in this code snippet:

  PreparedStatement stmt = conn.prepareStatement("insert ...; select ... from dual");
  ResultSet rs = stmt.executeQuery();

I got an ORA-00911 error: 'Invalid character'

I'd like to know if there is some workaround to do this. For instance, in Sybase It can be done this way:

  PreparedStatement stmt = conn.prepareStatement("insert ..." + "\nselect @@identity");
  ResultSet rs = stmt.executeQuery();

There is one workaround I'd like to avoid: that of creating a stored procedure from the Java side. For instance, this works:

  CallableStatement stmt = conn.prepareCall(     "begin insert into Customer (code,name,oracleId) values (?,?,?) returning oracleId into ?; end;");

  stmt.setInt(1, 1);
  stmt.setString(2,null);
  stmt.setInt(3, 0);
  stmt.registerOutParameter(4, Types.INTEGER);
  stmt.execute();

  System.out.println("oracleId = " + stmt.getInt(4));

But I prefer to keep this as an "if everything fails" option.

Thanks in advance for your help,
Pablo Received on Wed Sep 26 2001 - 11:00:20 CDT