-
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,
-
You'd be crazy to try anything other than the correct SQL statement.
-
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,
-
How were you planning on doing it programatically? Use that same logic to generate a SQL script.
Jeff Hunter
-
Use Design Tool.
Simple & Elegant & Better Doc.
Tamil
-
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???
-
Code:
begin
execute immediate 'comment on column my_table.col1 is ''comment'' ';
end;
-
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.
-
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|