• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2435
  • Last Modified:

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

0
_Katka_
Asked:
_Katka_
  • 3
1 Solution
 
_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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now