CODEWITHSUNDEEP
sqloracle-databaseplsql
Imran Hemani
Imran Hemani

Reputation: 629

ORACLE - missing keyword

I have the following query which shows me the error of missing keyword in Oracle:

DECLARE
  RESULT VARCHAR2(100);
  n number;
BEGIN

  for r in (SELECT TABLE_NAME FROM TST_REFRESH) LOOP

    execute immediate 'select count(*) into n from ' || R.TABLE_NAME;

    IF N = 0 THEN 
      dbms_output.put_line('Table Empty ' || r.TABLE_NAME);
    ELSE
      dbms_output.put_line('Table Not Empty' || r.TABLE_NAME);  
    END IF;

  END LOOP;

END;

What would be the error?

Upvotes: 0

Views: 109

Answers (3)

vishnu sable
vishnu sable

Reputation: 358

Here is sample code you are thinking 

declare
  result number;
  n number;
begin
  for r in (select table_name from user_tables where rownum < 3) loop
    execute immediate 'select count(*) from ' || r.table_name
      into n;
    if n = 0 then
      dbms_output.put_line('Table Empty ' || r.table_name);
    else
      dbms_output.put_line('Table Not Empty cont ' || n || ' : ' || r.table_name);
    end if;
  end loop;
end;

Upvotes: 0

Coder55
Coder55

Reputation: 559

Your error: You don't have to write 'into' in your query. But you are able to append it to your call of 'execute'. This query should work: 

@query := 'select count(*) from ' || R.TABLE_NAME;
execute immediate @query into n;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

There are several ways to return a value from an execute immediate. Having into n in the string is not one of them.

So, one thing you need to fix is this call. Instead:

@sql := 'select count(*) from ' || R.TABLE_NAME;
execute immediate @sql into n;

Upvotes: 2

Related Questions