How does one COMMENT a table ONLY if it has NO comment?

20 pts.
Tags:
Oracle Database
Oracle Table
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following