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
Posted by Daniel Borgstrom at 17:29
Labels: ,

18 comments:

  1. sai_narayan@yahoo.comJanuary 20, 2012 at 7:55 AM

    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. Daniel BorgstromJanuary 20, 2012 at 3:09 PM

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

      Delete
    2. AnonymousAugust 10, 2012 at 8:29 PM

      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. AnonymousSeptember 16, 2014 at 8:28 AM

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

      Delete
  2. bakeeMarch 7, 2012 at 7:57 AM

    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. AnonymousApril 20, 2012 at 12:19 PM

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

    Ravi

    ReplyDelete
  4. AnonymousDecember 17, 2012 at 6:43 AM

    It worked. Thanks!
    -Shilpi Karan Anand

    ReplyDelete
  5. AnonymousMarch 10, 2014 at 8:37 AM

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

    Rajashekar.
    kommaoracleapplications.blogspot.com

    ReplyDelete
  6. UnknownJuly 15, 2014 at 12:12 AM

    Thank you. It worked for me.

    ReplyDelete
  7. AnonymousMarch 25, 2015 at 4:48 PM

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

    ReplyDelete
  8. UnknownJanuary 16, 2016 at 3:04 PM

    Thank You. The solution worked for me.

    ReplyDelete
  9. AnonymousMarch 3, 2016 at 5:13 PM

    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
  10. UnknownFebruary 28, 2017 at 12:19 PM

    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
  11. Ekaansh agrawalNovember 29, 2017 at 11:06 AM

    Worked like a charm. Thanks :)

    ReplyDelete
  12. roshanMarch 2, 2018 at 11:11 AM

    It worked..Thank you so much..

    ReplyDelete
  13. AnonymousApril 21, 2018 at 8:08 AM

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

    ReplyDelete
  14. The Old CodgerAugust 27, 2018 at 4:42 PM

    Didn't work for me.

    ReplyDelete
  15. Al BundyAugust 20, 2019 at 6:08 PM

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

    ReplyDelete

Subscribe to: Post Comments (Atom)
javascript:void(0)