CODEWITHSUNDEEP
sqloracle-databasesyntaxplsqlexecute-immediate
bazyl
bazyl

Reputation: 263

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

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.

Upvotes: 1

Views: 1088

Answers (1)

Max Passaghe
Max Passaghe

Reputation: 68

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.

Upvotes: 1

Related Questions