?
Solved

How to use Oracle  PL/SQL to Comment a Table Or column

Posted on 2014-10-02
6
Medium Priority
?
2,229 Views
Last Modified: 2014-10-02
Our database is uncommented. I would like to embed table and column comments.

What is the syntax?

is it something like ALTER Table MyTable comment = "my table comment" ?

is it something like ALTER Table MyTable, Column MyColumn comment = "my column comment" ?

Thanks,
0
Comment
Question by:Dovberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1600 total points
ID: 40356989
PL/SQL is the procedural language.

What you want is regular SQL.

The online docs have everything you need:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4009.htm#i2119719

COMMENT ON COLUMN employees.job_id
   IS 'abbreviated job title';

The TABLE one is the same:
COMMENT ON TABLE employees
   IS 'some table comment';
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 400 total points
ID: 40357466
From PL/SQL, you need to use
execute immediate '.... DDL Command...'

Ex.
begin
  execute immediate 'comment on table temp_dev is ''This is Temp''';
end;
/
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40357481
Don't use PL/SQL to do this.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40358103
I see you've opened a similar question in another thread.  Here's the same code I posted there.

You can/should delete that other thread now

CREATE OR REPLACE PROCEDURE set_comment(
    p_owner     IN VARCHAR2,
    p_table     IN VARCHAR2,
    p_comment   IN VARCHAR2
)
IS
BEGIN
    EXECUTE IMMEDIATE
        'comment on table "' || p_owner || '"."' || p_table || '" is ''' || p_comment || '''';
END;

You might note I'm putting double quotes around the owner and table name.
That's to support mixed-cased object names and names with non-standard characters.

It's generally considered bad practice to have such names, but it's a good idea to support them.   If you don't need that you can either remove the double-quotes, or call the procedure with upper case names,  or wrap the parameters in UPPER() function in the concatenation
0
 

Author Closing Comment

by:Dovberman
ID: 40358104
That helps.  Thanks,
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40358126
looks like I was too late here on my cross post.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question