sqloracleplsqlora-00904

Getting 'ORA00904: invalid identifier' on dynamic tables


I have a script that works with dynamic tables. When executing the below code segment, it gives me error ORA00904: invalid identifier.

IF Database_SYS.Column_Exist (service_tab_, ''KEY_VALUE'') THEN                           
   UPDATE '|| service_tab_ || '
   SET key_ref    = new_key_ref_,                                  
   key_value  = ''Test'',                                  
   rowversion = SYSDATE
   WHERE ROWID    = rec_.ROWID;                                                      
ELSE                                              
   UPDATE '|| service_tab_ || '
   SET key_ref    = new_key_ref_,                               
   rowversion = SYSDATE
   WHERE ROWID    = rec_.ROWID;                           
END IF;

Solution

  • Would be better this one:

    BEGIN
        IF Database_SYS.Column_Exist (service_tab_, '''' || KEY_VALUE || '''') THEN
            EXECUTE IMMEDIATE 
                'UPDATE ' || service_tab_ || '
                    SET key_ref    = new_key_ref_,
                        key_value  = :val,
                        rowversion = SYSDATE
                  WHERE ROWID    = :rid'
            USING 'TEST', rec_.ROWID;
        ELSE
            EXECUTE IMMEDIATE
                'UPDATE ' || service_tab_ || '
                    SET key_ref    = new_key_ref_,
                        rowversion = SYSDATE
                  WHERE ROWID    = :rid'
            USING rec_.ROWID;
        END IF;
    END;
    

    I don't think you need '''' || KEY_VALUE || '''', using just Database_SYS.Column_Exist(service_tab_, KEY_VALUE) should be fine if you have properly coded the function.