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



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 file to point to the correct database. And have generated the schema SQL using:

./ --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 file to point to the Pega 7.1.7 instance.
2. Run the script:
%> ./ --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
from PRPC717RULES.pr4_base B, PRPC717RULES.pr4_rule_vw where V.pyclass = B.pyclassname
AND B.pxObjclass = 'Rule-Obj-Class'
AND B.pyClassType = 'Concrete'

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.


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.

Published February 5, 2018 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

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

Did you find this content helpful?

Want to help us improve this content?

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