Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

HELP-execute immediate in PL/SQL got error ORA-00904: : invalid identifier

570642Jan 5 2009 — edited Jan 6 2009
What is wrong with the following PL/SQL codes (actually it comes from Oracle Metalink Note: 313175.1):
===========
declare
cursor c1 is select * from semantics$;
v_statement varchar2(255);
v_nc number(10);
v_nt number(10);
begin
execute immediate
'select count(*) from semantics$' into v_nc;
execute immediate
'select count(distinct s_table_name) from semantics$' into v_nt;
dbms_output.put_line
('ALTERing ' || v_nc || ' columns in ' || v_nt || ' tables');
for r1 in c1 loop
v_statement := 'ALTER TABLE ' || r1.s_owner || '.' || r1.s_table_name;
v_statement := v_statement || ' modify (' || r1.s_column_name || ' ';
v_statement := v_statement || r1.s_data_type || '(' || r1.s_char_length;
v_statement := v_statement || ' CHAR))';
execute immediate v_statement;
end loop;
dbms_output.put_line('Done');
end;
/
=====
After run the codes as sysdba against 10gR2 database, I got this error:
Starting build select of columns to be altered
Altering 35249 columns in 4428 tables
declare
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at line 22

I didn't see anything wrong with the line of "execute immediate". I appreciate your help!

Thanks.
This post has been answered by OrionNet on Jan 5 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 3 2009
Added on Jan 5 2009
4 comments
1,733 views