Oracle table creation issue

Go To StackoverFlow.com

2

I use activity plugin in my grails application. When I try to run my application I getting this error. Tables are not created. I don't understand why.

| Error 2012-04-20 18:30:24,746 [pool-6-thread-1] ERROR interceptor.CommandContext  - Error while closing command context
Message: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may involve org.activiti.engine.impl.persistence.entity.PropertyEntity.selectProperty-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    Line | Method
->>    8 | wrapException                             in org.apache.ibatis.exceptions.ExceptionFactory
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|     77 | selectList                                in org.apache.ibatis.session.defaults.DefaultSqlSession
|     69 | selectList . . . . . . . . . . . . . . .  in     ''
|     40 | selectOne                                 in     ''
|    217 | selectById . . . . . . . . . . . . . . .  in org.activiti.engine.impl.db.DbSqlSession
|    629 | dbSchemaUpdate                            in     ''
|    885 | performSchemaOperationsProcessEngineBuild in     ''
|     25 | execute                                   in org.activiti.engine.impl.SchemaOperationsProcessEngineBuild
|     24 | execute . . . . . . . . . . . . . . . . . in org.activiti.engine.impl.interceptor.CommandExecutorImpl
|     42 | execute                                   in org.activiti.engine.impl.interceptor.CommandContextInterceptor
|     40 | execute . . . . . . . . . . . . . . . . . in org.activiti.spring.SpringTransactionInterceptor
|     33 | execute                                   in org.activiti.engine.impl.interceptor.LogInterceptor
|     77 | <init> . . . . . . . . . . . . . . . . .  in org.activiti.engine.impl.ProcessEngineImpl
|    271 | buildProcessEngine                        in org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl
|     60 | buildProcessEngine . . . . . . . . . . .  in org.activiti.spring.SpringProcessEngineConfiguration
|     56 | getObject                                 in org.activiti.spring.ProcessEngineFactoryBean
|     32 | getObject . . . . . . . . . . . . . . . . in     ''
|    334 | innerRun                                  in java.util.concurrent.FutureTask$Sync
|    166 | run . . . . . . . . . . . . . . . . . . . in java.util.concurrent.FutureTask
|   1110 | runWorker                                 in java.util.concurrent.ThreadPoolExecutor
|    603 | run . . . . . . . . . . . . . . . . . . . in java.util.concurrent.ThreadPoolExecutor$Worker
^    722 | run                                       in java.lang.Thread

Caused by SQLSyntaxErrorException: ORA-00942: table or view does not exist

->>   91 | newSQLException                           in oracle.jdbc.driver.SQLStateMapping
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    112 | newSQLException                           in oracle.jdbc.driver.DatabaseError
|    173 | throwSqlException . . . . . . . . . . . . in     ''
|    455 | processError                              in oracle.jdbc.driver.T4CTTIoer
|    413 | processError . . . . . . . . . . . . . .  in     ''
|   1030 | receive                                   in oracle.jdbc.driver.T4C8Oall
|    194 | doOall8 . . . . . . . . . . . . . . . . . in oracle.jdbc.driver.T4CPreparedStatement
|    785 | executeForDescribe                        in     ''
|    860 | executeMaybeDescribe . . . . . . . . . .  in     ''
|   1186 | doExecuteWithTimeout                      in oracle.jdbc.driver.OracleStatement
|   3381 | executeInternal . . . . . . . . . . . . . in oracle.jdbc.driver.OraclePreparedStatement
|   3482 | execute                                   in     ''
|   1373 | execute . . . . . . . . . . . . . . . . . in oracle.jdbc.driver.OraclePreparedStatementWrapper
|    172 | execute                                   in org.apache.commons.dbcp.DelegatingPreparedStatement
|     39 | query . . . . . . . . . . . . . . . . . . in org.apache.ibatis.executor.statement.PreparedStatementHandler
|     55 | query                                     in org.apache.ibatis.executor.statement.RoutingStatementHandler
|     41 | doQuery . . . . . . . . . . . . . . . . . in org.apache.ibatis.executor.SimpleExecutor
|    216 | queryFromDatabase                         in org.apache.ibatis.executor.BaseExecutor
|     95 | query . . . . . . . . . . . . . . . . . . in     ''
|     72 | query                                     in org.apache.ibatis.executor.CachingExecutor
|     75 | selectList . . . . . . . . . . . . . . .  in org.apache.ibatis.session.defaults.DefaultSqlSession
|     69 | selectList                                in     ''
|     40 | selectOne . . . . . . . . . . . . . . . . in     ''
|    217 | selectById                                in org.activiti.engine.impl.db.DbSqlSession
|    629 | dbSchemaUpdate . . . . . . . . . . . . .  in     ''
|    885 | performSchemaOperationsProcessEngineBuild in     ''
|     25 | execute . . . . . . . . . . . . . . . . . in org.activiti.engine.impl.SchemaOperationsProcessEngineBuild
|     24 | execute                                   in org.activiti.engine.impl.interceptor.CommandExecutorImpl
|     42 | execute . . . . . . . . . . . . . . . . . in org.activiti.engine.impl.interceptor.CommandContextInterceptor
|     40 | execute                                   in org.activiti.spring.SpringTransactionInterceptor
|     33 | execute . . . . . . . . . . . . . . . . . in org.activiti.engine.impl.interceptor.LogInterceptor
|     77 | <init>                                    in org.activiti.engine.impl.ProcessEngineImpl
|    271 | buildProcessEngine . . . . . . . . . . .  in org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl
|     60 | buildProcessEngine                        in org.activiti.spring.SpringProcessEngineConfiguration
|     56 | getObject . . . . . . . . . . . . . . . . in org.activiti.spring.ProcessEngineFactoryBean
|     32 | getObject                                 in     ''
|    334 | innerRun . . . . . . . . . . . . . . . .  in java.util.concurrent.FutureTask$Sync
|    166 | run                                       in java.util.concurrent.FutureTask
|   1110 | runWorker . . . . . . . . . . . . . . . . in java.util.concurrent.ThreadPoolExecutor
|    603 | run                                       in java.util.concurrent.ThreadPoolExecutor$Worker
^    722 | run . . . . . . . . . . . . . . . . . . . in java.lang.Thread
| Error 2012-04-20 18:30:24,927 [pool-6-thread-1] ERROR context.GrailsContextLoader  - Error executing bootstraps: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may involve org.activiti.engine.impl.persistence.entity.PropertyEntity.selectProperty-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Message: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may involve org.activiti.engine.impl.persistence.entity.PropertyEntity.selectProperty-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    Line | Method
->>  334 | innerRun  in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    166 | run       in java.util.concurrent.FutureTask
|   1110 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    603 | run       in java.util.concurrent.ThreadPoolExecutor$Worker
^    722 | run . . . in java.lang.Thread

Caused by PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may involve org.activiti.engine.impl.persistence.entity.PropertyEntity.selectProperty-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

->>    8 | wrapException in org.apache.ibatis.exceptions.ExceptionFactory
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|     77 | selectList in org.apache.ibatis.session.defaults.DefaultSqlSession
|     69 | selectList in     ''
|     40 | selectOne in     ''
|    217 | selectById in org.activiti.engine.impl.db.DbSqlSession
|    629 | dbSchemaUpdate in     ''
|    885 | performSchemaOperationsProcessEngineBuild in     ''
|     25 | execute   in org.activiti.engine.impl.SchemaOperationsProcessEngineBuild
|     24 | execute . in org.activiti.engine.impl.interceptor.CommandExecutorImpl
|     42 | execute   in org.activiti.engine.impl.interceptor.CommandContextInterceptor
|     40 | execute . in org.activiti.spring.SpringTransactionInterceptor
|     33 | execute   in org.activiti.engine.impl.interceptor.LogInterceptor
|     77 | <init> .  in org.activiti.engine.impl.ProcessEngineImpl
|    271 | buildProcessEngine in org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl
|     60 | buildProcessEngine in org.activiti.spring.SpringProcessEngineConfiguration
|     56 | getObject in org.activiti.spring.ProcessEngineFactoryBean
|     32 | getObject in     ''
|    334 | innerRun  in java.util.concurrent.FutureTask$Sync
|    166 | run . . . in java.util.concurrent.FutureTask
|   1110 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    603 | run . . . in java.util.concurrent.ThreadPoolExecutor$Worker
^    722 | run       in java.lang.Thread

Caused by SQLSyntaxErrorException: ORA-00942: table or view does not exist

->>   91 | newSQLException in oracle.jdbc.driver.SQLStateMapping
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    112 | newSQLException in oracle.jdbc.driver.DatabaseError
|    173 | throwSqlException in     ''
|    455 | processError in oracle.jdbc.driver.T4CTTIoer
|    413 | processError in     ''
|   1030 | receive   in oracle.jdbc.driver.T4C8Oall
|    194 | doOall8 . in oracle.jdbc.driver.T4CPreparedStatement
|    785 | executeForDescribe in     ''
|    860 | executeMaybeDescribe in     ''
|   1186 | doExecuteWithTimeout in oracle.jdbc.driver.OracleStatement
|   3381 | executeInternal in oracle.jdbc.driver.OraclePreparedStatement
|   3482 | execute   in     ''
|   1373 | execute . in oracle.jdbc.driver.OraclePreparedStatementWrapper
|    172 | execute   in org.apache.commons.dbcp.DelegatingPreparedStatement
|     39 | query . . in org.apache.ibatis.executor.statement.PreparedStatementHandler
|     55 | query     in org.apache.ibatis.executor.statement.RoutingStatementHandler
|     41 | doQuery . in org.apache.ibatis.executor.SimpleExecutor
|    216 | queryFromDatabase in org.apache.ibatis.executor.BaseExecutor
|     95 | query . . in     ''
|     72 | query     in org.apache.ibatis.executor.CachingExecutor
|     75 | selectList in org.apache.ibatis.session.defaults.DefaultSqlSession
|     69 | selectList in     ''
|     40 | selectOne in     ''
|    217 | selectById in org.activiti.engine.impl.db.DbSqlSession
|    629 | dbSchemaUpdate in     ''
|    885 | performSchemaOperationsProcessEngineBuild in     ''
|     25 | execute . in org.activiti.engine.impl.SchemaOperationsProcessEngineBuild
|     24 | execute   in org.activiti.engine.impl.interceptor.CommandExecutorImpl
|     42 | execute . in org.activiti.engine.impl.interceptor.CommandContextInterceptor
|     40 | execute   in org.activiti.spring.SpringTransactionInterceptor
|     33 | execute . in org.activiti.engine.impl.interceptor.LogInterceptor
|     77 | <init>    in org.activiti.engine.impl.ProcessEngineImpl
|    271 | buildProcessEngine in org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl
|     60 | buildProcessEngine in org.activiti.spring.SpringProcessEngineConfiguration
|     56 | getObject in org.activiti.spring.ProcessEngineFactoryBean
|     32 | getObject in     ''
|    334 | innerRun  in java.util.concurrent.FutureTask$Sync
|    166 | run       in java.util.concurrent.FutureTask
|   1110 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    603 | run       in java.util.concurrent.ThreadPoolExecutor$Worker
^    722 | run . . . in java.lang.Thread
2012-04-20 13:51
by emilan
The problem appears to be that a table or view referred to in the SQL statement either does not exist, or cannot be accessed by the account being used. Since the SQL statement was not included in the post, and we don't know what users are involved, it's not possible to say anything further at this point. If you can edit your post and include more information it might be possible for someone to make a better guess as to what's going on - Bob Jarvis 2012-04-20 14:24


3

3 possibilities come to mind for this error message:

  1. The table/view being referenced actually does not exist.
  2. The table/view being referenced does exist, but you don't have permissions to SELECT from it.
  3. The table/view exists, you have permissions to SELECT from it, but the table/view is in a different schema and is not referenced with a qualified name (e.g., SCHEMA.TABLE_NAME) or is not aliased via a synonym.

Fixes for the issue should be fairly obvious:

  1. Create the table/view
  2. Get permissions to the table/view via the GRANT command
  3. Create a synonym or fully qualify the table/view name in your SELECT statement
2012-04-20 15:43
by DCookie
>
  • The table/view being referenced actually does not exist. But I don't want to create these tables manually, because it must be created automatically.
  • - emilan 2012-04-23 05:21
    So this automatic table creation is something provided by Grails? I know of nothing in Oracle that will do this for you - DCookie 2012-04-23 13:31


    0

    I just ran into the same symptoms in my Grails project.

    I caused the error by adding a new date variable and a default value in a format that didn't exist.

    A date format that worked for me after the object-oriented version failed was:

    static mapping = {
        myVar defaultValue: "'01-JAN-1970'"
    }
    

    You probably have a different error, but the point is that the create table statement fails, probably due to some aspect of database interaction that is a little bit "off the beaten path" and recently added.

    To troubleshoot, I would take the following steps:

    1) Validate that you are running with the DataSource.groovy configuration that you think you are (eg. change your password and ensure that you fail to log in) and that your dbCreate variable is set to "create" or "create-drop" as appropriate. If there is a good reason to use update or validate, then either create the same object in a different env and move or just create the table appropriately. If you aren't sure, use create-drop in the very beginning of a new project to play with the tools, and then never again.

    2) When you have verified that grails should be creating the table as above, then turn on logging to see what is actually happening during restart. To do so, add "logSql = true" and "formatSql" = true to your datasource config at the same level as the username and password. You should see the flawed create statement in your standard out.

    3) If you can't see what is wrong immediately, then run the statement directly in sqlplus to isolate the offending cause. This likely will point to recent changes.

    2013-10-15 06:20
    by sf_jeff
    Ads