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