Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

ORA-00921 when applying schema SQL on Oracle

SA-49717

Summary



The developers are attempting to upgrade their environment from Pega 7.1.7 to 7.3.1.

In order to make the relevant schema changes the have already configured their setupDatabase.properties file to point to the correct database. And have generated the schema SQL using:


./generateddl.sh --action upgrade

However, when they attempt to apply the SQL, this is producing errors.


Error Messages



SP2-0042: unknown command ")" - rest of line ignored.
SP2-0734: unknown command beginning "where pzcl..." - rest of line ignored.
update PRPC717RULES.pr4_rule_vw V set pzclasstype sure
= (
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command



Steps to Reproduce



1. Configure the setupDatabase.properties file to point to the Pega 7.1.7 instance.
2. Run the script:
%> ./generateddl.sh --action upgrade

3. Log into the sqlplus Oracle command line tool.
4. Run the oracledate.sql file:
SQL> @'/software/pega/PEGA_7_3_1/115631_Pega7.31/schema/generated/oracledate/update/oracledate.sql


Root Cause



In this instance the generated SQL file contains blank lines, such as:

update PRPC717RULES.pr4_rule_vw V set pzclasstype = (
SELECT (CASE WHEN B.pyclassinheritance = 'true' AND B.pyruleresolution = 'true' then 0
WHEN B.pyclassinheritance = 'false' and B.pyruleresolution = 'true' then 1
WHEN B.pyclassinheritance = 'false' and B.pyruleresolution = 'false' then 2
else -1
end)
from PRPC717RULES.pr4_base B, PRPC717RULES.pr4_rule_vw where V.pyclass = B.pyclassname
AND B.pxObjclass = 'Rule-Obj-Class'
AND B.pyClassType = 'Concrete'
AND ROWNUM = 1



)
where pzclasstype is null



When using sqlplus, by default if a blank line is outside of a PL/SQL block then this will get treated as a cancel.

So when it then reaches the line that begins with the right parenthesis symbol ")" this is not recognized as valid beginning to a SQL command, and hence throws an error.

Resolution



Prior to importing the oracledate.sql file the following command line setting can used to process over the blank lines as a continuation of the current SQL statement:

set sqlblanklines on


An enhancement request, FDBK-24201, has also been creating requesting for blank lines to be discarded when generating the oracledate.sql file, unless these are contained within a PL/SQL block.

Suggest Edit

Published February 5, 2018 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us