[Building Sakai] Be careful about using /* */ in Oracle (Re: SAK-15801: Commenting syntax for conversion scripts)
Drew Zhu
qszhu at umich.edu
Mon Mar 30 13:47:43 PDT 2009
Sorry, in where I use, it should be "prompt" Not 'sqlprompt" in the SQL
file.
Thanks,
Drew Zhu
Oracle DBA
MCOP
University of Michigan
Drew Zhu wrote:
> 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.
>
>
> 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.
>
>
> 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.
>
>
> 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"
>>
>
> _______________________________________________
> 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"
>
>
>
More information about the sakai-dev
mailing list