1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using comment on in a stored procedure

Discussion in 'SQL PL/SQL' started by Flealand, Apr 3, 2013.

  1. Flealand

    Flealand Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hey there everyone,

    I'm new here, so I hope I haven't done any mistakes so far :)

    So, I want to create a procedure which adds a comment on each column of a table.
    The text of the comment sould be the columns name itself.

    It seems like I can't use "comment on" natively in a procedure, so I guess I have to use the "execute immediate" statement to do so.

    This is how I tried to do this, but without any success:


    Code (Text):

    procedure column_names_as_comment (p_table_name user_tab_columns.table_name%type)

    is
        -- cursor with table and column name
        cursor c_table_to_comment is
        select table_name,column_name
        from user_tab_columns
        where table_name = p_table_name;
        -- rowset for the cursor
        r_table_to_comment c_table_to_comment%rowtype;
       
    begin      

        for r_table_to_comment in c_table_to_comment loop                                            
            execute immediate 'comment on column '|| r_table_to_comment.table_name ||'.'|| r_table_to_comment.column_name ||''' is  '''|| r_table_to_comment.column_name ||''' ';  
        end loop;
    end;
     

    I hope you can give me a hint, or another idea how to reach my goal.

    Thanks in advance!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your statement you're submitting to execute immediate isn't correct:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE column_names_as_comment (p_table_name user_tab_columns.table_name%TYPE)
      2  IS
      3      -- cursor with table and column name
      4          cursor c_table_to_comment IS
      5          SELECT TABLE_NAME,column_name
      6          FROM user_tab_columns
      7          WHERE TABLE_NAME = p_table_name;
      8
      9      -- rowset for the cursor
     10          r_table_to_comment c_table_to_comment%rowtype;
     11
     12      -- statement variable
     13          v_sqlstmt       varchar2(4000);
     14
     15  BEGIN
     16
     17          FOR r_table_to_comment IN c_table_to_comment loop
     18                  v_sqlstmt:='comment on column '|| r_table_to_comment.TABLE_NAME ||'.'|| r_table_to_comment.column_name ||' is  '''|| r_table_to_comment.column_name||'''';
     19                  EXECUTE immediate v_sqlstmt;
     20          END loop;
     21  END;
     22  /
     
    PROCEDURE created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> EXEC column_names_as_comment('EMP')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM user_col_comments;
     
    TABLE_NAME                     COLUMN_NAME                    COMMENTS
    ------------------------------ ------------------------------ ----------------------------------------
    EMP                            JOB                            JOB
    EMP                            MGR                            MGR
    EMP                            HIREDATE                       HIREDATE
    EMP                            SAL                            SAL
    EMP                            COMM                           COMM
    EMP                            DEPTNO                         DEPTNO
    EMP                            EMPNO                          EMPNO
    EMP                            ENAME                          ENAME
    BONUS                          COMM
    DEPT                           DEPTNO
    BONUS                          ENAME
     
    TABLE_NAME                     COLUMN_NAME                    COMMENTS
    ------------------------------ ------------------------------ ----------------------------------------
    DEPT                           DNAME
    SALGRADE                       GRADE
    BONUS                          SAL
    SALGRADE                       LOSAL
    BONUS                          JOB
    SALGRADE                       HISAL
    DUMMY                          DUMMY
    DEPT                           LOC
     
    19 ROWS selected.
     
    SQL>
    The table name and column you're commenting on cannot be strings; you have them as such in your code. The only string in the statement is the comment text. I have corrected the statement you provided as shown above.
     
    Flealand likes this.
  3. Flealand

    Flealand Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Well, I guess i couldn't have asked for more.
    Thank you very much for your answer! It works just fine.

    Seems like this forum is also gently to PL/SQL beginners, which makes me quite happy =).
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Knowledge is for sharing -- it does no good to horde it like a miser. That which you know may be new knowledge to someone else -- as others share with you share what you know with others.

    I am happy to help any time and in any way I can.