ora-00911 - invalid character
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