-
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
-
execute immediate is not a function, take out parenthesis like...
EXECUTE IMMEDIATE insert_statement;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
PavB,
I did and got the same error.
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.
-
please do this... comment out whole EXCEPTION block so complete error message stack is shown.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
PavB,
Line 39 is the execute immediate line.
SQL> execute this_proc;
COMMENT ON TABLE INC_HDR is 'Incidents Header Added this test data';
BEGIN this_proc; END;
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "THIS_PROC", line 39
ORA-06512: at line 1
-
Why are you are using two different cursor constructs? A cursor for loop does not need the exit when not found statement. Self-inflicted code wound.
-
Originally Posted by PAVB
execute immediate is not a function, take out parenthesis like...
EXECUTE IMMEDIATE insert_statement;
That is irrelevant.
Code:
SQL> create table test_tab (line number);
Table created.
SQL> declare
2 v_stmt varchar2(80) := 'insert into test_tab values (1)';
3 begin
4 execute immediate v_stmt;
5 execute immediate (v_stmt);
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select * from test_tab;
LINE
----------
1
1
-
Thanks, I figured it didn't matter, but got the same error anyway.
So, this is mind boggling, why does this error occur?
-
What is mind boggling about "why are you using two different cursor constructs?"
for rec in cur loop
do whatever
end loop
Where do you see a check for notfound in cursor for loop syntax?
-
Stecal,
Thanks I have taken that out. But, still the error.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|