Sunday, August 14, 2011

Fix for java.sql.SQLException: Invalid column type when extending a VO

When you are extending a view object (VO) in OA Framework it's possible that you will run in to the problem java.sql.SQLException: Invalid column type, especially if it's an LOV VO you are trying to extend. When you click on the LOV torch for the first time it is working fine. But whenever you click on the GO button in the LOV Region it gives an error.

The error can look like this:
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException:
JBO-27122: SQL error during statement preparation.  
Statement: SELECT * FROM (SELECT ca.location LocId,
ca.party_site_number LocNumber,
ca.party_name PartyName,
ca.party_number PartyNumber,
ca.address1 Address1,
ca.address2 Address2,
ca.address3 Address3,
ca.address4 Address4,
ca.city City,
ca.state State,
ca.zip PostalCode,
ca.country Country,
ca.party_id partyId,
hzp.site_use_type CurrSiteUsage
FROM csi_hzpty_addresses_v ca,
hz_party_site_uses hzp,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE ca.party_id = :1
AND   ca.location = hzp.party_site_id(+)
AND   site_use_type(+) = 'INSTALL_AT'
AND   ca.status = 'A'
AND   SYSDATE BETWEEN NVL(ca.start_date_active, SYSDATE)
AND NVL(ca.end_date_active, SYSDATE)
AND   hzp.status(+) = 'A'
AND   ca.location = hcas.party_site_id
AND   hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND   hcsu.status = 'A'
AND   hcsu.site_use_code = 'SHIP_TO'
AND   hcas.org_id = nvl(FND_PROFILE.VALUE('ORG_ID'), hcas.org_id)
AND   hcsu.org_id = nvl(FND_PROFILE.VALUE('ORG_ID'), hcsu.org_id))
QRSLT  WHERE (( UPPER(ADDRESS1) like UPPER(:2) AND
(ADDRESS1 like :3 OR ADDRESS1 like :4 OR ADDRESS1 like :5 OR 
ADDRESS1 like :6)))
at oracle.apps.fnd.framework.OAException.wrapperException
(OAException.java:896)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException
(OAPageErrorHandler.java:1169)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors
(OAPageErrorHandler.java:1435)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest
(OAPageBean.java:2867)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage
(OAPageBean.java:1840)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage
(OAPageBean.java:538)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage
(OAPageBean.java:426)
at _OA._jspService(_OA.java:212)
at com.orionserver.http.OrionHttpJspPage.service
(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service
(JspPageTable.java:379)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ResourceFilterChain.doFilter
(ResourceFilterChain.java:64)
at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter
(ReleaseResFilter.java:26)
at com.evermind.server.http.EvermindFilterChain.doFilter
(EvermindFilterChain.java:15)
at oracle.apps.fnd.security.AppsServletFilter.doFilter
(AppsServletFilter.java:318)
at com.evermind.server.http.ServletRequestDispatcher.invoke
(ServletRequestDispatcher.java:621)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal
(ServletRequestDispatcher.java:370)
at com.evermind.server.http.HttpRequestHandler.doProcessRequest
(HttpRequestHandler.java:871)
at com.evermind.server.http.HttpRequestHandler.processRequest
(HttpRequestHandler.java:453)
at com.evermind.server.http.AJPRequestHandler.run
(AJPRequestHandler.java:313)
at com.evermind.server.http.AJPRequestHandler.run
(AJPRequestHandler.java:199)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run
(ServerSocketReadHandler.java:260)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run
(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:619)
## Detail 0 ##
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical
(OraclePreparedStatement.java:8516)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal
(OraclePreparedStatement.java:8034)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal
(OraclePreparedStatement.java:8767)
at oracle.jdbc.driver.OraclePreparedStatement.setObject
(OraclePreparedStatement.java:8748)...

What you need to change is the Binding Style from Oracle Named to Oracle Positional in the View Object declaration. The framework is adding a where clause to the query using bind variables that are typed :n, this is why you need to set Oracle Positional.

Query entered in the VO:
SELECT ca.location LocId,
ca.party_site_number LocNumber,
ca.party_name PartyName,
ca.party_number PartyNumber,
ca.address1 Address1,
ca.address2 Address2,
ca.address3 Address3,
ca.address4 Address4,
ca.city City,
ca.state State,
ca.zip PostalCode,
ca.country||' (Funktionsnr: '||to_char(hcsu.site_use_id)||')' Country,
ca.party_id PartyId,
hzp.site_use_type CurrSiteUsage
FROM csi_hzpty_addresses_v ca,
hz_party_site_uses hzp,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE ca.party_id = :1
AND   ca.location = hzp.party_site_id(+)
AND   site_use_type(+) = 'INSTALL_AT'
AND   ca.status = 'A'
AND   SYSDATE BETWEEN NVL(ca.start_date_active, SYSDATE) AND NVL(ca.end_date_active, SYSDATE)
AND   hzp.status(+) = 'A'
AND   ca.location = hcas.party_site_id
AND   hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND   hcsu.status = 'A'
AND   hcsu.site_use_code = 'SHIP_TO'
AND   hcas.org_id = nvl(FND_PROFILE.VALUE('ORG_ID'), hcas.org_id)
AND   hcsu.org_id = nvl(FND_PROFILE.VALUE('ORG_ID'), hcsu.org_id)

Query when click the Go button in the LOV region:
SELECT * FROM (SELECT ca.location LocId,
ca.party_site_number LocNumber,
ca.party_name PartyName,
ca.party_number PartyNumber,
ca.address1 Address1,
ca.address2 Address2,
ca.address3 Address3,
ca.address4 Address4,
ca.city City,
ca.state State,
ca.zip PostalCode,
ca.country Country,
ca.party_id partyId,
hzp.site_use_type CurrSiteUsage
FROM csi_hzpty_addresses_v ca,
hz_party_site_uses hzp,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE ca.party_id = :1
AND   ca.location = hzp.party_site_id(+)
AND   site_use_type(+) = 'INSTALL_AT'
AND   ca.status = 'A'
AND   SYSDATE BETWEEN NVL(ca.start_date_active, SYSDATE)
AND NVL(ca.end_date_active, SYSDATE)
AND   hzp.status(+) = 'A'
AND   ca.location = hcas.party_site_id
AND   hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND   hcsu.status = 'A'
AND   hcsu.site_use_code = 'SHIP_TO'
AND   hcas.org_id = nvl(FND_PROFILE.VALUE('ORG_ID'), hcas.org_id)
AND   hcsu.org_id = nvl(FND_PROFILE.VALUE('ORG_ID'), hcsu.org_id)) QRSLT
WHERE (( UPPER(ADDRESS1) like UPPER(:2) AND (ADDRESS1 like :3 OR ADDRESS1  
like  :4 OR ADDRESS1 like :5 OR ADDRESS1 like :6)))
Change Binding Style

25 comments:

  1. It didnt work for me Daniel, I changed the Binding style , redeployed the VO class and xml as well as re-imported the jpx. No luck. First time OK and next time we click on Go button it goes kaput.

    ReplyDelete
    Replies
    1. Hm, that's very strange. What's the error message you are getting?

      Delete
    2. I had same issue in VO Extension. I had to add some where clause in VO.
      I have followed below steps and fixed it in notepad.
      1) Take standard vo.xml file
      2) Copy and create new file
      3) change package name (You can check modified VO for reference and try to do the same changes in notepad)
      4) VO name
      5) write extends=""
      6) Change query where clause
      7) Just compare both file except these changes rest is same.
      8) remove or change class names details - Rowimpl javaimpl

      Hope this helps.

      Delete
    3. Thanks it worked !! Along with that check for the encoding to be 'UTF-8' , jdev mentions it as 'windows-1252'

      Delete
  2. Hi,

    i have the same problem, but i only want to extend EO. im doing all thinks with OAF documentation and nothink, invalid column type... i did extend EO, extend EOimpl.java did a substition, import document with jpxImporter (succesfully imported), restart applicaiton, loading page and error.. :/ im looking for solution since few days and i cant find :/ can u help me ?

    ReplyDelete
  3. We tried that and I worked successfully...thanks Daniel

    Ravi

    ReplyDelete
  4. It worked. Thanks!
    -Shilpi Karan Anand

    ReplyDelete
  5. It's working fine... Thanks a lot...

    Rajashekar.
    kommaoracleapplications.blogspot.com

    ReplyDelete
  6. Thank you very much, sir. The internet is amazing.

    ReplyDelete
  7. Thank You. The solution worked for me.

    ReplyDelete
  8. Awesome solution. I've been banging my head against a wall over this for a few days now. Your solution is perfect.
    Thank you sir

    ReplyDelete
  9. Asome blog good information about blog This listing is in reality very useful. in comparison to outstanding web sites i have visited, you furthermore mght protected the little by little manual to test in in this internet internet websites.
    For more about information plese check the internet site on line trainings

    ReplyDelete
  10. It worked..Thank you so much..

    ReplyDelete
  11. Nice Blog having good and useful information for more information please visit:Oracle R12 Financials Training in Ameerpet

    ReplyDelete
  12. This worked for me. Changed Binding Style to Oracle positional, and my VO started working! Daniel, you are awesome.

    ReplyDelete
  13. Thank you so much Daniel Borgstrom, worked for me

    ReplyDelete

javascript:void(0)