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!

ORA-00903: invalid table name

875438Apr 3 2012 — edited Apr 3 2012
Hi,

I am getting the error "ORA-00903: invalid table name ORA-06512: at line 30"
set feedback off;
set define off;
set serveroutput on;
declare 

v_sql varchar2(4000);
v_alter_table_name varchar2(30);
--------------------------------------
v_alter_col_cons_name varchar2(30);
v_alter_col_cons_action varchar2(100);
--------------------------------------
v_alter_fk_col_name   varchar2(40);
v_alter_fk_reference  varchar2(40);
--------------------------------------
v_date_stamp varchar2(8);
v_cnt number(3);

begin 
  
dbms_output.put_line ('---------------- Start :Altering tables ----------------');
  --------------------------------------  
  --Add Foreign Key
  --------------------------------------  
  v_alter_table_name    :='CM_CUST';  
  v_alter_col_cons_name :='FK_CM_CUST_CMNSCALE_GRADE';
  v_alter_col_cons_action := 'add';
  v_alter_fk_col_name     := 'CMNSCALE_GRADE';
  v_alter_fk_reference     := 'MST_COMMONSCALE (CMNSCALE_GRADE)';
  dbms_output.put_line ('Altering (to '|| v_alter_col_cons_action ||') foreign key contraint ' || v_alter_table_name || '.' || v_alter_col_cons_name || '...');
  select count(1) into v_cnt from user_constraints c where lower(c.CONSTRAINT_NAME) = lower(v_alter_col_cons_name);
  if v_cnt = 0 then 
    v_sql:= 'alter table ' || v_alter_table_name || ' add constraint ' || v_alter_col_cons_name || ' foreign key ('|| v_alter_fk_col_name ||') references ('||v_alter_fk_reference||')' ;
    execute immediate v_sql;   
    dbms_output.put_line (v_alter_col_cons_action || 'ed foreign key contraint ' || v_alter_col_cons_name || ' on table ' || v_alter_table_name);
  else
    dbms_output.put_line ('## Constraint ' || v_alter_table_name || '.' || v_alter_col_cons_name || ' already  exists'); 
  end if;
  dbms_output.put_line ('');
  
  dbms_output.put_line ('---------------- End   :Altering tables ----------------');

  v_sql :=null;

end;  
/
Pls help
This post has been answered by Igor.M on Apr 3 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 1 2012
Added on Apr 3 2012
7 comments
1,620 views