CODEWITHSUNDEEP
sqloracle-database
Amber C
Amber C

Reputation: 43

Add Comment or Notes Block to top of View?

My department frequently puts a comment block at the top of our procedures that explains the basic purpose of the procedure, as well as some modification history and related work order numbers. I would like to do the same thing with a few new views I am creating. Is this possible?

I've tried placing the comment block in various spots, before the CREATE OR REPLACE, after it, but it seems like any comment lines above the select are dropped. We are using Oracle and PL/SQL Developer.

/*
PURPOSE
MULTI-LINE COMMENTS
WORK ORDER NUMBERS FOR MODIFICATIONS 
ETC
*/

CREATE OR REPLACE VIEW SAMPLE_VIEW AS
SELECT     SYSDATE
FROM       DUAL

I don't receive any error messages. The comments are visible in the saved sql file, but not when you right-click and view the view in PL/SQL Developer. Any help would be greatly appreciated.

Upvotes: 3

Views: 1648

Answers (1)

APC
APC

Reputation: 146209

The statement starts at create. Anything before that is not part of the view definition and is ignored by the database. (This is the same for procedures or packages: the database ignores comments before the create or replace keywords.)

Solution is simple: move the comment text into the view body.

CREATE OR REPLACE VIEW SAMPLE_VIEW AS
    SELECT   
/*
PURPOSE
MULTI-LINE COMMENTS
WORK ORDER NUMBERS FOR MODIFICATIONS 
ETC
*/
      SYSDATE AS DATE1
FROM       DUAL

Note the comment must be embedded in the query, after the SELECT keyword, otherwise the compiler will bin it off. Frankly that's bobbins but that's the way it is.

Also, remember that we can add comments about database objects (table or table column, unified audit policy, edition, indextype, materialized view, mining model, operator, or view) using the COMMENT command. This is cleaner than embedding the comment in the query and treats Views like Tables, which seems idiomatic, rather than PL/SQL units. Find out more.

Upvotes: 3

Related Questions