Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Weird, can someone explain as to why this happened ?

Re: Weird, can someone explain as to why this happened ?

From: Geoffrey Bray <gbray1_at_my-deja.com>
Date: 2000/09/20
Message-ID: <8qb1hd$asn$1@nnrp1.deja.com>#1/1

Could be a number of reasons ... but the most likely is that the table got created with the column names in quotes and hence they are case sensitive. Typically Oracle ignores case on table and column names (not really ... it internally forces everything to upper when not in quotes and resolves that way) but the case where it doesn't is when it is quoted. The most common case of this I've seen is when you're using some kind of ODBC tool to create the database. It can be easily demonstrated as below.

>create table junk2 (name varchar2(30), "Age" number(3));
>desc junk2;

 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                                     VARCHAR2(30)
 Age                                      NUMBER(3)

>insert into junk2 values ('GB', 34);
>select * from junk2;

NAME                                  Age
------------------------------ ----------
GB                                     34

>select age from junk2;

select age from junk2

       *
ERROR at line 1:
ORA-00904: invalid column name

>select name from junk2;

NAME



GB

In article <39c1dc40.5077877_at_news.iinet.net.au>,   mechhunter_at_rocketmail.com (Arthur) wrote:
> SQL> select * from emp_vu;
>
> empno Employee Deptno
> --------- ---------- ---------
> 7369 SMITH 20
> 7499 ALLEN 30
> 7521 WARD 30
> 7566 JONES 20
> 7654 MARTIN 30
> 7698 BLAKE 30
> 7782 CLARK 10
> 7788 SCOTT 20
> 7839 KING 10
> 7844 TURNER 30
> 7876 ADAMS 20
> 7900 JAMES 30
> 7902 FORD 20
> 7934 MILLER 10
>
> 14 rows selected.
>
> After creating the view - I did a select via the view
> and this is the result.
>
> SQL> select employee from emp_vu;
> select employee from emp_vu
> *
> ERROR at line 1:
> ORA-00904: invalid column name
>
> I got fed up and recreate the view and this time, it worked.
> Why would the error occur in the first place ? I had everything
> correct, or so I hope - can someone shed some light into
> this please ?? So next time it happens, I will know why.
>
> Arthur
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 20 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US