This discussion is archived
6 Replies Latest reply: Jul 5, 2010 5:23 AM by ora_1978 RSS

how to put Comments in SQL

658523 Newbie
Currently Being Moderated
Hello,

Can any body tell me how I can put Comments in .SQL file, so that whenever I call .sql file from SQL*PLUS; SQL*Plus ignores these comments and consider only sql & PLSQL commands.

Regards,

Abbasi
  • 1. Re: how to put Comments in SQL
    NSK2KSN Journeyer
    Currently Being Moderated
    -- single line comment,

    /*

    Multi
    ;line comments

    */
  • 2. Re: how to put Comments in SQL
    Karthick_Arp Guru
    Currently Being Moderated
    You can put your comments using the keyword REM.

    You can use REM like this
    REM This is my Commend
  • 3. Re: how to put Comments in SQL
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Abbasi,

    As the others have said (but more pedantically):

    Two dashes (--) mean the rest of the line is a comment
    /* means that everything up to and including the next */ is a comment. Even though this is often called a "multi-line comment", the two symbols can be on the same line, for example:
    SELECT  empno, /* hiredate, */ ename
    FROM    scott.emp;
    The two types of comments above can be used in the middle of a SQL statement. If they are included in code that creates a view or a stored procedure (e.g., CREATE PACKAGE), then the comments are strored in the data dictionary along with the source code that is compiled.

    The SQL*Plus command REMARK (which can be abbreviated REM) is a comment.
  • 4. Re: how to put Comments in SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    To add to what Frank said - you ideally want comments to be client comments and not SQL comments. In other words, you want the comments to be treated as comments by the client language or application (such as Java or SQL*Plus). And not for the comments to be send to Oracle as part of the SQL statement.

    There's a couple of reasons for this. Not only do the comments increase the size of the SQL instruction send to Oracle for parsing, it is also parsed and stored like that in the SQL shared pool. This means an increase in memory required to store that SQL with comments, and the risk of not re-using the actual SQL statement. The shared pool is all about creating common and unique SQL cursors that are re-usable by the same session and by other sessions.

    The same SQL with different comments is a problem. E.g.
    SQL> exec DBMS_APPLICATION_INFO.set_module( 'MY TEST', 'comments' );
    
    PL/SQL procedure successfully completed.
    
    SQL> select count(*) from user_objects -- this is a comment;
    
      COUNT(*)
    ----------
             4
    
    SQL> select count(*) from user_objects /* this is a comment */;
    
      COUNT(*)
    ----------
             4
    
    SQL> select count(*) /* this is a comment */ from user_objects;
    
    COUNT(*)/*THISISACOMMENT*/
    --------------------------
                             4
    
    SQL> col sql_fulltext format a100 trunc
    SQL> select sql_fulltext from v$sqlarea where parsing_schema_name = USER and module = 'MY TEST' and sql_fulltext not like '%v$sqlarea%';
    
    SQL_FULLTEXT
    ----------------------------------------------------------------------------------------------------
    select count(*) from user_objects -- this is a comment
    select count(*) from user_objects /* this is a comment */
    select count(*) /* this is a comment */ from user_objects
    
    SQL>
    The comments resulted in 3 SQL cursors being created (hard parsed) and stored in the shared pool. If those comments were not part of the actual SQL statement (but part of the client app/language), we would have seen a single cursor in the shared pool, being used 3x.
  • 5. Re: how to put Comments in SQL
    Sven W. Guru
    Currently Being Moderated
    I sometimes use
     PROMPT text
    .

    But this is not ignored but will create an output to a possible spool file. Serves me often as a type of section heading in a larger SQL script.

    So technically it is not a comment but can be used like one depending on the circumstances.

    Edited by: Sven W. on Jul 5, 2010 1:55 PM
  • 6. Re: how to put Comments in SQL
    ora_1978 Explorer
    Currently Being Moderated
    Single line comment is -- and multi line comment is by using the block indise /* lines to be commented*/

    vinodh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points