Commenting an Oracle stored procedure

I want to comment a stored procedure in Oracle something like this

-- MODIFICATION HISTORY
-- Person           Date            Comments
-- ---------        ------          ------------------------------------------
-- MICK             09/15/2010      New Sproc

CREATE OR REPLACE PROCEDURE INTERMISSIONS(
                            p_Myid        IN NUMBER,
                            p_Mytype      IN NUMBER,
                            p_recordset         OUT GET_RESULTS_BY_ID_PKG.get_by_id_cursor)

How do you do this in Oracle? How do developers comment a SPROC. Do the comment sit inside the stored procedure? This开发者_JAVA技巧 will look terrible when there are loads of revision changes so looking for best practice and advice :-)

Thanks mick

Comments are yet another reason to stay away from stored procedures, and use packages instead.

You can comment a packaged procedure just like you want, for example:

CREATE OR REPLACE PACKAGE your_package
AS
  --
  -- MODIFICATION HISTORY
  -- Person           Date           Comments
  -- ---------        ------         ------------------------------------------
  -- MICK             09/15/2010     Created new packaged procedure INTERMISSIONS
  --  
  PROCEDURE INTERMISSIONS
  ( p_Myid      IN  NUMBER
  , p_Mytype    IN  NUMBER
  , p_recordset OUT GET_RESULTS_BY_ID_PKG.get_by_id_cursor
  );
END your_package;

Regards,
Rob.

I always package my procedures and add comments containing version history immediately after the package/package body statement

CREATE OR REPLACE PACKAGE test_pkg AS
-- 
-- Version History
-- version date      Name  Description
-- 1.0     13/3/2011 pablo initial version
-- 
PROCEDURE proc1

If you want the version info stored in the file, then you can do it as you are. But if you want it stored in the database then it needs to exist as a comment inside the actual proc or Oracle will not store it.

I don't know why anyone wants to keep all of the version history inside the proc though. Isn't that what your version control system is for? And that history is just comments anyway, you still need to go back to diff against your previous version if you want to see the actual code changes.

I generally set up tags for the version control system in comments in the declaration section so that the version control system updates it automatically. This way I can always see what the current version is, and that is enough.

Indeed, on my current project the standard exception handling/logging system we built grabs the version info from variables in our API packages so that we can tie recorded code errors to software versions. The variables get updated automatically through the tags that the version control system recognizes.

e.g. each package has the following:

 create or replace package body pkg_payment_api as  
   cs_package_name     CONSTANT VARCHAR2(60) :='pkg_payment_api';
   cs_package_version  CONSTANT VARCHAR2(30) := '$Rev: 24992 $';
   cs_package_author   CONSTANT VARCHAR2(30) := '$Author: MBrought $';
   cs_package_date     CONSTANT VARCHAR2(60) := '$Date: 2011-03-08 14:54:48 -0500 (Tue, 08 Mar 2011) $';

   FUNCTION get_package_version
   RETURN varchar2
   IS
   BEGIN
        RETURN 'Version: '||cs_package_version || ' Author: ' ||cs_package_author || ' Timestamp: '||cs_package_date;
   END get_package_version;

And every public function and procedure in that package has an exception handler that calls a common logging routine which will store the exception, time, version info, and other relevant information.

But no way am I storing all of the version hisotry info in the database. The database just needs the current build and a means to identify its component versions - that's all.