Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    5

    Unanswered: SQL LOADER error in Oracle 10G

    Hi All,
    I am getting sqlloader error in Oracle 10G. Works fine in 9i.

    The error is:
    SQL*Loader-297: Invalid syntax or bind variable in SQL string for column "CUSTOMER_MASTER_CODE".
    ORA-01756: quoted string not properly terminated


    Coressponding line in Control file is:
    "CUSTOMER_MASTER_CODE" POSITION (1:11) CHAR "SUBSTR(:CUSTOMER_MASTER_CODE,1,7)||'0000'",

    Please tell me what is wrong in 10g SQL Loader?

    Any help ?

    DB : Oracle 10.2.0.3
    OS : Sun 5.9

    Thanks in advance !
    Deeksha

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There should be a space character at the end of the line, between a single and a double quote:
    Code:
    CUSTOMER_MASTER_CODE POSITION (1:11) CHAR "SUBSTR(:CUSTOMER_MASTER_CODE,1,7) || '0000' "
    Compare:
    Code:
    Wrong: || '0000'"
    Right: || '0000' "

  3. #3
    Join Date
    Jan 2008
    Posts
    5
    Thanks for your reply.

    I made changes to the control file as you suggested and it loaded the file without any errors. But the CUSTOMER_MASTER_CODE is empty.


    Table structure:
    CUSTOMER_ACCOUNT_CODE VARCHAR2(20)
    CUSTOMER_MASTER_CODE VARCHAR2(20)

    Control file:
    "CUSTOMER_ACCOUNT_CODE" POSITION (1:11) CHAR ,
    "CUSTOMER_MASTER_CODE" POSITION (1:11) CHAR "(SUBSTR(:CUSTOMER_MASTER_CODE,1,7)||'0000' )",

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Is it? In my example, it seems to be OK:
    Code:
    C:\TEMP>type test.ctl
    load data
    infile *
    
    replace
    into table test
    ( CUSTOMER_MASTER_CODE POSITION (1:11) CHAR "SUBSTR(:CUSTOMER_MASTER_CODE,1,7) |
    | '0000' "
    )
    
    begindata
    12345678901abcdefgh
    abcdefghijk12345678
    
    
    C:\TEMP>sqlplus scott/Tiger
    
    SQL*Plus: Release 10.2.0.1.0 - Production on ╚et Sij 31 07:09:55 2008
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> desc test
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
    
     CUSTOMER_MASTER_CODE                               VARCHAR2(11)
    
    SQL> $sqlldr scott/Tiger control=test.ctl log=test.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on ╚et Sij 31 07:10:12 2008
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 3
    
    SQL> select * from test;
    
    CUSTOMER_MA
    -----------
    12345670000
    abcdefg0000
    
    SQL>
    Part of the 'test.log' file:
    Code:
    Record 3: Discarded - all columns null.
    
    Table TEST:
      2 Rows successfully loaded.
      0 Rows not loaded due to data errors.
      0 Rows not loaded because all WHEN clauses were failed.
      1 Row not loaded because all fields were null.
    Last edited by Littlefoot; 01-31-08 at 02:16.

  5. #5
    Join Date
    Jan 2008
    Posts
    5
    Thanks for your help.
    I was able to resolve the issue with oracle support. I installed the Patch number 4766696 to resolve the issue.

  6. #6
    Join Date
    May 2010
    Posts
    1
    I also have a smilar problem while loading with sqlldr it says commit point reached 64.. But even at that when i select on the table... Its responds that no row selected.. What a i to do? Thanks

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Check what's written into the log file - it will tell you why all the records have been rejected.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also please do not hijack an old issue, start a new thread.

    Thanks.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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