RE: ORA-00904

From: Eugene Pipko <eugene.pipko_at_unionbay.com>
Date: Fri, 26 Jun 2009 09:17:53 -0700
Message-ID: <34DB87F47199374280ADFD2968CDBCFA87FE48E267_at_MAIL01KT.seattlepacificindustries.com>

Thanks for the script.
The reason I ran desc tableA_at_remote_db is to check why my package would not compile. Compilation times out and I figured out that it is because of the Delete statement (from the same table) over d/b link. So, my next test was to simply describe that table in remote d/b. It failed with ORA-00904.

Regards,

Eugene Pipko
Seattle Pacific Industries
office: 253.872.5243
cell: 206.304.7726
  Please consider the environment before printing this e-mail.

-----Original Message-----
From: Barbara Baker [mailto:barb.baker_at_gmail.com] Sent: Friday, June 26, 2009 8:59 AM
To: Eugene Pipko
Cc: oracle-l-freelists
Subject: Re: ORA-00904

I think I vaguely remember this being a bug in 8.1 I don't remember who I stole this from, but you can pop it into sql and try the describe this way. (Kind of overkill for "describe", but it works)

SET ECHO OFF
accept table_name prompt "Enter the name of the Table :" set heading on
set verify on
set newpage 0
spool descr.lst

btitle off
column nline newline
set pagesize 54
set linesize 132
set heading off
set embedded off
set verify off
accept owner_nam char prompt 'Enter table owner: ' ---accept report_comment char prompt 'Enter a comment to identify system: '

select 'Date -  '||to_char(sysdate,'Day Ddth Month YYYY     HH24:MI:SS'),
        'Username      -  '||USER  nline

from sys.dual
/
prompt
set heading on
set embedded off

column cn  format a25         heading "Column Name|Dflt Val"
column fo  format a25         heading 'Type'
column nu  format a8          heading 'Null'
column nds format 99,999,999  heading 'No|Distinct'
column dfl format 9999        heading 'Dflt|Len'
column dfv format a22         heading 'Default|Value'

---column dfv format a10 heading '' newline

ttitle 'Table Description - Column Definition' select COLUMN_NAME cn,

        DATA_TYPE ||
        decode(DATA_TYPE,
                'NUMBER',
                    '('||to_char(DATA_PRECISION)||
                        decode(DATA_SCALE,0,'',','||to_char(DATA_SCALE))||')',
                'VARCHAR2',
                    '('||to_char(DATA_LENGTH)||')',
                'DATE','',
                'CHAR','',
                'Error') fo,
        decode(NULLABLE,'Y','','NOT NULL') nu,
        NUM_DISTINCT nds,
        DEFAULT_LENGTH dfl,
        DATA_DEFAULT dfv

FROM all_tab_columns
where TABLE_NAME=UPPER('&&table_name')
and owner=UPPER('&&owner_nam')
order by COLUMN_ID
/

On Fri, Jun 26, 2009 at 9:49 AM, Eugene Pipko<eugene.pipko_at_unionbay.com> wrote:
> Hi all,
>
> I am getting following error: ORA-00904: "CHAR_LENGTH": invalid identifier
> while trying to run: desc tableA_at_remote_db from 9.2.0.8 to 8.1.7.4
>
> Everything I read so far suggests that one of the columns being Oracle
> reserved word, but not sure what this error message refers to as every
> column in destination table starts with “ST00_”
>
>
>
> Any suggestions?
>
>
>
>
>
> Regards,
>
>
>
> Eugene Pipko
>
> Seattle Pacific Industries
>
> office: 253.872.5243
>
> cell: 206.304.7726
>
> P  Please consider the environment before printing this e-mail.
>
>

��i��0���zX���+��n��{�+i�^ Received on Fri Jun 26 2009 - 11:17:53 CDT