PL/SQL ORA-00903: invalid table name during EXECUTE IMMEDIATE

Question

here is my code :

procedure test IS 
  tn varchar2(99);
  i number(6);
  cursor c1 is
    select tname
    from   tab
    where  tabtype='TABLE'
    and    tname NOT LIKE 'BIN%'
    order by tname;
begin
  open c1;
  loop
    fetch c1 into tn;
    exit when c1%notfound;
    sq3 := 'select count(*) from '||tn;
    EXECUTE IMMEDIATE sq3 INTO i;
    htp.anchor2('XXX.test2?tabl='||tn,tn||' = '||i,'F3','F3');
  end loop;
  close c1;
end test;

after execution I receive error

Failed to execute target procedure
ORA-00903: invalid table name

for this line "EXECUTE IMMEDIATE sq3 INTO i;"

Any ideas why?

Thanks in advance!

Answer 1

The error is self explanatory:

One of the names of tables you are trying to count the number of rows of is invalid.

You can use the following code to catch the exception and determine the name of the table (you also need to declare the sq3 variable):

procedure test
IS
  sq3 CLOB;
  tn  varchar2(99);
  i   number(6);
  cursor c1 is
    select tname
    from   tab
    where  tabtype='TABLE'
    and    tname NOT LIKE 'BIN%'
    order by tname;

  invalid_table_name EXCEPTION;
  PRAGMA EXCEPTION_INIT( invalid_table_name, -903 );
begin
  open c1;
  loop
    fetch c1 into tn;
    exit when c1%notfound;
    BEGIN
      sq3 := 'select count(*) from '||tn;
      EXECUTE IMMEDIATE sq3 INTO i;
      htp.anchor2('XXX.test2?tabl='||tn,tn||' = '||i,'F3','F3');
    EXCEPTION
      WHEN invalid_table_name THEN
        DBMS_OUTPUT.PUT_LINE( 'Invalid Table Name: ' || tn );
    END;
  end loop;
  close c1;
end test;

You may find that the table name is invalid because it is either case-sensitive or contains characters not normally expected in a table name and you are not handling it as such in the EXECUTE IMMEDIATE statement. In which case, you can use:

sq3 := 'select count(*) from "'||tn||'"';

db<>fiddle