Oracle execution issue

[Justin Musgrove <JM...@fcci-group.com>]

Again, newbie to Ibatis.

I am trying to leverage the <selectKey> functionality with Oracle.  It
appears that the <selectKey> is not executing before the insert
therefore I am getting:

"java.sql.SQLException: ORA-01400: cannot insert NULL into
("ODS"."ENTITY_SRC"."ENT_SRC_ID")"

I searched a number of post and found nothing similar to this problem.
Although, the online documentation states to add type="pre"
(http://opensource2.atlassian.com/confluence/oss/pages/viewpage.action?p
ageId=407) to get the sequence before execution of the insert statement.


When this is added the following occurs (it isn't defined in dtd):
 Attribute "type" must be declared for element type "selectKey".

If anyone could help assist it would be much appreciated. I have a
feeling it maybe something "stupid".

Thanks in advance.

Code snippet:

  <insert id="insertEntity"
parameterClass="com.x.ods.domain.entity.Entity"> 
    <selectKey resultClass="java.lang.Integer" keyProperty="entSrcId">
       	SELECT ODS.ENT_SRC_ID_SEQ.NEXTVAL as entSrcId FROM DUAL
    </selectKey>  
    insert into ODS.ENTITY_SRC (ENT_SRC_ID, SRC_SYSTEM, SRC_ENT_NUM) 
    values (#entSrcId:DECIMAL#, #srcSystem:VARCHAR#,
#srcEntNum:VARCHAR#)
    
  </insert>

RE: Oracle execution issue

[Rashmi Dave <ra...@persistent.co.in>]

Hi Justin,

You do not need to specify what variable to select the next value into.
That's what is throwing the error.


  <insert id="insert" parameterClass="com.eobone.uam.dblayer.to.Address">
    <selectKey resultClass="java.math.BigDecimal" keyProperty="addressId">
      SELECT EOBONE.ADDRESS_SEQ.NEXTVAL FROM DUAL
    </selectKey>
    
    insert into EOBONE.ADDRESS (ADDRESS_ID, FACILITY_ID, USER_ID, ADDRESS1, 
      ADDRESS2, CITY, STATE, ZIP, PHONE, FAX, AT_LAST_USER, AT_DT_CREATED, 
      AT_LAST_DT_UPDATED) 
    values (#addressId:DECIMAL#, #facilityId:DECIMAL#, #userId:DECIMAL#, 
      #address1:VARCHAR#, #address2:VARCHAR#, #city:VARCHAR#,
#state:VARCHAR#, 
      #zip:VARCHAR#, #phone:VARCHAR#, #fax:VARCHAR#, #atLastUser:VARCHAR#, 
      TO_DATE(SYSDATE),TO_DATE(SYSDATE)) 
  
  </insert>

Hope this helps.

Regards
~Rashmi

-----Original Message-----
From: Justin Musgrove [mailto:JMusgrove@fcci-group.com] 
Sent: Friday, February 24, 2006 8:47 PM
To: user-java@ibatis.apache.org
Subject: Oracle <selectKey> execution issue

Again, newbie to Ibatis.

I am trying to leverage the <selectKey> functionality with Oracle.  It
appears that the <selectKey> is not executing before the insert
therefore I am getting:

"java.sql.SQLException: ORA-01400: cannot insert NULL into
("ODS"."ENTITY_SRC"."ENT_SRC_ID")"

I searched a number of post and found nothing similar to this problem.
Although, the online documentation states to add type="pre"
(http://opensource2.atlassian.com/confluence/oss/pages/viewpage.action?p
ageId=407) to get the sequence before execution of the insert statement.


When this is added the following occurs (it isn't defined in dtd):
 Attribute "type" must be declared for element type "selectKey".

If anyone could help assist it would be much appreciated. I have a
feeling it maybe something "stupid".

Thanks in advance.

Code snippet:

  <insert id="insertEntity"
parameterClass="com.x.ods.domain.entity.Entity"> 
    <selectKey resultClass="java.lang.Integer" keyProperty="entSrcId">
       	SELECT ODS.ENT_SRC_ID_SEQ.NEXTVAL as entSrcId FROM DUAL
    </selectKey>  
    insert into ODS.ENTITY_SRC (ENT_SRC_ID, SRC_SYSTEM, SRC_ENT_NUM) 
    values (#entSrcId:DECIMAL#, #srcSystem:VARCHAR#,
#srcEntNum:VARCHAR#)
    
  </insert>