When trying to run the following code in SQL Developer the return values from the statement are all NULL.
SELECT DECODE(paaf.assignment_type, 'E', hr_general.decode_lookup('EMP_CAT', paaf.employment_category),
'C', hr_general.decode_lookup('CWK_ASG_CATEGORY', paaf.employment_category))
FROM per_all_assignments_f paaf
WHERE SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
However when the same query in SQL*Plus and Toad the return values are correct.
After some rooting about in the Oracle eTRM I found that the function decode_lookup was using a cursor that took data from the view hr_lookup.
When I looked at this view through SQL Developer there is no data. However when querying the view in SQL*Plus there are > 51K rows.
Can anyone shed light on why this is? I have tried it on SQL Developer 1.2 and 1.5 EA3. I am working on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 with eBusiness Suite 11.5.10.2
Thanks,
James.