Re: ORA-00904: invalid column name

From: Joao Prates <jmmp_at_esoterica.pt>
Date: Fri, 20 Aug 1999 02:08:29 +0100
Message-ID: <935114997.631643@gorgoroth.esoterica.pt>

Thanks Kyte, I already solved the problem. However, the nature of it is so obscure that I think I should post this explanation:

I was not using dynamic SQL. Of course if the column name was a string Oracle could not see that and it could not validate that at compile time but only at run time. But as I said, that was not the problem. After a lot of dbms_output.put_line I finally found that the offending command was this one:

CURSOR c_ven_dir_inv IS

   SELECT crr_codigo,
          vdn_num_con_nac_pro_dec,
          cam_ano_ini_cam,
          vdn_ini_per
    FROM  qlt_ven_dir_inv_99
    WHERE crr_codigo              =       '0007'
     AND  cam_ano_ini_cam         =       i_cam_ano_ini_cam
     AND  aju_codigo              =       i_aju_codigo
     AND  vdn_num_con_nac_pro_dec BETWEEN i_lim_inf AND i_lim_sup

   FOR UPDATE OF crr_cod_rej;

The problem being that as soon as I opened the cursor... ORA-00904. After a careful look at all column names at the cursor declaration, I noticed a mistake: column crr_cod_rej stated as being to be updated does not exist. In fact it should be crr_codigo, I made a typing mistake.

Now this is really weird!!!! I tried to change every other single column in the select list and in the where clause, and all changes were detected at compile time and produced a compile error. BUT the for update clause CAN TAKE ANY GARBAGE that Oracle does NOT check it until run time!!!! HOW CAN THIS BE????? Be careful when declaring your FOR UPDATE OF clause, Oracle accepts anything in there, even your dog name! Hope this can help anyone out there in the future. Oracle is in fault here I guess...

Thank you,

Joao Prates

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:37bc0bf9.269684686_at_newshost.us.oracle.com...
> A copy of this was sent to "Joao Prates" <jmmp_at_esoterica.pt>
> (if that email address didn't require changing)
> On Thu, 19 Aug 1999 00:36:57 +0100, you wrote:
>
> >Hi,
> >
> >I'm getting error "ORA-00904: invalid column name" when running a stored
> >procedure that compiled ok. Anyone can explain what can be causing this
> >error? If a column name were in fact invalid shouldn't the compiler
detect
> >it at compile time? I checked the tables structure and there was no
> >modification since compile time, even so I recompiled it again and the
> >problem remains only at run time. Any ideas?
> >
> >Thank you in advance,
> >
> >Joćo Prates
>
> are you using dynamic sql?
> what does the procedure look like?
> how are you running it?
>
>
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation

Received on Thu Aug 19 1999 - 20:08:29 CDT