What is causing random ORA-00904: Invalid identifier ?

Hello to fellow experts, we've encountered rather killer problem that is haunting us. When we execute this select, it throws the mentioned error ORA-00904: Invalid identifier. How is that possible ? What is causing this problem ? How to get rid of it ? It seems like some strange Oracle cache problem.

<see sql statement>

Note: When we try to modify it like order by 1, it works for a while but then it stops working again. It's random. Thus unusable.

We're using ODP.NET, this select doesn't work from C# (server-side).

regards,
Kate
SELECT * FROM ( select /*+ FIRST_ROWS*/
    gc.id, gc.id_c_grp_cond, gc.id_c_flt_cond, gc.note, gc.d_start, gc.d_end, gc.b_value_any, 
    vscfc.code as fc_code, vscfc.short_name as fc_short_name, vscfc.full_name as fc_full_name,
    gc.id_person_payer, gc.id_person_customer, gc.id_service_pid, gc.id_contract, gc.id_ident,
    vscil.code cil_code, vscil.short_name as cil_short_name, vscil.full_name as cil_full_name,
    vscgc.code as cgc_code, vscgc.short_name as cgc_short_name, vscgc.full_name as cgc_full_name,
    decode(nvl2(gc.id_person_payer, -1, -2), -1, af_utl.get_person_text(py.pc, py.ic, py.dic, py.title, py.first_name, py.surname, 'S'),
      decode(nvl2(gc.id_person_customer, -1, -2), -1, af_utl.get_person_text(cu.pc, cu.ic, cu.dic, cu.title, cu.first_name, cu.surname, 'S'),
        decode(nvl2(gc.id_contract, -1, -2), -1, c.pc,
          decode(nvl2(gc.id_service_pid, -1, -2), -1, svc.pid,
            decode(nvl2(gc.id_ident, -1, -2), -1, i.prefix,
              gc.value_str))))
    ) as identification
  from 
    grp_cond gc, vsc_item_list vscil, vsc_grp_cond vscgc, 
    person cu, person py, service svc, contract c, ident i,
    vsc_flt_cond vscfc
  where 
    gc.id_c_item_list = vscil.id_c_item_list (+)
    and gc.id_c_grp_cond = vscgc.id_c_grp_cond
    and gc.id_person_customer = cu.id (+)
    and gc.id_person_payer = py.id (+)
    and gc.id_service_pid = svc.id (+)
    and gc.id_contract = c.id (+)
    and gc.id_ident = i.id (+)
    and gc.id_c_flt_cond = vscfc.id_c_flt_cond (+)
  order by
    identification

) WHERE ROWNUM <= 50

Open in new window

LVL 10
_Katka_Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_Katka_Author Commented:
Hi, roshmon. Thanks for the reply. I know that, but this isn't the case, at least not that obvious (that's what's problem here). It is 'ORA-00904: : invalid identifier' one. You can easily run this select from pl/sql navigator, or any other tool and it would work each and every time. It only doesn't work when run from C# thru ODP.NET (and only sometimes). But unluckily Oracle seems to record only successful selects. Thus we cannot determine what's wrong.

regards,
Kate
0
HainKurtSr. System AnalystCommented:
make sure the compared fields are same format... (if one is char the other one is number, or text & date, it may create problem)
 gc.id_c_item_list = vscil.id_c_item_list(+)
 gc.id_c_grp_cond = vscgc.id_c_grp_cond
 gc.id_person_customer = cu.id(+)
 gc.id_person_payer = py.id(+)
 gc.id_service_pid = svc.id(+)
 gc.id_contract = c.id(+)
 gc.id_ident = i.id(+)
 gc.id_c_flt_cond = vscfc.id_c_flt_cond(+)

Open in new window

0
_Katka_Author Commented:
Ok, thanks for the suggestion. I will check that, but it should all be just id's (as in numbers), but I'll check that again. Are you sure it would work inside navigator, and wouldn't from within C# ?

regards,
Kate
0
_Katka_Author Commented:
We solved it by encapsulating it with view. Mediocre solution, but it works for us so far.

regards,
Kate
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.