Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    15

    Unanswered: SqlLoader Problem with column name

    Hi, this is my first post here on the forum, a great forum btw.

    My problem is that i cant load information into a field named DATA.
    The "DATA" fields means date in portuguese and i'm trying to find out some solution before rename the field in several tables.

    Can anyone help on this?
    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Can anyone help on this?
    Not without more information

    Show us your table definition (ideally a CREATE TABLE statement)
    Show us your SQL*Loader control file
    Show us the error message you get when you try to load the data

  3. #3
    Join Date
    Dec 2009
    Posts
    15
    Sorry for the thread with so little information,

    Here goes all the data about the problem:
    - Table Definition
    CREATE TABLE "MSTR_DATASOURCE"."TMP_BSC_F_JURIDICO"
    ( "ID_JURIDICO" NUMBER(8,0) NOT NULL ENABLE,
    "OE" NUMBER(8,0) NOT NULL ENABLE,
    "OO" NUMBER(8,0) NOT NULL ENABLE,
    "DIRECCAO" NUMBER(8,0) NOT NULL ENABLE,
    "DATA" NUMBER(8,0) NOT NULL ENABLE,
    "N_ESCLAR_PREST_MODO_EFECTIVO" NUMBER(5,0),
    "N_TT_ESCLAR_PREST" NUMBER(5,0),
    "N_PARECERES_ELABOR_PR" NUMBER(5,0),
    "N_TT_PARECERES" NUMBER(5,0),
    "N_PROC_CONTRA_ORDEN_CONC_PR" NUMBER(5,0),
    "N_TT_PROC_CONTRA_ORDEN" NUMBER(5,0),
    "DATA_CTRL" NUMBER(8,0) NOT NULL ENABLE
    )

    - SQL Loader ctl file
    LOAD DATA
    INFILE 'Formulario_GJC_20091214.csv'
    INTO TABLE TMP_BSC_F_JURIDICO
    FIELDS TERMINATED BY ','
    TRAILING NULLCOLS
    (
    ID_JURIDICO "SEQUENCE_ID_JURIDICO.nextval"
    , OE INTEGER EXTERNAL NULLIF (OE=BLANKS)
    , OO INTEGER EXTERNAL NULLIF (OO=BLANKS)
    , DIRECCAO INTEGER EXTERNAL NULLIF (DIRECCAO=BLANKS)
    , DATA INTEGER EXTERNAL NULLIF (DATA=BLANKS)
    , N_ESCLAR_PREST_MODO_EFECTIVO INTEGER EXTERNAL NULLIF (N_ESCLAR_PREST_MODO_EFECTIVO=BLANKS)
    , N_TT_ESCLAR_PREST INTEGER EXTERNAL NULLIF (N_TT_ESCLAR_PREST=BLANKS)
    , N_PARECERES_ELABOR_PR INTEGER EXTERNAL NULLIF (N_PARECERES_ELABOR_PR=BLANKS)
    , N_TT_PARECERES INTEGER EXTERNAL NULLIF (N_TT_PARECERES=BLANKS)
    , N_PROC_CONTRA_ORDEN_CONC_PR INTEGER EXTERNAL NULLIF (N_PROC_CONTRA_ORDEN_CONC_PR=BLANKS)
    , N_TT_PROC_CONTRA_ORDEN INTEGER EXTERNAL NULLIF (N_TT_PROC_CONTRA_ORDEN=BLANKS)
    , DATA_CTRL INTEGER EXTERNAL NULLIF (DATA_CTRL=BLANKS)
    )

    - Error Message:
    SQL*Loader-350: Erro de sintaxe na linha 11.
    Esperava inteiro positivo ou nome da coluna, encontrou Palavra chave data.
    , DATA INTEGER EXTERNAL NULLIF (DATA=BLAN

    translating the error message: Waiting for a positive integer or column name, found reserved word DATA
    the error points to asteristc DATA*=BLANK

    Thank you shammat for answering so fast

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Did you try to quote DATA in the controlfile?
    Code:
    "DATA" INTEGER EXTERNAL NULLIF ("DATA"=BLANKS)
    I don't know if that works, but it's worth trying.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you could try enclosing the column name in double quotes:
    Code:
    , "DATA" INTEGER EXTERNAL NULLIF ("DATA"=BLANKS)
    or using the relative notation:
    Code:
    , "DATA" INTEGER EXTERNAL NULLIF ((*)=BLANKS)
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is always a BAD idea to use a RESERVED WORD/KEYWORD as a column name.

    Code:
    SQL> desc v$reserved_words
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     KEYWORD					    VARCHAR2(30)
     LENGTH 					    NUMBER
     RESERVED					    VARCHAR2(1)
     RES_TYPE					    VARCHAR2(1)
     RES_ATTR					    VARCHAR2(1)
     RES_SEMI					    VARCHAR2(1)
     DUPLICATE					    VARCHAR2(1)
    SELECT COUNT(*) FROM V$RESERVED_WORDS WHERE KEYWORD = 'DATA';

    If COUNT = 1, then do not use this label.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2009
    Posts
    15
    Quote Originally Posted by shammat View Post
    Did you try to quote DATA in the controlfile?
    Code:
    "DATA" INTEGER EXTERNAL NULLIF ("DATA"=BLANKS)
    I don't know if that works, but it's worth trying.
    I was trying that, but i tried quote only on the nullif and that was not working, quoting on both sides worked perfectly!
    Thank you very much

  8. #8
    Join Date
    Dec 2009
    Posts
    15
    Quote Originally Posted by anacedent View Post
    It is always a BAD idea to use a RESERVED WORD/KEYWORD as a column name.

    Code:
    SQL> desc v$reserved_words
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     KEYWORD					    VARCHAR2(30)
     LENGTH 					    NUMBER
     RESERVED					    VARCHAR2(1)
     RES_TYPE					    VARCHAR2(1)
     RES_ATTR					    VARCHAR2(1)
     RES_SEMI					    VARCHAR2(1)
     DUPLICATE					    VARCHAR2(1)
    SELECT COUNT(*) FROM V$RESERVED_WORDS WHERE KEYWORD = 'DATA';

    If COUNT = 1, then do not use this label.
    Thanxs for the tip, i will use it.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Effective date maybe?

    Quote Originally Posted by anacedent View Post
    It is always a BAD idea to use a RESERVED WORD/KEYWORD as a column name.
    ... etc ...
    I completely agree and suggest you actually change the name in the tables now that you have the oportunity and thus avoid problems down the line.

    A standard way to use reseved word/keywords in column names is just to prefix or suffix the word with some meaningfull text, kinda like: DATA_EFF (effective date?) or so.

    My 2c.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •