Problem with Oracle queries

From: Pablo J. Pedemonte <ppedemonte_at_luminaamericas.com.ar>
Date: Wed, 26 Sep 2001 19:35:38 GMT
Message-ID: <e6qs7.13635$Ir6.136883_at_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  _at__at_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 - 21:35:38 CEST