CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
sadabat
sadabat

Reputation: 3

I want to update these tables by adding 1000000 'FİLE_NO' and 'PROT_NO' columns

I want to find tables in the user schema that contain the columns 'FİLE_NO' and 'PROT_NO'. Then I want to update these tables by adding 1000000 to 'FİLE_NO' and 'PROT_NO' columns. I tried to write the code below, but it failed when updating. How do I fix this error?

declare
 v_match_count integer;
 begin
  v_match_count:=0;
 for tablolar in  
 (select  user_tables.table_name from user_tables,user_tab_columns where  
 user_tables.table_name=user_tab_columns.table_name 
                   and user_tab_columns.column_name='file_no'

intersect   

 select  user_tables.table_name from user_tables,user_tab_columns where  
 user_tables.table_name=user_tab_columns.table_name 
                   and user_tab_columns.column_name='PROT_NO'
) loop          
       -- v_match_count:=v_match_count+1;
        --              dbms_output.put_line(tablolar.table_name||' = '||v_match_count);
        UPDATE tablolar.table_name SET file_no =file_no+1000000,--oracle gives me the error on this line ???
                                       PROT_NO =PROT_NO+1000000;


end loop;                   
end;

ORA-06550: line 16, column 25:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 16, column 9:
PL/SQL: SQL Statement ignored

Upvotes: 0

Views: 32

Answers (2)

Olivier Depriester
Olivier Depriester

Reputation: 1625

Unhappily PL/SQL is not an oriented object language. So tablolar.table_name is not a pointer to a table :). You are trying to update the table table_name of the schema tablolar You have to use some dynamic sql :

EXECUTE IMMEDIATE 'UPDATE ' || tablolar.table_name || ' SET file_no =file_no+1000000, PROT_NO =PROT_NO+1000000';

instead of

UPDATE tablolar.table_name SET file_no =file_no+1000000, PROT_NO =PROT_NO+1000000';

Upvotes: 0

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

You can not just get a table name from a cursor and update it. Use dynamic SQL for that: 

EXECUTE IMMEDIATE 'UPDATE '||tablolar.table_name||' SET file_no = file_no+1000000,
                                                        PROT_NO = PROT_NO+1000000';

Upvotes: 1

Related Questions