Hi!
I was trying to use the HQL update, but apparently, it's trying to create a temporary table, but it fails 'cause i have no privileges. My DBA isn't very happy about givin' me those privileges.
Can that generation be turned off?
Code:
String hqlUpdate = "update "+
"FCCLicense l " +
"set l.deletedFlag = 'Y' " +
"where l.callSign = :callsign ";
try
{
int updatedEntities = session .createQuery( hqlUpdate )
.setString( "callsign", callSign )
.executeUpdate();
System.err.println("entities: "+ updatedEntities);
}
catch (HibernateException e)
{
logger.debug("FCCLicenseDAO : selectFCCLicense : Caught hibernate exception ", e);
throw new DatabaseException(e);
}
and the log...
Code:
10:46:52,156 DEBUG SessionFactoryObjectFactory:39 - initializing class SessionFactoryObjectFactory
10:46:52,171 DEBUG SessionFactoryObjectFactory:76 - registered: 836e8dba0a3c99c0010a3c99c8fc0000 (unnamed)
10:46:52,171 INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
10:46:52,171 DEBUG SessionFactoryImpl:292 - instantiated session factory
10:46:52,171 INFO SessionFactoryImpl:353 - Checking 0 named HQL queries
10:46:52,171 INFO SessionFactoryImpl:373 - Checking 0 named SQL queries
10:46:52,250 DEBUG SessionImpl:272 - opened session at timestamp: 4683685937852416
10:46:52,265 DEBUG JDBCContext:115 - opening user JDBC connection, application must close it
10:46:52,265 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
10:46:52,265 DEBUG DriverManagerConnectionProvider:99 - using pooled JDBC connection, pool size: 0
10:46:52,406 DEBUG JDBCTransaction:54 - begin
10:46:52,406 DEBUG ConnectionManager:313 - opening JDBC connection
10:46:52,406 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 1
10:46:52,406 DEBUG DriverManagerConnectionProvider:109 - opening new JDBC connection
10:46:54,906 DEBUG DriverManagerConnectionProvider:115 - created connection to: jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = nbcddbs1.nbc.com)(PORT =
15811)) ) (CONNECT_DATA = (SERVICE_NAME = d041.nbc.com)) ), Isolation Level: 2
10:46:54,906 DEBUG JDBCTransaction:59 - current autocommit status: false
10:46:54,906 DEBUG JDBCContext:202 - after transaction begin
10:46:54,921 DEBUG QueryPlanCache:69 - unable to locate HQL query plan in cache; generating (update FCCLicense l set l.deletedFlag = 'Y' where l.callSign = :callsign )
10:46:55,015 DEBUG QueryTranslatorImpl:236 - parse() - HQL: update com.ge.nbc.fcclicense.bus.license.domain.FCCLicense l set l.deletedFlag = 'Y' where l.callSign = :callsign
10:46:55,046 DEBUG AST:252 - --- HQL AST ---
\-[UPDATE] 'update'
+-[FROM] 'FROM'
| \-[RANGE] 'RANGE'
| +-[DOT] '.'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[DOT] '.'
| | | | | | | | +-[IDENT] 'com'
| | | | | | | | \-[IDENT] 'ge'
| | | | | | | \-[IDENT] 'nbc'
| | | | | | \-[IDENT] 'fcclicense'
| | | | | \-[IDENT] 'bus'
| | | | \-[IDENT] 'license'
| | | \-[IDENT] 'domain'
| | \-[IDENT] 'FCCLicense'
| \-[ALIAS] 'l'
+-[SET] 'set'
| \-[EQ] '='
| +-[DOT] '.'
| | +-[IDENT] 'l'
| | \-[IDENT] 'deletedFlag'
| \-[QUOTED_STRING] ''Y''
\-[WHERE] 'where'
\-[EQ] '='
+-[DOT] '.'
| +-[IDENT] 'l'
| \-[IDENT] 'callSign'
\-[COLON] ':'
\-[IDENT] 'callsign'
10:46:55,046 DEBUG ErrorCounter:68 - throwQueryException() : no errors
10:46:55,125 DEBUG HqlSqlBaseWalker:111 - update << begin [level=1, statement=update]
10:46:55,171 DEBUG FromElement:104 - FromClause{level=1} : com.ge.nbc.fcclicense.bus.license.domain.FCCLicense (l) -> fcclicense0_
10:46:55,171 DEBUG FromReferenceNode:51 - Resolved : l -> FCC_LICENSE_ID
10:46:55,171 DEBUG DotNode:530 - getDataType() : deletedFlag -> org.hibernate.type.YesNoType@1a19458
10:46:55,171 DEBUG FromReferenceNode:51 - Resolved : l.deletedFlag -> DELETED_FLAG
10:46:55,187 DEBUG FromReferenceNode:51 - Resolved : l -> FCC_LICENSE_ID
10:46:55,187 DEBUG DotNode:530 - getDataType() : callSign -> org.hibernate.type.StringType@11f23e5
10:46:55,187 DEBUG FromReferenceNode:51 - Resolved : l.callSign -> CALL_SIGN
10:46:55,187 DEBUG HqlSqlBaseWalker:117 - update : finishing up [level=1, statement=update]
10:46:55,203 DEBUG HqlSqlBaseWalker:123 - update >> end [level=1, statement=update]
10:46:55,203 DEBUG AST:222 - --- SQL AST ---
\-[UPDATE] UpdateStatement: 'update' querySpaces (FCC_LICENSE)
+-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[l], fromElementByTableAlias=[fcclicense0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'FCC_LICENSE' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=l,role=null,tableName=FCC_LICENSE,tableAlias=fcclicense0_,origin=null,colums={,className=com.ge.nbc.fcclicense.bus.license.domain.FCCLicense}}
+-[SET] SqlNode: 'set'
| \-[EQ] BinaryLogicOperatorNode: '='
| +-[DOT] DotNode: 'DELETED_FLAG' {propertyName=deletedFlag,dereferenceType=4,propertyPath=deletedFlag,path=l.deletedFlag,tableAlias=fcclicense0_,className=com.ge.nbc.fcclicense.bus.license.domain.FCCLicense,classAlias=l}
| | +-[ALIAS_REF] IdentNode: 'FCC_LICENSE_ID' {alias=l, className=com.ge.nbc.fcclicense.bus.license.domain.FCCLicense, tableAlias=fcclicense0_}
| | \-[IDENT] IdentNode: 'deletedFlag' {originalText=deletedFlag}
| \-[QUOTED_STRING] LiteralNode: ''Y''
\-[WHERE] SqlNode: 'where'
\-[EQ] BinaryLogicOperatorNode: '='
+-[DOT] DotNode: 'CALL_SIGN' {propertyName=callSign,dereferenceType=4,propertyPath=callSign,path=l.callSign,tableAlias=fcclicense0_,className=com.ge.nbc.fcclicense.bus.license.domain.FCCLicense,classAlias=l}
| +-[ALIAS_REF] IdentNode: 'FCC_LICENSE_ID' {alias=l, className=com.ge.nbc.fcclicense.bus.license.domain.FCCLicense, tableAlias=fcclicense0_}
| \-[IDENT] IdentNode: 'callSign' {originalText=callSign}
\-[NAMED_PARAM] ParameterNode: '?' {name=callsign, expectedType=org.hibernate.type.StringType@11f23e5}
10:46:55,203 DEBUG ErrorCounter:68 - throwQueryException() : no errors
10:46:55,234 DEBUG MultiTableUpdateExecutor:51 - Generated ID-INSERT-SELECT SQL (multi-table update) : insert into HT_FCC_LICENSE select bulk_target.FCC_LICENSE_ID as FCC_LICENSE_ID from FCC_LICENSE bulk_target where CALL_SIGN=?
10:46:55,250 DEBUG HQLQueryPlan:212 - HQL param location recognition took 0 mills (update FCCLicense l set l.deletedFlag = 'Y' where l.callSign = :callsign )
10:46:55,375 DEBUG QueryPlanCache:75 - located HQL query plan in cache (update FCCLicense l set l.deletedFlag = 'Y' where l.callSign = :callsign )
10:46:55,375 DEBUG HQLQueryPlan:194 - executeUpdate: update FCCLicense l set l.deletedFlag = 'Y' where l.callSign = :callsign
10:46:55,390 DEBUG QueryParameters:262 - named parameters: {callsign=HX91}
10:46:55,812 DEBUG MultiTableUpdateExecutor:115 - unable to create temporary id table [ORA-01031: insufficient privileges
]
10:46:55,921 DEBUG AbstractBatcher:311 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
10:46:55,921 DEBUG SQL:346 - insert into HT_FCC_LICENSE select bulk_target.FCC_LICENSE_ID as FCC_LICENSE_ID from FCC_LICENSE bulk_target where CALL_SIGN=?
Hibernate: insert into HT_FCC_LICENSE select bulk_target.FCC_LICENSE_ID as FCC_LICENSE_ID from FCC_LICENSE bulk_target where CALL_SIGN=?
10:46:55,921 DEBUG AbstractBatcher:424 - preparing statement
10:46:55,953 DEBUG StringType:79 - binding 'HX91' to parameter: 1
10:46:56,328 DEBUG AbstractBatcher:319 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
10:46:56,328 DEBUG AbstractBatcher:470 - closing statement
10:46:56,453 DEBUG JDBCExceptionReporter:63 - could not insert/select ids for bulk update [insert into HT_FCC_LICENSE select bulk_target.FCC_LICENSE_ID as FCC_LICENSE_ID from FCC_LICENSE bulk_target where CALL_SIGN=?]
java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:117)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:326)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:202)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1111)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at com.ge.nbc.fcclicense.bus.license.dao.FCCLicenseDAO.deleteFCCLicense(FCCLicenseDAO.java:215)
at com.ge.nbc.license.test.fcclicense.TestFCCLicense.testDelete(TestFCCLicense.java:77)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
10:46:56,453 WARN JDBCExceptionReporter:71 - SQL Error: 942, SQLState: 42000
10:46:56,453 ERROR JDBCExceptionReporter:72 - ORA-00942: table or view does not exist
10:46:56,453 DEBUG AbstractBatcher:311 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
10:46:56,453 DEBUG SQL:346 - drop table HT_FCC_LICENSE
Hibernate: drop table HT_FCC_LICENSE
10:46:56,453 DEBUG AbstractBatcher:424 - preparing statement
10:46:56,812 WARN MultiTableUpdateExecutor:138 - unable to drop temporary id table after use
java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at org.hibernate.hql.ast.exec.AbstractStatementExecutor.dropTemporaryTableIfNecessary(AbstractStatementExecutor.java:135)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:169)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:326)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:202)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1111)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at com.ge.nbc.fcclicense.bus.license.dao.FCCLicenseDAO.deleteFCCLicense(FCCLicenseDAO.java:215)
at com.ge.nbc.license.test.fcclicense.TestFCCLicense.testDelete(TestFCCLicense.java:77)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
10:46:56,812 DEBUG AbstractBatcher:319 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
10:46:56,812 DEBUG AbstractBatcher:470 - closing statement
10:46:56,937 DEBUG FCCLicenseDAO:222 - FCCLicenseDAO : selectFCCLicense : Caught hibernate exception
org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:126)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:326)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:202)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1111)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at com.ge.nbc.fcclicense.bus.license.dao.FCCLicenseDAO.deleteFCCLicense(FCCLicenseDAO.java:215)
at com.ge.nbc.license.test.fcclicense.TestFCCLicense.testDelete(TestFCCLicense.java:77)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:117)
... 21 more
com.ge.nbc.common.exception.DatabaseException: could not insert/select ids for bulk update
at com.ge.nbc.fcclicense.bus.license.dao.FCCLicenseDAO.deleteFCCLicense(FCCLicenseDAO.java:223)
at com.ge.nbc.license.test.fcclicense.TestFCCLicense.testDelete(TestFCCLicense.java:77)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
10:46:56,953 DEBUG SessionImpl:296 - closing session
10:46:56,953 DEBUG ConnectionManager:272 - performing cleanup
10:46:56,953 DEBUG ConnectionManager:333 - closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
10:46:56,953 DEBUG DriverManagerConnectionProvider:129 - returning connection to pool, pool size: 1