Last updated on November 26th, 2014 at 07:07 am
Comments are text embedded within SQL statements and PL/SQL code which describes the purpose of the code. Comments improve the readability, usability and maintenance of the code. Proper comment and formatting can save lot of hours of unproductive work for developers and teams.
Oracle developers can use two types of comments – In Line and Multiline. Both Inline and Multiline comments can be used in SQL statements and PL/SQL.
In-Line comments starts with — (two hyphens) and is only limited to one line.
Multiline comments start with /* and ends with */ and spans multiple lines.
Oracle uses following comment styles for its standard scripts (scripts under – $oracle_home/rdbms/admin directory) which are shipped with oracle database.
-- -- Copyright (c) Oracle Corporation 1988, 1999. All Rights Reserved. -- -- NAME -- demobld.sql -- -- DESCRIPTION -- This script creates the SQL*Plus demonstration tables in the -- current schema. It should be STARTed by each user wishing to -- access the tables. To remove the tables use the demodrop.sql -- script. -- -- USAGE -- SQL> START demobld.sql -- --
In many oracle shipped sql scripts you will see REM commands (REMARK command). This can also be used to enhance the readability and maintenance of the sql scripts. A sample snippet from the mksample.sql is shown below:
Rem Rem $Header: mksample.sql.sbs 02-apr-2003.14:55:17 $ Rem Rem mksample.sql Rem Rem Copyright (c) 2001, 2003, Oracle Corporation. All rights reserved. Rem Rem NAME Rem mksample.sql - creates all 5 Sample Schemas Rem Rem DESCRIPTION Rem This script rees and creates all Schemas belonging Rem to the Oracle Database 10g Sample Schemas. Rem If you are unsure about the prerequisites for the Sample Schemas, Rem please use the Database Configuration Assistant DBCA to Rem configure the Sample Schemas. Rem Rem NOTES Rem - OUI instantiates this script during install and saves it Rem as mksample.sql. The instantiated scripts matches Rem the directory structure on your system Rem - Tablespace EXAMPLE created with: Rem CREATE TABLESPACE example Rem NOLOGGING Rem DATAFILE '<filename>' SIZE 150M REUSE Rem AUTOEXTEND ON NEXT 640k Rem MAXSIZE UNLIMITED Rem EXTENT MANAGEMENT LOCAL Rem SEGMENT SPACE MANAGEMENT AUTO;
In my PL/SQL programs I use multi-line comment style like below:
/************************************************************************ ** Purpose: Script to demonstrate the top 10 features in Oracle 12c for developers ** Date: 1st Feb 2014, Saturday ** Author: Vijay Mahawar ** Website: https://www.mahawar.net/blog ** Version: 1.0 *************************************************************************/
Oracle allows to store comments even for schema objects like table and views and on columns of table, views and materialized views. These comments provide useful information about the table and columns to other developers.
To insert an explanatory remark on the notes
column of the EMP table, you might issue the following statement:
COMMENT ON COLUMN EMP.EMPNO IS 'Employee Number assigned to Employees';
The comments defined in this way are stored in data dictionary views.
Object comments are stored in USER_TAB_COMMENTS
Column comments are stored in USER_COL_COMMENTS
To drop this comment from the database, issue the following statement:
COMMENT ON COLUMN EMP.EMPNO IS '';
Please use setup scripts and demo script script for this.
Note: Few minutes spent on proper comments can save hours of efforts in code maintenance and debug.
Cheers!
Vijay
You must log in to post a comment.