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.
Published February 5, 2018 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.