-
Remove the semicolon at the end. Statements don't need that when using exec immediate. If your insert statement were something like the other example I posted, note there is not a semicolon at the end.
Practice using this, turn the comment into an insert statement on a test table (i.e., get a normal insert to work first).
Code:
DECLARE
error_code VARCHAR2(10);
error_message VARCHAR2(2000);
insert_statement VARCHAR2(4000);
v_owner metadb_tables.schema_owner%type;
v_table metadb_tables.table_name%type;
v_comments metadb_tables.table_comments%type;
CURSOR c IS
select t.schema_owner,
t.table_name,
t.table_comments
from metadb_tables t
where t.schema_owner = 'SOMETHING'
and t.table_name like '%HDR%'
order by 1,2,3;
BEGIN
FOR r in c LOOP
insert_statement :=
'COMMENT ON TABLE ' || r.schema_owner || '.' || r.table_name
|| ' is ' || '''' ||r.table_comments|| '''' || ';';
--remove this------------------^
DBMS_OUTPUT.PUT_LINE (insert_statement);
EXECUTE IMMEDIATE (insert_statement);
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
error_message := SQLERRM;
DBMS_OUTPUT.PUT_LINE (error_code || error_message);
END;
/
-