how to update a list of tables using native dynamic sql?

From: navrsale <navrsalemile_at_yahoo.ca>
Date: 20 Apr 2005 12:18:46 -0700
Message-ID: <11da0fe1.0504201118.39f0fb36@posting.google.com>

Hi all,

Given is the list of tables that need to be updated. I try to use this syntax (and many others) but got errors:

declare

   sql_string1 varchar2(2000);
   sql_string2 varchar2(2000);
   tab varchar2(50);
begin

    sql_string1 := 'update ';
    sql_string2 := 'set created_date = SYSDATE,

                        created_userid = ''sale'',
                        last_modified_date = SYSDATE,
                        last_modified_userid = ''sale''
                        ;';
    for tab in (select


        'tablea',

        'tableb',

        'tablec',

        'tabled'

         from dual)
    loop
                execute immediate sql_string1 || tab || sql_string2;
                dbms_output.put_line( 'Updated -> ' || tab );
        end loop;

end;
/

I also tried:

declare

• Type TablesList IS TABLE OF VARCHAR2( 50 ); TYPE TablesList IS VARRAY( 200 ) OF VARCHAR2(50); sql_string varchar2(4000); tab varchar2(50); i pls_integer;
• tables dbms_sql.Varchar2_Table; tables TablesList := TablesList(
  'tablea',
  'tableb',
  'tablec',
  'tabled'
  ); begin for i in tables.first..tables.last loop sql_string := 'update ' || tables(i) || ' set created_date = sysdate,'; sql_string := sql_string || 'created_userid = ''sale'','; sql_string := sql_string || 'last_modified_date = sysdate,'; sql_string := sql_string || 'last_modified_userid = ''sale'';';

          execute immediate sql_string;

          dbms_output.put_line( 'Updated -> ' || tables(i) );     end loop;
exception

    when others then

        RAISE;
        -- raise_application_error( -20199, 'Error updating table: '

|| tables(i) || ' (' ||SQLERRM || ')' ); end;
/
show errors;

I get error:

declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 149 Received on Wed Apr 20 2005 - 14:18:46 CDT