Skip to Content

Sync fails with error "java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name".

Issue: Full Sync fails with error "java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name".

When are we facing this issue ? - We are encountering this error when we trigger a full sync on our content catalog.

Flexiquery which is failing during Sync configuration:

 SELECT {p:pk} FROM {CMSItem AS p} WHERE {p:catalogVersion}=?tgtVer AND NOT EXISTS ( {{SELECT {ts:pk} FROM {ItemSyncTimestamp AS ts} WHERE {ts:syncJob}=0 AND {ts:targetVersion}=?tgtVer AND {ts:sourceVersion}=?srcVer AND {ts:targetItem}={p:pk} }} ) AND NOT EXISTS( {{  SELECT {pp:pk} FROM {CMSItem as pp LEFT JOIN ItemSyncTimestamp AS ts2       ON {ts2:sourceItem}={pp:pk} AND {ts2:syncJob}=0 AND {ts2:targetVersion}=?tgtVer AND {ts2:sourceVersion}=?srcVer }       WHERE {pp:uid}={p:uid} AND {pp:catalogVersion}=?srcVer AND {ts2:pk} IS NULL }} ) ORDER BY {p:creationtime} ASC, {p:pk} ASC) with arguments (srcVer,tgtVer)

Details around our Component Modeling :

  • Our component models are designed to be grouped based on functionality and each component group has its own table. Currently we have 36 such component groups.

  • CatalogVersionSyncJobs are configured to have roottypes as CMSItem and CMSRelation.

  • Thus we have 56 subtypes of CMSItem which have their own deployment table to be joined/unioned during sql query execution.

When we look for the cause for the ORA-01745, we understand that this might happen if the bind variables used in the sql queries are db specific keywords or column names. But considering sqls are built based on flexi queries by the hybris platform, we were not able understand WHY IS SYNC FAILING DUE TO SQLSyntaxErrorException.

Any ideas on troubleshooting this ?

Note: I have the transformed SQL with me, which I am not posting owing to its size. I can definitely share this if it helps in analysis.

Please find the error stack-trace below.

 ERROR [Thread-67] [SyncSchedulerCallableBase] Exception ORA-01745: invalid host/bind variable name
  occurred during executing query
 (SELECT {p:pk} FROM {CMSItem AS p} WHERE {p:catalogVersion}=?tgtVer AND NOT EXISTS ( {{SELECT {ts:pk} FROM {ItemSyncTimestamp AS ts} WHERE {ts:syncJob}=0 AND {ts:targetVersion}=?tgtVer AND {ts:sourceVersion}=?srcVer AND {ts:targetItem}={p:pk} }} ) AND NOT EXISTS( {{  SELECT {pp:pk} FROM {CMSItem as pp LEFT JOIN ItemSyncTimestamp AS ts2       ON {ts2:sourceItem}={pp:pk} AND {ts2:syncJob}=0 AND {ts2:targetVersion}=?tgtVer AND {ts2:sourceVersion}=?srcVer }       WHERE {pp:uid}={p:uid} AND {pp:catalogVersion}=?srcVer AND {ts2:pk} IS NULL }} ) ORDER BY {p:creationtime} ASC, {p:pk} ASC) with arguments (srcVer,tgtVer),
 values(th_content/Staged(8796093252185),th_content/Online(8796093284953))
 
 de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.handleError(JDBCQuery.java:123) ~[catalogserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:71) ~[catalogserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.callImpl(SyncSchedulerCallableBase.java:117) [catalogserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:93) [catalogserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:1) [catalogserver.jar:?]
         at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_201]
         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_201]
         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_201]
         at de.hybris.platform.core.TenantAwareThreadFactory$1.internalRun(TenantAwareThreadFactory.java:159) [coreserver.jar:?]
         at de.hybris.platform.core.threadregistry.RegistrableThread.run(RegistrableThread.java:135) [coreserver.jar:?]
 Caused by: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name
 
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
         at de.hybris.platform.jdbcwrapper.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:195) ~[coreserver.jar:?]
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:67) ~[catalogserver.jar:?]
         ... 8 more
 ERROR [00017MZN::de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob] (00017MZN) [Job] Caught throwable de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 [HY--1]
 de.hybris.platform.jalo.JaloSystemException: de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 [HY--1][HY--1]
         at de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob.configureFullVersionSync(CatalogVersionSyncJob.java:500)
         at de.hybris.platform.catalog.hmc.SynchronizeCatalogVersionWizard$FullSyncConfigurator.configureCronjob(SynchronizeCatalogVersionWizard.java:864)
         at de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob.performCronJob(CatalogVersionSyncJob.java:816)
         at de.hybris.platform.cronjob.jalo.Job.execute(Job.java:1390)
         at de.hybris.platform.cronjob.jalo.Job.performImpl(Job.java:814)
         at de.hybris.platform.cronjob.jalo.Job.access$1(Job.java:767)
         at de.hybris.platform.cronjob.jalo.Job$JobRunable.run(Job.java:686)
         at de.hybris.platform.util.threadpool.PoolableThread.internalRun(PoolableThread.java:208)
         at de.hybris.platform.core.threadregistry.RegistrableThread.run(RegistrableThread.java:135)
 Caused by: java.util.concurrent.ExecutionException: de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 [HY--1]
         at java.util.concurrent.FutureTask.report(FutureTask.java:122)
         at java.util.concurrent.FutureTask.get(FutureTask.java:192)
         at de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob.scheduleRemovalOfMissingItems(CatalogVersionSyncJob.java:704)
         at de.hybris.platform.catalog.jalo.synchronization.CatalogVersionSyncJob.configureFullVersionSync(CatalogVersionSyncJob.java:462)
         ... 8 more
 Caused by: de.hybris.platform.jalo.JaloSystemException: ORA-01745: invalid host/bind variable name
 [HY--1]
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.handleError(JDBCQuery.java:123)
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:71)
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.callImpl(SyncSchedulerCallableBase.java:117)
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:93)
         at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:1)
         at java.util.concurrent.FutureTask.run(FutureTask.java:266)
         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
         at de.hybris.platform.core.TenantAwareThreadFactory$1.internalRun(TenantAwareThreadFactory.java:159)
         ... 1 more
 Caused by: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name
 
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
         at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
         at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
         at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
         at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
         at de.hybris.platform.jdbcwrapper.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:195)
         at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:67)
         ... 8 more
  
Add a comment
10|10000 characters needed characters exceeded

  • There is no problem with your query as per the Flexible Search Query specification. In order to analyze further, I will need the generated SQL. Please put it in a text file and attach it with the question.

    The purpose for which I need the generated SQL is to check the value of the bind variables and ensure that they are not Oracle keywords or column names in the database.

    Apart from this, I also recommend trying it with a different database (e.g. the default HSQLDB) and if it works, try upgrading/downgrading your Oracle database driver.

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Feb 14, 2019 at 01:02 PM

    It should be due to a malformed SQL statement, where substitution is failing. Can you match the brackets, substitutes and their values ??

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.