Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011

    Unanswered: comment for column of all tables. ("insufficient privileges")

    I have 100's of tables having the common columns like Created_On , Last_Modified_ON , Created_by , Last_Modified_by , Enable_flag , Disable_flag.
    I want to Comment for All the tables Having the Similar Column name and Purpose.
    My Query as -

    set COMMENTS = 'This Stores the Time stamp when the record is created in the System.'

    How to give privileges ??
    I have tried -
    grant DBA to <Current_User>
    but not solved.

    Plz help.....

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    To define a comment on a column you have to use the COMMENT ON statement, you cannot use an UPDATE statement for that.

    Additionally USER_COL_COMMENTS is a system view that cannot be udpated (and even if you could, it wouldn't work, as the view does not contain a row unless there is a comment, so your UPDATE wouldn't update anything anyway).

    You need to generate a script that will contain the necessary SQL statement, something like this:

    SELECT 'COMMENT ON COLUMN '||table_name||'.'||column_name||' IS ''This stores the timestamp when the record is created in the system'';'
    FROM all_tab_columns
    WHERE column_name = 'LT_CREATED_ON';
    Just spool the output of this statement into a SQL script, then run that generated SQL script.

    Btw: what's a "plz"? (Sorry, I'm not a native speaker)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts