DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQLLDR-providing values explicitly

  1. #1
    Join Date
    May 2009
    Posts
    2

    SQLLDR-providing values explicitly

    Hi,

    I am trying to upload data to a table using sqlldr. It works fine if i specify all the column values in .txt file, but if i specify explicitly in the control file its not working

    LOAD DATA
    INFILE 'example.txt'
    DISCARDFILE 'example.dsc'
    APPEND
    INTO TABLE test_tab
    FIELDS TERMINATED BY ','
    (empid ,
    ename "acb",
    sal constant'1',
    job "bbc")

    my example.txt file goes like this
    1,
    2,
    3,
    4
    I am getting the following err
    Record 1: Rejected - Error on table TEST_TAB, column JOB.
    ORA-00984: column not allowed here

    Record 2: Rejected - Error on table TEST_TAB, column JOB.
    ORA-00984: column not allowed here

    Record 3: Rejected - Error on table TEST_TAB, column JOB.
    ORA-00984: column not allowed here

    Record 4: Rejected - Error on table TEST_TAB, column JOB.
    ORA-00984: column not allowed here

    I am expecting the following result
    empid ename sal job
    1 acb 1 bbc
    2 acb 1 bbc
    3 acb 1 bbc
    4 acb 1 bbc

    Plz suggest

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    desc TEST_TAB please.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Order in the...

    You have to order the columns (fields) in the controlfile the same order as in the source file and THEN supply the constants.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    May 2009
    Posts
    2
    Quote Originally Posted by PAVB View Post
    desc TEST_TAB please.
    SQL> desc test_tab
    Name Null? Type
    ----------------------------------------- -------- -----------------
    EMPID NOT NULL NUMBER(5)
    ENAME NOT NULL VARCHAR2(10)
    SAL NOT NULL NUMBER(10)
    JOB NOT NULL VARCHAR2(10)

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Thumbs down Bad boy, BAD boy...SIT!

    Try this:
    Code:
    LOAD DATA
    INFILE 'example.txt'
    DISCARDFILE 'example.dsc'
    APPEND INTO TABLE test_tab
    FIELDS TERMINATED BY ',' TRAILING NULLCOLS
    ( empid
    , ename CONSTANT 'abc'
    , sal   CONSTANT '1'
    , job   CONSTANT 'bbc')
    "The person who says it cannot 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
  •  


Click Here to Expand Forum to Full Width