execute immediate for tables having number in their names

Question

I have a execute immediate in my package and when the v_object_name is something like 20200823_AGL, I get this error:

ORA-00903: Invalid table name

How can I fix it?

EXECUTE IMMEDIATE 'SELECT MAX(LAST_UPDATE),COUNT(*) FROM ' || v_object_name || ''
                                                                 INTO v_max_update_date,
                                                                      v_count;

Answer 1

From Oracle documentation: Schema Object Names and Qualifiers

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

EXECUTE IMMEDIATE 'SELECT MAX(LAST_UPDATE),COUNT(*) FROM "' || v_object_name || '"'
                                                                 INTO v_max_update_date,
                                                                      v_count;

A more complete example:

declare
  v_object_name char(12) := '20200823_AGL';
  v_count pls_integer;
begin
  execute immediate 'select count(*) from "' || v_object_name || '"'
  into v_count;
--
  DBMS_OUTPUT.PUT_LINE('v_count = ' || v_count);
end;

Answer 2

Such object were probably created using double quotes.

create view "1_test_view" as select 1 col_name from dual; --> ORA-00999: invalid view name

create view "1_test_view" as select 1 col_name from dual: --> works fine

The same rules for tables

In this case you need to query from such objects using double quotes as well

select * from 1_test_view; --> ORA-00903: Invalid table name

select * from "1_test_view"; --> will work fine