[Building Sakai] Be careful about using /* */ in Oracle (Re: SAK-15801: Commenting syntax for conversion scripts)

Mon Mar 30 13:31:12 PDT 2009

There might be things we need to careful about using /* */ comments.
1. The document part (the contents in between /* */) is not shown in the 
log file.
2. Putting the /* */ at the beginning of a line after an SQL execution, 
a ";' at the end of previous line, for example, will lead the previous 
text to be executed twice because Oracle treat the "/" in sqlplus as "do 
it again". In that case, the second execution may give an error if 
previous execution is a DDL.
3. If the previous is a DML, an update or insert, for examples, that 
will lead the DML to be executed twice. That could lead to duplicated 
rows for non-key inserts, and for long updates, it will waste a lot og time.
4. The error messages for the DDL executions could be misleading if you 
are searching ORA- errors.

I think we need to be careful and do more tests before making decision. 
Because Oracle does not show the comments after -- or /* */, what I did 
before were to "sqlprmpt" to add it to key steps I am interested in 
looking, that will show the text after it.

The below is a test I designed to raise the awareness using /* */. The 
SQL and sql logs are shown below.

Hope we understand the comments better.

Thanks,
Drew Zhu
Oracle DBA
MCOP
University of Michigan

Create a table called test_date first with any columns.

<test_comments.sql>

/* This is a test*/
alter table test_date drop column text_1;
/* test 1*/
alter table test_date add text_1 varchar2(100);

/*finish testing*/
alter table test_date drop column id;
alter table test_date add id number;

/*test insert*/
insert into test_date values (sysdate,'test4',4);
/*test update*/
update test_date set id=10 where id=4;
/*update is done*/
commit;
/*All done */

</test_comments.sql>

<test_comments.log>

SQL> @test_comments.sql

Table altered.

alter table test_date add text_1 varchar2(100)
*
ERROR at line 1:
ORA-01430: column being added already exists in table

Table altered.

alter table test_date add id number
*
ERROR at line 1:
ORA-01430: column being added already exists in table

1 row created.

1 row created.

2 rows updated.

0 rows updated.

Commit complete.

SQL> spool off

</test_comments.log>

Stephen Swinsburg wrote:
> +1
>
> ...although I don't think I get a vote ;)
>
>
>
> ---
> Steve Swinsburg
> Portal Systems Developer
> Centre for e-Science
> Lancaster University
> Lancaster
> LA1 4YT
>
> email: s.swinsburg at lancaster.ac.uk <mailto:s.swinsburg at lancaster.ac.uk>
> phone: +44 (0) 1524 594870
>
> On 30/03/2009, at 9:13 PM, David Haines wrote:
>
>> +1
>>
>> David Haines
>> CTools Developer
>> Digital Media Commons
>> University of Michigan
>> dlhaines at umich.edu <mailto:dlhaines at umich.edu>
>>
>>
>>
>> On Mar 30, 2009, at 4:05 PM, Anthony Whyte wrote:
>>
>>> +1
>>>
>>> Anth
>>>
>>> On Mar 30, 2009, at 3:12 PM, May, Megan Marie wrote:
>>>
>>>> Hi everyone,
>>>>
>>>> It’s been raised that the DB upgrade scripts in 
>>>> /reference/conversion contain incorrect comment syntax that can 
>>>> break the script (ie manual intervention/resolution is necessary).
>>>>
>>>> The offending lines are those like:
>>>>
>>>> ----------------
>>>>
>>>> This is invalid for MySQL which requires a space, whitespace or 
>>>> control character after the first two dashes.
>>>>
>>>> ie
>>>>
>>>> -- this is a comment
>>>>
>>>> --this is an invalid comment
>>>>
>>>> ------------------ is just a bunch of dashes that will break
>>>>
>>>>
>>>>
>>>> MySQL syntax notation: 
>>>> http://dev.mysql.com/doc/refman/5.1/en/comments.html
>>>>
>>>>
>>>>
>>>> Note that the interpretation of the standard is different in MySQL 
>>>> and Oracle.
>>>>
>>>>
>>>>
>>>> Oracle DOES NOT support the # character and will execute anything 
>>>> inside it.
>>>>
>>>> Oracle DOES support ----------------- as a comment line
>>>>
>>>>
>>>>
>>>> The only consistent comment syntax between the two is:
>>>>
>>>>
>>>>
>>>> /* this is a comment */
>>>>
>>>>
>>>>
>>>> This can also be used as multiline comments:
>>>>
>>>>
>>>>
>>>> /* this is
>>>>
>>>> a
>>>>
>>>> multiline
>>>>
>>>> comment
>>>>
>>>> */
>>>>
>>>>
>>>>
>>>> Steve Swinsburg has proposed that all upgrade scripts from the 2.4 
>>>> series onwards be updated to this cross vendor syntax, /* */ to 
>>>> save possible upgrade headaches. This proposal was well received at 
>>>> the last QA / Release WG meeting. Before making the plunge, I’d 
>>>> like to gather wider support and or comments for the practice.
>>>>
>>>> This JIRA tracking this proposal is 
>>>> http://bugs.sakaiproject.org/jira/browse/SAK-15801
>>>>
>>>>
>>>>
>>>> +1, -1, 0?
>>>>
>>>>
>>>>
>>>> Megan
>>>>
>>>> _______________________________________________
>>>> sakai-dev mailing list
>>>> sakai-dev at collab.sakaiproject.org 
>>>> <mailto:sakai-dev at collab.sakaiproject.org>
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>>
>>>> TO UNSUBSCRIBE: send email to 
>>>> sakai-dev-unsubscribe at collab.sakaiproject.org 
>>>> <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a 
>>>> subject of "unsubscribe"
>>>
>>> _______________________________________________
>>> sakai-dev mailing list
>>> sakai-dev at collab.sakaiproject.org 
>>> <mailto:sakai-dev at collab.sakaiproject.org>
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>
>>> TO UNSUBSCRIBE: send email to 
>>> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of 
>>> "unsubscribe"
>>
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org 
>> <mailto:sakai-dev at collab.sakaiproject.org>
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>
>> TO UNSUBSCRIBE: send email to 
>> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of 
>> "unsubscribe"
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"