Options

Parsing failed with message SyntaxError. Unexpected token 'PARTITION' (Line 35, Col 1) symbol Id

asollberasollber Posts: 5 New member
in Schema Compare for Oracle
I'm trying to create audit tables with a storage clause as well as a partitioning clause. This is what such a table looks like:

create table MY_SCHEMA.MY_TEST_AUDT (
    TEST_ID                        NUMBER(19),
    -- all sorts of business fields, omitted for clarity
    AUDT_CRT_DTM                   TIMESTAMP DEFAULT SYSTIMESTAMP,
    AUDT_ACTN_CODE                 VARCHAR2(1),
    AUDT_CRT_USR_NM                VARCHAR2(128) DEFAULT USER,
    AUDT_CLIENT_IDENTIFIER         VARCHAR2(256),
    AUDT_CLIENT_INFO               VARCHAR2(256)
)
TABLESPACE MY_TABLESPACE
PCTFREE 0
INITRANS 10
STORAGE (
    INITIAL          64K
    NEXT             1M
    MINEXTENTS       1
    MAXEXTENTS       UNLIMITED
    PCTINCREASE      0
    BUFFER_POOL      DEFAULT
)
COMPRESS FOR OLTP
NOCACHE
PARTITION BY RANGE (AUDT_CRT_DTM)
INTERVAL(interval '1' month)
(
    PARTITION P0 VALUES LESS THAN (date '2018-11-01')
    PCTFREE 0
    INITRANS 10
)
/<br>
The first time I ran it I got this error:
Parsing failed with message SyntaxError. Unexpected token 'K'
Pointing to the storage clause. When I got rid of the storage clause (since I can use the defaults) it started complaining about the partitioning clause and that's where I am not very happy with the software.
Parsing failed with message SyntaxError.
Unexpected token 'PARTITION' (Line 35, Col 1) symbol Id<br>
We're running Schema Compare for Oracle version:4.0.8.420
The options we're setting (wrapped in another script) are:
-behavior=scriptheader
-behavior=defineoff
-ignore=slowdependencies
-ignore=dependentobjects
-ignore=whitespace
-ignore=casedifferences
-ignore=storage
-ignore=doublequotes
-ignore=sequencevalue
-ignore=permissions
-ignore=constraintnames
-ignore=indexnames
-ignore=loggroups
/exclude:Additional

Do I have to change the format to get this to work or is there an option I have to set differently?

Thanks for any help with this
Alain
Tagged:
· Share on Twitter

Best Answer

  • Options
    Eddie DEddie D Posts: 1,780 Rose Gold 5
    edited January 17, 2019 2:26PM Answer ✓
    Hi Alain

    I have a reproduction of the problem, the fact that you are comparing scripts folders helped me identify the cause of the parsing problem.  There are in fact 3 parsing problems, the first two are easily rectified and the third one is little puzzling as it is a valid PL-SQL keyword.

    For some reason the scripts parser encounters a problem.

    System.AggregateException: One or more errors occurred. ---> RedGate.Oracle.Common.ScriptPopulationException: Failed to parse file D:\EDDIE_ScriptsFolder_Target\EDDIE\Tables\MY_TEST_AUDT.sql - Parsing failed with message SyntaxError. Unexpected token 'K' (Line 12, Col 24) symbol Id.


    I was able to resolve the above by changing:  <pre class="CodeBlock"><code>INITIAL&nbsp; &nbsp; &nbsp;64K   to become  <pre class="CodeBlock"><code>INITIAL&nbsp; &nbsp; &nbsp;65536

    System.AggregateException: One or more errors occurred. ---> RedGate.Oracle.Common.ScriptPopulationException: Failed to parse file D:\EDDIE_ScriptsFolder_Target\EDDIE\Tables\MY_TEST_AUDT.sql - Parsing failed with message SyntaxError. Unexpected token 'M' (Line 14, Col 23) symbol Id.


    I was able to resolve the above by changing:

    NEXT&nbsp; &nbsp; &nbsp;1M
      to become    
    NEXT 1048576<br>


    System.AggregateException: One or more errors occurred. ---> RedGate.Oracle.Common.ScriptPopulationException: Failed to parse file D:\EDDIE_ScriptsFolder_Target\EDDIE\Tables\MY_TEST_AUDT.sql - Parsing failed with message SyntaxError. Unexpected token 'PARTITION' (Line 22, Col 1) symbol Id.


    I removed the NOCACHE keyword from the script, which resolved this problem. 


    Although looking at the Oracle help, NOCACHE is a valid keyword and the default value if NOCACHE or CACHE is not specified.  When first creating the table, I noticed that Oracle SQL Developer placed a red squiggle line underneath the keyword and when viewing the DDL for the table using Oracle SQL Developer the keyword is omitted.

    Despite the work around, I have submitted a new bug report whose reference is OC-1026.

    Many Thanks
    Eddie


    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter

Answers

  • Options
    Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi,
    Thank you for your forum post.

    Using V5.2.1.1127 of Schema Compare for Oracle, I am unable to replicate the reported problem on an Oracle 12c2 database, using the DDL you posted.

    The only changes to your script I made were to the schema name and tablespace name.

    Are you comparing two live schemas, or a scripts folder to a live schema?

    Are you using the GUI or the Command Line to perform your comparison?

    A support ticket has been created for you, I will sent to you a private message on how to view the support ticket created for you.

    Would it be possible for you to increase the minimum logging level to verbose and repeat the comparison attempt.  When the reported error occurs, please update the support ticket.

    If using the GUI, please us the information in this help article to set the verbose logging.

    If using the command line, please add the /loglevel:verbose switch to your syntax.

    You will be able to locate the log files in the following folder:
    C:\Users\<user name>\AppData\Local\Red Gate\Logs\Schema Compare for Oracle

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • Options
    asollberasollber Posts: 5 New member
    Hi Eddie

    Thanks for getting back to me.
    The comparison I'm using is the scripts folders (both sides) and I'm using the GUI.

    I attached you the log file.
    Your help is much appreciated.

    Best regards
    Alain
    · Share on Twitter
  • Options
    Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi,
    Thank you for your forum post.

    Using V5.2.1.1127 of Schema Compare for Oracle, I am unable to replicate the reported problem on an Oracle 12c2 database, using the DDL you posted.

    The only changes to your script I made were to the schema name and tablespace name.

    Are you comparing two live schemas, or a scripts folder to a live schema?

    Are you using the GUI or the Command Line to perform your comparison?

    A support ticket has been created for you, I will sent to you a private message on how to view the support ticket created for you.

    Would it be possible for you to increase the minimum logging level to verbose and repeat the comparison attempt.  When the reported error occurs, please update the support ticket.

    If using the GUI, please us the information in this help article to set the verbose logging.

    If using the command line, please add the /loglevel:verbose switch to your syntax.

    You will be able to locate the log files in the following folder:
    C:\Users\<user name>\AppData\Local\Red Gate\Logs\Schema 
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.