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

17 comments

Archived from the original blog on Blogger. New comments are closed for now.

sai_narayan@yahoo.com20 Jan 2012
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.
1. Daniel BorgströmAuthor20 Jan 2012
  Hm, that's very strange. What's the error message you are getting?
2. Anonymous10 Aug 2012
  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.
3. Anonymous16 Sept 2014
  Thanks it worked !! Along with that check for the encoding to be 'UTF-8' , jdev mentions it as 'windows-1252'
bakee7 Mar 2012
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 ?
Anonymous20 Apr 2012
We tried that and I worked successfully...thanks Daniel

Ravi
Anonymous17 Dec 2012
It worked. Thanks!
-Shilpi Karan Anand
Anonymous10 Mar 2014
It's working fine... Thanks a lot...

Rajashekar.
kommaoracleapplications.blogspot.com
Anonymous15 Jul 2014
Thank you. It worked for me.
Anonymous25 Mar 2015
Thank you very much, sir. The internet is amazing.
Anonymous16 Jan 2016
Thank You. The solution worked for me.
Anonymous3 Mar 2016
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
Ekaansh agrawal29 Nov 2017
Worked like a charm. Thanks :)
roshan2 Mar 2018
It worked..Thank you so much..
The Old Codger27 Aug 2018
Didn't work for me.
Al Bundy20 Aug 2019
This worked for me. Changed Binding Style to Oracle positional, and my VO started working! Daniel, you are awesome.
Anonymous9 Jun 2025
thanks a lot Danial its working