DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: update COMMENTS on the columns

Hybrid View

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    update COMMENTS on the columns

    Is there a way we can update the columns by writing a scripts, I don't want to do it manually like :

    COMMENT on COLUMN table_name.comments is 'blah';


    Another word, USER_COL_COLUMNS is a view and I can't update that view, is there any others way?????

    thanks,

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You'd be crazy to try anything other than the correct SQL statement.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Oct 2003
    Posts
    312
    Slimdave,

    so you are saying I should update each comment manually through SQL??? The problem is we have a lot of columns that need to be update with the new comments.

    thanks,

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How were you planning on doing it programatically? Use that same logic to generate a SQL script.
    Jeff Hunter

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use Design Tool.
    Simple & Elegant & Better Doc.

    Tamil

  6. #6
    Join Date
    Oct 2003
    Posts
    312
    thanks all,

    I have the procedure but I have problem, below is my code:

    CREATE OR REPLACE PROCEDURE load_comments

    IS

    v_name varchar2(1000);
    v_table varchar2(50);
    v_column varchar2(50);
    v_comments varchar2(4000);

    CURSOR COLUMN_LIST IS
    SELECT TABLE_NAME, COLUMN_NAME, DATA_DESCRIPTION || case when comments = '0' then '' else '; ' || comments end
    from mytest;
    UNION
    SELECT TABLE_NAME, COLUMN_NAME, DATA_DESCRIPTION || case when comments = '0' then '' else '; ' || comments end
    from blah;
    UNION
    SELECT TABLE_NAME, COLUMN_NAME, DATA_DESCRIPTION || case when comments = '0' then '' else '; ' || comments end
    from blah_blah;

    BEGIN
    OPEN COLUMN_LIST;
    LOOP
    FETCH COLUMN_LIST INTO v_table, v_column, v_comments;

    v_name := 'dummy.'||v_table||'.'||v_column;

    COMMENT ON COLUMN v_name IS v_comments;

    end loop;
    close COLUMN_LIST;

    EXCEPTION
    WHEN others THEN
    dbms_output.put_line(SQLERRM);
    END; -- Procedure
    /



    I have problem with the COMMENT ON COLUMN, it said "encounterd symbol ON"


    any advises???

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    begin
       execute immediate 'comment on column my_table.col1 is ''comment'' ';
    end;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Oct 2003
    Posts
    312
    Slimdave,


    how can you fix the above code???

    rewrite:

    dbms_output.put_line('tst');
    OPEN COLUMN_LIST;
    LOOP
    FETCH COLUMN_LIST INTO v_table, v_column, v_comments;


    v_name := 'COMMENT ON COLUMN '||v_table ||'.'||v_column||' IS '||' " '||v_comments||' " ';

    dbms_output.put_line(v_name);



    EXECUTE IMMEDIATE v_name;



    end loop;
    close COLUMN_LIST;

    EXCEPTION
    WHEN others THEN
    dbms_output.put_line(SQLERRM);
    END;
    /


    my problem now is the " when I try to pick up the v_comments variable and it complain about identifier too long.
    Last edited by learning_bee; 02-10-2004 at 03:39 PM.

  9. #9
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    v_name varchar2(4000);
    Cheers!
    OraKid.

  10. #10
    Join Date
    Oct 2003
    Posts
    312
    balajiyes,

    it's not b/c of the length is not long enough. it's b/c of the " that caused the problem. I try to figure it out how to pick up the variable together with the ||

Posting Permissions

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


Click Here to Expand Forum to Full Width