Re: error: SQL Loader-466 column not exist ??? help !!!!

From: <fitzjarrell_at_cox.net>
Date: Thu, 10 Jan 2008 12:59:36 -0800 (PST)
Message-ID: <d8c05dc5-fcef-4fbf-a3f2-d21706e31ce3@21g2000hsj.googlegroups.com>

On Jan 10, 2:36 pm, chijar <chi..._at_gmail.com> wrote:
> dear all.
> i have a problem with sql loader.
> The error is:
> SQL*Loader-466: Column NUM_PAQ does not exist in table PLANELEC.file
>
> i saw desc table:
> SQL> DESC PLANELEC.file
> Nombre ¿Nulo? Tipo
> ----------------------------------------- --------
> ----------------------------
> num_paq NOT NULL CHAR(8)
> formulario NOT NULL CHAR(4)
> norden NOT NULL NUMBER(38)
> cod_docide_dec NOT NULL NUMBER(38)
> num_docide_dec NOT NULL VARCHAR2(11)
> num_correl_a NOT NULL NUMBER(38)
> cod_docide_aseg NOT NULL NUMBER(38)
> num_docide_aseg NOT NULL VARCHAR2(15)
> cod_cat_tra NOT NULL NUMBER(38)
> cod_tipo NOT NULL NUMBER(38)
> fec_ini_perlab NOT NULL DATE
> fec_fin_perlab DATE
> cod_extincion CHAR(2)
> ind_envio NUMBER(38)
> fec_envio DATE
> num_ctl CHAR(6)
>
> ****************
> my ctl file is:
> Load DATA
> INFILE file.UNL
> INSERT
> INTO TABLE PLANELEC.file
> fields terminated by '|'
> TRAILING NULLCOLS
> (
> NUM_PAQ,FORMULARIO,NORDEN,COD_DOCIDE_DEC,NUM_DOCIDE_DEC,NUM_CORREL_A,COD_DO­CIDE_ASEG,
> NUM_DOCIDE_ASEG,COD_CAT_TRA,COD_TIPO,FEC_INI_PERLAB,FEC_FIN_PERLAB,COD_EXTI­NCION,
> IND_ENVIO,FEC_ENVIO,NUM_CTL
> )
>
> and the first line of the file.unl is:
> 00000000|0601|2000043|6|20100066603|1|1|90000001|1|20|01/02/2002|||0||
> 613954|
>
> what could be the problem with that?
>
> thanks a lot !
> cesar
>
> ORACLE 10GR2
> RHEL AS V4.0
Your table name isn't valid without enclosing it in "":

create table PLANELEC.file

                      *

ERROR at line 1:
ORA-00903: invalid table name

Therefore your control file for SQL*Loader must also list the table name enclosed in ""

Load DATA
INFILE file.UNL
INSERT
INTO TABLE "PLANELEC.file"
fields terminated by '|'
TRAILING NULLCOLS
(
NUM_PAQ,FORMULARIO,NORDEN,COD_DOCIDE_DEC,NUM_DOCIDE_DEC,NUM_CORREL_A,COD_DOCIDE_ASEG, NUM_DOCIDE_ASEG,COD_CAT_TRA,COD_TIPO,FEC_INI_PERLAB,FEC_FIN_PERLAB,COD_EXTI­ NCION,
IND_ENVIO,FEC_ENVIO,NUM_CTL
)

When properly configured SQL*Loader completes successfully:

SQL*Loader: Release 10.2.0.3.0 - Production on Thu Jan 10 14:52:07 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1

SQL> select *
  2 from "PLANELEC.file";

NUM_PAQ  FORM     NORDEN COD_DOCIDE_DEC NUM_DOCIDE_ NUM_CORREL_A
COD_DOCIDE_ASEG NUM_DOCIDE_ASEG COD_CAT_TRA   COD_TIPO FEC_INI_P
FEC_FIN_P CO  IND_ENVIO FEC_ENVIO NUM_CT
-------- ---- ---------- -------------- ----------- ------------

--------------- --------------- ----------- ---------- --------- --------- -- ---------- --------- ------

00000000 0601    2000043              6 20100066603
1               1 90000001                  1         20 01-
FEB-02                       0           613954

SQL> You should really use proper table naming convention in this database.

David Fitzjarrell Received on Thu Jan 10 2008 - 14:59:36 CST