How to add table name into dynamic query in execute immediate?

Question

I'm confused, because I don't know how to use variable as string in execute immediate clause.

declare
  variable1 varchar2(30):
  cur sys_refcursor;
begin
  open cur for
    select tablename from table1;

  loop
    fetch cur into variable1;
    exit when cur %notfound;

    execute immediate 'select count(*)
                         into variable1
                         from user_tables
                        where table_name =''' || variable1 || '''';
  end loop;

  close cur;

end;

Here variable1 is a table name. There should be a string value. How to do that? Still got an error.

Note that there is no reason (and no benefit) to using dynamic SQL at all here. If this is the real problem, use static SQL. And put counts in an `integer` or `number` variable, not into a `varchar2(30)`. — Justin Cave, Oct 30 '15 at 14:46

score 1 · Accepted Answer · answered Oct 30 '15 at 14:01

1

The execute immediate statement should look like this:

execute immediate 'select count(*) from user_tables where table_name ='''||variable1||'''' into variable1;

with the into xxx after the query.

answered Oct 30 '15 at 14:01

Max Passaghe

68
4

1

Better use `execute immediate 'select count(*) from user_tables where table_name = :var1' into variable1 USING variable1;` – Wernfried Domscheit Oct 30 '15 at 14:25

How to add table name into dynamic query in execute immediate?

1 Answers1