All,

I am getting an error on this procedure.

AS
error_code VARCHAR2(10) := ' ';
error_message VARCHAR2(2000) := ' ';
insert_statement VARCHAR2(4000) := ' ';
v_owner VARCHAR2(30) := ' ';
v_table VARCHAR2(30) := ' ';
v_comments VARCHAR2(4000) := ' ';



CURSOR cur_in_data 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%' -- note restriction for testing
order by 1,2,3;


BEGIN

FOR each_record in cur_in_data
LOOP
EXIT WHEN cur_in_data%NOTFOUND;

v_owner := each_record.schema_owner;
v_table := each_record.table_name;
v_comments := each_record.table_comments;

insert_statement := ' ';
insert_statement := 'COMMENT ON TABLE ' || v_owner || '.' || v_table
|| ' is ' || '''' ||v_comments|| '''' || ';';

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 procedure name;

If I rem out the execute immediate it doesn't give the error, but obviously doesn't execute. I put in OUTPUT so you can see the line I am trying to execute. If i cut and past the line it works.

SQL> set serveroutput on

SQL> execute this_proc;

COMMENT ON TABLE INC_HDR is 'Incidents Header Added this test data';

-911ORA-00911: invalid character

PL/SQL procedure successfully completed.

Thanks
Katora