How does one COMMENT a table ONLY if it has NO comment?
I am importing comments on 1500 tables from a separate source. I would like to put a comment on each table ONLY if one does not currently exist (COMMENTS field is NULL). A command like:
COMMENT ON TABLE
owner1.table1 IS
'Test 4 adding comment'
WHERE owner = 'OWNER1'
AND table_name = 'TABLE1';
But the WHERE clause is not valid. Is there a way to effectively do this for 1500 COMMENT statements?
Thanks.
Software/Hardware used:
Software/Hardware used:
ASKED:
December 4, 2008 2:39 PM
UPDATED:
April 20, 2012 12:45 PM
Answer Wiki:
This will have to be a 2 step process
<pre>
DECLARE
CURSOR c1 IS
SELECT *
FROM ALL_TAB_COMMENTS
WHERE COMMENTS IS NULL
AND OWNER = 'scott';
BEGIN
FOR rec IN c1
LOOP
EXECUTE IMMEDIATE 'COMMENT ON TABLE ' || rec.owner, || '.' || rec.table_name || ' IS ''new table comment''';
END LOOP;
END;
/
</pre>
-----------------------
If the comments are not the same for all of the tables you could create your own <i>update_if_null</i> procedure.
Something like this:
<pre>CREATE OR REPLACE PROCEDURE comment_if_null (
table_name_in IN varchar2,
owner_in IN varchar2,
comments_in IN varchar2
)
IS
l_has_comments number;
table_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
BEGIN
SELECT 1 INTO l_has_comments
FROM all_tab_comments
WHERE owner = owner_in
AND table_name = table_name_in
AND comments is not null;
EXCEPTION
when no_data_found then
BEGIN
EXECUTE IMMEDIATE 'COMMENT ON TABLE '||owner_in||'.'||table_name_in||' IS '''||comments_in||'''';
EXCEPTION
when table_does_not_exist then
DBMS_OUTPUT.PUT_LINE('Table '||owner_in||'.'||table_name_in||' does not exist');
END;
END;
/</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
