selectKey with Oracle

[Paul Glezen <pg...@us.ibm.com>]

Hi, Folks,

I get a "sequence does not exist" error message when trying to get the key
resulting from an insert into an Oracle table.  The key is a column called
"unique_product_id" which maps to uniqueProductId in my bean.  My insert
statement looks like this.

<insert id="addProduct" parameterClass="xyz.ProductEntityBean">
   <selectKey keyProperty="uniqueProductId" resultClass="long">
       select stockidsequence.nextval as id from dual
   </selectKey>
      insert into mrt_product (COMPANY_ID,  DIVISION_ID,  UPC_NO)
                      values  (#companyId#, #divisionId#, #upcNo#)
</insert>

The full error message is:

11:42:58:693 ERROR ibatis.IbatisProductMaintenance - Encountered SQL
exception during addProduct.
11:42:58:703 ERROR ibatis.IbatisProductMaintenance -
--- The error occurred in productMap.xml.
--- The error occurred while applying a parameter map.
--- Check the addProduct-SelectKey-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-02289: sequence does not exist

- Paul

Re: selectKey with Oracle

[Larry Meadors <la...@gmail.com>]

The "sequence does not exist" error is an Oracle message. 

Try it from sql*plus (with the same user/password as iBATIS is using),
and let us know how that works.

Larry


On 8/19/05, Paul Glezen <pg...@us.ibm.com> wrote:
>  
> 
> Hi, Folks,
>  
>  I get a "sequence does not exist" error message when trying to get the key
> resulting from an insert into an Oracle table. The key is a column called
> "unique_product_id" which maps to uniqueProductId in my bean. My insert
> statement looks like this.
>  
>  <insert id="addProduct" parameterClass="xyz.ProductEntityBean">
>  <selectKey keyProperty="uniqueProductId" resultClass="long">
>  select stockidsequence.nextval as id from dual
>  </selectKey>
>  insert into mrt_product (COMPANY_ID, DIVISION_ID, UPC_NO)
>  values (#companyId#, #divisionId#, #upcNo#)
>  </insert>
>  
>  The full error message is:
>  
>  11:42:58:693 ERROR ibatis.IbatisProductMaintenance - Encountered SQL
> exception during addProduct.
>  11:42:58:703 ERROR ibatis.IbatisProductMaintenance - 
>  --- The error occurred in productMap.xml. 
>  --- The error occurred while applying a parameter map. 
>  --- Check the addProduct-SelectKey-InlineParameterMap. 
>  --- Check the statement (query failed). 
>  --- Cause: java.sql.SQLException: ORA-02289: sequence does not exist
>  
>  - Paul