Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Dinesh.

Asked: September 13, 2016 - 7:04 am UTC

Last updated: September 13, 2016 - 8:38 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I've below code where i need to update different table with status to null.

declare
type v_table_name is table of varchar2(100);
p_tab v_table_name;
pv_tab v_table_name;
v_sql varchar2(100);
v_var varchar2(100);

TYPE NameList IS TABLE OF VARCHAR2(15);
enames NameList;
begin
select table_name bulk collect
into p_tab
from user_tables
where table_name like 'MOR_%';
for i in 1 .. p_tab.count loop
dbms_output.put_line(p_tab(i));
end loop;

/*for i in 1 .. p_tab.count
loop
execute immediate 'update ' || p_tab(i) || ' set MONEY = NULL';
end loop;
commit;*/
forall i in p_tab.first .. p_tab.last
execute immediate 'update || p_tab(i) || ' set MONEY = NULL' using p_tab(i);
commit;
end;

Please advise how to perform Bulk Updates on All MOR% tables.

Thanks
Dnesh

and Chris said...

You can't pass table names as bind variables. And you can't have a forall statement that dynamically generates SQL statements, so each pass affects a different table.

So you need to stick with your original loop approach and forget forall:

  for i in 1 .. p_tab.count
  loop
    dbms_output.put_line ( p_tab ( i ) ) ;
  end loop;


Or, even better, ditch dynamic SQL and place the update statements directly in your code!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dinesh Kumar, September 13, 2016 - 9:05 am UTC

Thanks a lot...

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here