Getting ORA-00904: invalid identifier error while running generateddl.sh
Hi All - Can you guys please help me resolve the error which I am getting this while executing the below command for 7.1.9: (FYI--same settings in setupDatabase properties file worked well for older 7.1.4 system but we used only basic command :- generateddl.sh --action upgrade without any dbSchema parameters)
>generateddl.sh --action upgrade --dbSchema MY_PEGA_RULES --dbDataSchema MY_PEGA_DATA --outputDirectory /xxx/bbb..
Below is the exception details
com.pega.pegarules.data.ddl.SchemaGenerator main
SEVERE: Error generating ddl for oracledate, MY_PEGA_RULES
ORA-00904: : invalid identifier
java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
Hi All - Can you guys please help me resolve the error which I am getting this while executing the below command for 7.1.9: (FYI--same settings in setupDatabase properties file worked well for older 7.1.4 system but we used only basic command :- generateddl.sh --action upgrade without any dbSchema parameters)
>generateddl.sh --action upgrade --dbSchema MY_PEGA_RULES --dbDataSchema MY_PEGA_DATA --outputDirectory /xxx/bbb..
Below is the exception details
com.pega.pegarules.data.ddl.SchemaGenerator main
SEVERE: Error generating ddl for oracledate, MY_PEGA_RULES
ORA-00904: : invalid identifier
java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3593)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3637)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
at com.pega.pegarules.data.ddl.SchemaManager.readObjectBodies(Unknown Source)
at com.pega.pegarules.data.ddl.SchemaManager.readViews(Unknown Source)
at com.pega.pegarules.data.ddl.SchemaManager.<init>(Unknown Source)
at com.pega.pegarules.data.ddl.SchemaManager.<init>(Unknown Source)
at com.pega.pegarules.data.ddl.SchemaGenerator.saveCurrentSchemaState(SchemaGenerator.java:547)
at com.pega.pegarules.data.ddl.SchemaGenerator.main(SchemaGenerator.java:1638)
Accepted Solution
Pegasystems Inc.
US
From looking at the code and the error message this looks like the database user that is running the script has not been granted the select_catalog_role. Besides all of the privileges to access everything in both schemes the user also needs privileges to analyze any dictionary and needs to be granted the select_catalog_role.
Pegasystems Inc.
US
I am assuming that the database information is specified in the setupDatabase.properties file. Does the database user that you are running this script with have permissions to select on everything in the MY_PEGA_RULES and MY_PEGA_DATA schemas?
JPMC
US
Thank you Celeste Dufresne!! I checked with DBAs and the DB ID which we are using is having required roles and privileges and still we are getting same error. We tried the simpler format of command to run the script i.e. "generateddl.sh --action upgrade" but this also didn't work and gave same error. we may have to open SR as it is kind of road block for us. please suggest.
Pegasystems Inc.
US
If you post the full log from when you run it would help to figure out what the issue is. Also confirm with your DBA that the user running the script has been granted the SELECT_CATALOG_ROLE role.
Accepted Solution
Pegasystems Inc.
US
From looking at the code and the error message this looks like the database user that is running the script has not been granted the select_catalog_role. Besides all of the privileges to access everything in both schemes the user also needs privileges to analyze any dictionary and needs to be granted the select_catalog_role.
JPMC
US
Thank you Celeste Dufresne for your response! Let me check with DBAs about this role and ability to analyze dictionary for this user. Since it was show-stopper issue for us I had opened a SR-A16912. I have provided all the necessary information in this SR.
Thank you Celeste Dufresne for your response! Let me check with DBAs about this role and ability to analyze dictionary for this user. Since it was show-stopper issue for us I had opened a SR-A16912. I have provided all the necessary information in this SR. Please let me know if you could find anything else regarding this issue.
Pegasystems Inc.
US
Upon reviewing the corresponding SR, we see that it has been resolved. SA-19424 was created as a result of the investigation. Please reference that if you have the same question.