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