6 Replies Latest reply: Jul 5, 2010 7:23 AM by ora_1978 RSS

    how to put Comments in SQL

    658523
      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
          -- single line comment,

          /*

          Multi
          ;line comments

          */
          • 2. Re: how to put Comments in SQL
            Karthick_Arp
            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
              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
                Billy~Verreynne
                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.
                  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
                    Single line comment is -- and multi line comment is by using the block indise /* lines to be commented*/

                    vinodh