oracle-databaseplsqlexecute-immediate

execute immediate for tables having number in their names


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;

Solution

  • 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;