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