INMEMORY objects causing error

The script generated for a table with INMEMORY attribute is malformed.

Replicate the issue by running this script to create the table then run Schema Compare 

-- Create Table
CREATE TABLE some_table_name (
  supplier VARCHAR2(20 BYTE) NOT NULL,
  short_text VARCHAR2(1000 BYTE),
  title VARCHAR2(20 BYTE) NOT NULL,
  long_text NCLOB NOT NULL,
  CONSTRAINT supplier_pk1 PRIMARY KEY (supplier)
);
COMMENT ON TABLE some_table_name IS 'Table Description';
COMMENT ON COLUMN some_table_name.supplier IS 'Column 1 Desc';
COMMENT ON COLUMN some_table_name.short_text IS 'Column 2 desc';
COMMENT ON COLUMN some_table_name.title IS 'Column 3 Desc';
COMMENT ON COLUMN some_table_name.long_text IS 'Column 4 desc';

ALTER TABLE SOME_TABLE_NAME NO INMEMORY  (SHORT_TEXT) ;


Tagged:

Answers

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

    What version of Schema Compare for Oracle are you using?

    When the error occurs, are you able to submit an error report using the link provided?  If no, can you please submit the error report and include your email address so I can locate the report.

    Many Thanks
    Eddie


    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • teltestteltest Posts: 12 Bronze 1
    Hi,
    I am using version 5.7.8.97 although a colleague is using version 5.7.17.1842 and is getting the same issue.
    It doesn't produce an error - its just that the script is malformed (and subsequently wont work)
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi,
    Thank you for your reply.

    Using V5.7.23.2524 of Schema Compare for Oracle and comparing two Oracle 12c2 schemas, the table compares successfully for me, as per the screen shot below.  No error is generated.



    I can also generate the deployment script successfully.

    However, when I compare the DDL in my IDE with the CREATE TABLE statement in the comparison results (as per the above screen shot) and deployment script, Schema Compare for Oracle is missing the
    NO INMEMORY ("short_text)

    Checking the comparison options, I cannot identify if I missed an option to enable or disable for the NO INMEMORY keywords to be included.  I believe this to be bug and therefore will submit a bug report.  I will update this forum post when I have further news to update.

    Many Thanks
    Eddie

     
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi,
    After seeking the advice of colleagues, this is actually normal behaviour.

    The comparison engine will ignore certain parameters when the default has been configured.  So when the object is deployed, the default on the target will also be applied.

    So in your example of NO INMEMORY.

    The NO INMEMORY parameter is the default setting.  So it is ignored by the comparison engine.  When the object is deployed to the target, Oracle will apply the default so the column will be NO INMEMORY.

    So, if the table is made INMEMORY in the future, Schema Compare will deploy the exceptions for individual columns at that point, when it actually has any effect on behaviour.

    I hope this answers your question.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • teltestteltest Posts: 12 Bronze 1
    Thank you for your reply.
    I wonder if the issue does not exist in your system because of the version of Schema Compare that you are using or because of the version of the database. We are using Oracle v19c.
    We fixed the issue at our end by re-creating the table without any INMEMORY parameters.
Sign In or Register to comment.