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

[navrsalemile]

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

 

[SantaMufasa]

Your error message says:

declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 149

It does not appear that your code is 149 lines long. Could you please post a copy-and-paste image of the code, complete with generated line numbers, to which the error message refers?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[taupirho]

You have a couple of problems here.
The code below should work for you:

declare
sql_string1 varchar2(2000);
sql_string2 varchar2(2000);
sql_string3 varchar2(2000);
-- dont need the line below
-- tab varchar2(50);
begin
sql_string1 := 'update ';

-- an extraneous ; was removed at end of line below
sql_string2 := 'set created_date = SYSDATE,
created_userid = ''sale'',
last_modified_date = SYSDATE,
last_modified_userid = ''sale''
';
-- for loop changed significantly !
for tab in (
select 'tablea' as tabname from dual union
select 'tableb' as tabname from dual union
select 'tablec' as tabname from dual union
select 'tabled' as tabname from dual
)
loop
execute immediate sql_string1 || tab.tabname || sql_string2
;
dbms_output.put_line( 'Updated -> ' || tab.tabname );
end loop;
end;

 

[sem]

This code is also not error-free: blank space should be added before SET keyword (at the beginning of sql_string2).

My bet is that "Invalid character" was produced by semicolon.

Regards, Dima

http://seminihin.narod.ru

 

[taupirho]

OK APART from the missing blank, did it work?

 

[sem]

I didn't try it
Actually I like the second example (with varray) more, as it's easier to add new tables.

Regards, Dima

http://seminihin.narod.ru

 

[navrsalemile]

Thank you all,

I am sure it would work. for loop is excellent trick. In the meantime I had to quickly resolve it and I did it by defining stored procedure accepting table name as param and I had no problem with string concatenation there (!?). In anonymous block I called stored procedure using second example above (based on VARRAY).
It could be that semicolon was the cause of the problem as sem pointed out. If I have time I'll investigate.

Thanks again!