Results 1 to 11 of 11

Thread: sqlloader

  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Red face Unanswered: sqlloader

    hi,
    I'm using sqlloader on windows 2000 and I try to load data
    from file into table.
    I want to commit only at the end of transition,
    so the parameter specified in the control file are:
    options (BINDSIZE=12557648, ROWS=66796)
    The result is the follow message:
    "specified value for readsize(1048576) less than bindsize(12557648)".
    If I try to specify READSIZE=12557648 it seems that nothing
    happen.
    Someone could help me?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    You don't need to specify either.
    Specify ERRORS=1 and DIRECT=FALSE and then it won't commit until the end.
    If it hits an error, then nothing will be loaded.
    No errors = all loaded

    LOAD default is ALL RECORDS

    from Docs:
    Code:
        userid -- ORACLE username/password           
       control -- Control file name                  
           log -- Log file name                      
           bad -- Bad file name                      
          data -- Data file name                     
       discard -- Discard file name                  
    discardmax -- Number of discards to allow          (Default all)
          skip -- Number of logical records to skip    (Default 0)
          load -- Number of logical records to load    (Default all)
        errors -- Number of errors to allow            (Default 50)
          rows -- Number of rows in conventional path bind array or between
    direct path data saves
                   (Default: Conventional path 64, Direct path all)
      bindsize -- Size of conventional path bind array in bytes  (Default 256000)
        silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
        direct -- use direct path                      (Default FALSE)
       parfile -- parameter file: name of file that contains parameter specifications
      parallel -- do parallel load                     (Default FALSE)
          file -- File to allocate extents from      
    skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
    skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default 
    FALSE)
      readsize -- Size of Read buffer                  (Default 1048576)
    external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_ 
    USED)
    columnarrayrows -- Number of rows for direct path column array  (Default 5000)
    streamsize -- Size of direct path stream buffer in bytes  (Default 256000)
    multithreading -- use multithreading in direct path  
     resumable -- enable or disable resumable for current session  (Default FALSE)
    resumable_name -- text string to help identify resumable statement 
    resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
    date_cache -- size (in entries) of date conversion cache  (Default 1000)
    http://download-west.oracle.com/docs...96652/ch04.htm
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    4

    Red face

    I 've try to specify ERRORS=1 and DIRECT=FALSE
    without values of BINDSIZE and ROWS.
    It commits every 64 recors and not at the end of file.
    If I try to specify BINDSIZE or / and ROWS the old error
    is displayed and data are not loaded.
    Do you have any other suggestion?
    thank you

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    post your controlfil, logfile, and command-prompt output.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    This is because the READSIZE and the BINDSIZE is set differently from the other.

    You need to change the values so that BINDSIZE=READSIZE.

    When specifying the BINDSIZE or READSIZE, if the values are not equal, the higher value is selected for both and a message is generated.
    SATHISH .

  6. #6
    Join Date
    Dec 2003
    Posts
    4
    control file:

    options (BINDSIZE=12557648, ROWS=66796)
    load data
    infile 'D:\WORK\DWPS06.TXT'
    badfile 'D:\BAD\DWPS06_20040107205627.TXT'
    append into table SS_INTERFACE
    (
    PRG_REC "SEQ_PRG_REC.NEXTVAL",
    DT_ELAB SYSDATE,
    ESITO_ELAB CONSTANT 'DE',
    TP_OPER POSITION(1:1),
    CD_FILE CONSTANT '06',
    DT_DOC POSITION(2:9),
    CD_SOC POSITION(10:13),
    CD_TST_MADRE POSITION(14:16),
    DT_USC POSITION(17:24),
    CD_CMPNT POSITION(25:27),
    NUM_CMPNT POSITION(28:35),
    FLAG_EST POSITION(36:36) "NVL(:FLAG_EST, ' ')",
    PAGNT_OMG_SCR POSITION(37:37) "NVL(:PAGNT_OMG_SCR, ' ')",
    CD_CNL POSITION(38:39) "NVL(:CD_CNL, ' ')",
    CD_POLO POSITION(40:41) "NVL(:CD_POLO, ' ')",
    CD_EDZ POSITION(42:44) "NVL(:CD_EDZ, ' ')",
    CD_PRV POSITION(45:47) "NVL(:CD_PRV, ' ')",
    CD_ZONA_ISP POSITION(48:49) "NVL(:CD_ZONA_ISP, ' ')",
    NUM_COPIE_SPE POSITION(50:60),
    NUM_COPIE_TAGL POSITION(61:71),
    NUM_COPIE_RESO POSITION(72:82),
    PER_CPRT_RESO POSITION(83:89),
    FLAG_PUBB POSITION(90:94),
    NUM_COPIE_PREN POSITION(95:105),
    CD_UTE_INS CONSTANT 'INT-DIFF',
    CD_APPL CONSTANT 'INTER-DIFF'
    )


    command-prompt output:

    SQL*Loader: Release 8.1.7.0.0 - Production on Mer Gen 7 20:56:40 2004

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    specified value for readsize(1048576) less than bindsize(12557648)
    _

    thank you

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    read post by Satish
    options (BINDSIZE=12557648, READSIZE=12557648, ROWS=66796)

    why are you even specifying the bindsize anyways?
    did it not work when you specified nothing?

    I normally use this and have never had problems
    PHP Code:
    OPTIONS
    SKIP 0,
      
    ERRORS 1000,
      
    ROWS 66796,
      
    DIRECT FALSE,
      
    PARALLEL FALSE

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    IS it working fine now?
    SATHISH .

  9. #9
    Join Date
    Dec 2003
    Posts
    4
    If I try to use the option you suggested
    OPTIONS
    ( SKIP = 0,
    ERRORS = 1000,
    ROWS = 66796,
    DIRECT = FALSE,
    PARALLEL = FALSE
    )
    result is a commit every 348 recors and not at the end of file:

    SQL*Loader: Release 8.1.7.0.0 - Production on Gio Gen 8 09:46:23 2004

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    Punto di commit raggiunto - conteggio di record logici 348
    Punto di commit raggiunto - conteggio di record logici 696
    Punto di commit raggiunto - conteggio di record logici 1044
    Punto di commit raggiunto - conteggio di record logici 1392
    and so on...

    using:
    options (BINDSIZE=12557648, READSIZE=12557648, ROWS=66796)
    nothing happen waiting 15 minuts.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    Okay, you are right.
    After messing around with this I came upon something REALLY buggy.

    Look at this:
    Code:
    C:\jayson\weather>sqlldr platform/password@kod1 control=load_wa_zips.ctl READSIZE=1000000 BINDSIZE=1000000
    
    SQL*Loader: Release 9.2.0.1.0 - Production on Thu Jan 8 12:10:36 2004
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Commit point reached - logical record count 718
    now look at my log file!!
    PHP Code:
    Record 704Rejected Error on table TESTcolumn T_DATE.
    ORA-01861literal does not match format string


    MAXIMUM ERROR COUNT EXCEEDED 
    Above statistics reflect partial run.

    Table TEST:
      
    0 Rows successfully loaded.
      
    1 Row not loaded due to data errors.
      
    0 Rows not loaded because all WHEN clauses were failed.
      
    0 Rows not loaded because all fields were null.


    Space allocated for bind array:                 999799 bytes(1919 rows)
    Read   buffer bytes1000000

    Total logical records skipped
    :          0
    Total logical records read
    :           718
    Total logical records rejected
    :         1
    Total logical records discarded
    :        0

    Run began on Thu Jan 08 12
    :10:36 2004
    Run ended on Thu Jan 08 12
    :10:36 2004

    Elapsed time was
    :     00:00:00.31
    CPU time was
    :         00:00:00.07 
    It says it loaded ZERO rows.
    But when I count the table:
    Code:
    12:10:27 kod:platform>  select count(*) from test;
    
      COUNT(*)
    ----------
           703

    WTF?!?!?!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace.
    SATHISH .

Posting Permissions

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