Error when refreshing (Comment has a single quote in it)

Brian.Lieb

So, in my initial attempt to create an SVN repository of one of the schema's I have inherited I get this error:

Parsing failed with message SyntaxError. Unexpected token 's' (Line 52, Col 67) symbol Id.

The offending line is this:

COMMENT ON TABLE mytable IS 'Each COOP's member's QS units are summed by species, then the percentage of the QS pool is calculated using this total.';

So, like you, I can see the error is the single quote in the comment. However, when I extract the DDL using SQL Navigator that single quote gets escaped (e.g. '').

Anyway, this is a large schema that I would really like to get into source control, and I don't necessarily control all of the objects and there may very well be poorly commented columns in this schema.

Is there some option that will escape single quotes, or is my option to edit any comment that causes the problem?

Chris Auckland

Thanks for your post.

Do you happen to know what version of Oracle was used when those comments were originally created? I haven't been able to reproduce the problem as Oracle 11g will always reject my comments if the quotes are not properly escaped.

What happens if you use 'Schema Compare for Oracle' and compare the schema to another (blank) schema? Does it have the same problem, or do the comments get properly escaped?

If they get escaped, you might be able to do a deployment to a blank schema, and then back again to fix up any of the problematic comments in the original schema.

If that doesn't help, I can see if there is anything else we can do. Do you know of any way I can reproduce this in house?

Brian.Lieb

I'm sorry, I don't have any idea what version of oracle these comments were made on.

Anyway, I used Tortoise to get the scripts and the comments field that is causing the problem looks like this:

COMMENT ON TABLE myview IS 'The coop's name and some additional calculated columns.';

Seems obvious why there is a problem.

What is interesting is this:

COMMENT ON COLUMN myview.mycol IS 'Amount of this year''s payment that has covered accrued interest';

Is a comment on a column in the same view. It has the '' so it is fine.

Finally, there is one more piece of information. I am unable to find a comment definition for any view that contains a ' that is properly escaped. However, the comment definitions on the tables that have a ' are all escaped correctly.

COMMENT ON TABLE myTable IS 'Table captures hard copy User''s Comment Form';

I will look into schema comparing to a blank schema, and making the repo from there also, but I thought you might be interested in the info I have found.

Brian.Lieb

Okay. In my frustration, I have made two blank schemas. In the first, I put a table with two columns.

CREATE TABLE mytable
    (col1                           VARCHAR2(5 BYTE),
    col2                           VARCHAR2(5 BYTE))
;

COMMENT ON TABLE mytable IS 'This is mytable''s awesome data.';
COMMENT ON COLUMN mytable.col1 IS 'Column 1''s values are stored here';
COMMENT ON COLUMN mytable.col2 IS 'Column 2';

Then I made this view:

CREATE OR REPLACE VIEW myview (
   col1,
   col2 )
AS
select col1, col2 from mytable;

COMMENT ON TABLE myview IS 'This view shows mytable''s data.';
COMMENT ON COLUMN myview.col1 IS 'Column 1''s value from the view';
COMMENT ON COLUMN myview.col2 IS 'Column 2 value from the view';

That is the exact SQL I ran to create the views and tables. It worked fine.

Then I did a schema compare between the schema with the new table and view against the blank schema. And chose to to a deployment to a script. The script that was generated was this:

--
-- Script generated by Schema Compare for Oracle 3.0.0.790 on 12/10/2013 11:45:49 AM
--
SET DEFINE OFF

CREATE TABLE schema2.mytable (
  col1 VARCHAR2(5 BYTE),
  col2 VARCHAR2(5 BYTE)
);

COMMENT ON TABLE schema2.mytable IS 'This is mytable''s awesome data.';

COMMENT ON COLUMN schema2.mytable.col1 IS 'Column 1''s values are stored here';

COMMENT ON COLUMN schema2.mytable.col2 IS 'Column 2';

CREATE FORCE VIEW schema2.myview (col1,col2) AS
select col1, col2 from mytable;

COMMENT ON TABLE schema2.myview IS 'This view shows mytable's data.';

COMMENT ON COLUMN schema2.myview.col1 IS 'Column 1''s value from the view';

COMMENT ON COLUMN schema2.myview.col2 IS 'Column 2 value from the view';

So, you can see that the comment on the view is wrong! So, as one last check, I ran this:

select dbms_metadata.get_dependent_ddl('COMMENT', 'MYVIEW', 'SCHEMA1') from dual

To see what Oracle returned. The results:

COMMENT ON COLUMN "SCHEMA1"."MYVIEW"."COL1" IS 'Column 1''s value from the view'

COMMENT ON COLUMN "SCHEMA1"."MYVIEW"."COL2" IS 'Column 2 value from the view'

COMMENT ON TABLE "SCHEMA1"."MYVIEW"  IS 'This view shows mytable''s data.'

As you can see, Oracle is returning it correctly as well.

So, I have surmised that either I am missing something really small, or there is a bug in generating comments in Red Gate's schema compare when looking at views.

Chris Auckland

Sorry for the delay. I missed your updates and only just noticed your reply.

I'll look at this now.

Chris Auckland

Hi Brian,

Thanks for the reproduction. This looks like a bug. It seems we're not properly escaping the comments at the object level for Views.

In my previous tests I was only commenting on tables, which seemed to work fine, so thanks for the more detailed steps.

I'll log a bug for this and find out when it might get fixed.

Chris Auckland

The bug tracking code for this issue is OC-644.

Brian.Lieb

I don't want to harp on this too much, as I am a developer and know all too well the pain of being asked, "When will that be fixed?"

However, we would really like to begin using Source Control for Oracle, and the schema is way too large to go through and change all the comments. (Plus, we shouldn't have to.)

So, I guess, you have given me what appears to be a JIRA issue number, but no way to track it, so I am wondering if this fix will be in a release soon or not?