1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle data load - some basic help please!

Discussion in 'General' started by RobDickinson, May 3, 2012.

  1. RobDickinson

    RobDickinson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi!

    Need some quick help with some modern basics! Some background - I've used oracle a long time ago (ver6 or so) and used to use toad, sqlldr etc , as a developer (backend delphi datastorage etc).

    Now I've got a task to regularly load data into a customers oracle database. Our system is living on a windows 2008 server and will produce csv files formatted for load to holding tables in the oracle db.

    I currently dont have any info on oracle version no (though they are running financials) dont have an oracle client installed (on my own machine or the server etc, dont have a connection string.

    Now I assume I need an oracle client , including sqlldr (which isnt in the instant client). Or is there a more modern way of scheduling data load to oracle now (given the clients oracle 'experts' are anything but and both client and oracle guys are most unhelpful).

    Then I will use a windows schedule to run a batch file to check a folder , run sqlldr and copy the finished files somewhere else.

    Can I download a trial oracle db from oracle? and a Client install? So I can at least get started with this whole process..

    Thanks,
    Rob.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The client software is free for whatever use you see fit; it's available at www.oracle.com/downloads. It would help tremendously to know which Oracle release is being used however the 11.2 client should connect to any release running recent versions of Oracle financials. You can also download a recent release of Oracle as well, free of charge, as long as it's only you connecting to the database and it's for testing/development purposes. This is found at the same location as the client.

    It would appear that your old friend, sqlldr, is the tool of choice as the CSV files are not local to the database. So download away, create your database and test your scripts/batch files to get this task going. Come back should you have further quesitons.
     
  3. RobDickinson

    RobDickinson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thanks David.

    Have downloaded and installed 11.2 and setup a test db, loading files via sqlldr as we speak.

    Some testing is going to be require top make this robust...
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Feel free to post your scripts/batch files -- I, and others here, will be happy to help you fix any problems.
     
  5. RobDickinson

    RobDickinson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thanks, currently it is this:

    Batch file :
    for /f %%a in ('dir /b C:\temp\sqlldr\*.csv') do (
    sqlldr PARFILE=loaderAR.par data=%%a
    move %%a ./complete
    )

    loaderAR.par:
    USERID=user/password@ddatabase
    CONTROL=loader_AR.ctl
    ERRORS=9999
    LOG=fileARLOAD.log

    loader_AR.ctl:
    load data
    Append into table USERX.TABLENAME
    fields terminated by "," optionally enclosed by '"'
    (
    TRANS_NUMBER,
    LINE_OR_HEADER,
    INVOICE_TYPE ,
    CUSTOMER_NAME,
    CUSTOMER_NUMBER,
    INVOICE_NUMBER,
    INVOICE_DATE "to_date :)INVOICE_DATE, 'YYYY-MM-DD HH24:MI:SS')",
    INVOICE_AMOUNT,
    DESCRIPTION ,
    CURRENCY_CODE,
    CONVERSION_DATE "to_date :)CONVERSION_DATE, 'YYYY-MM-DD HH24:MI:SS')",
    CONVERSION_RATE,
    ACCOUNTING_DATE "to_date :)ACCOUNTING_DATE, 'YYYY-MM-DD HH24:MI:SS')",
    SEGMENT1,
    SEGMENT2,
    SEGMENT3,
    SEGMENT4,
    SEGMENT5,
    CREATION_DATE "to_date :)CREATION_DATE, 'YYYY-MM-DD HH24:MI:SS')",
    CREATED_BY,
    ATTRIBUTE12,
    ATTRIBUTE13,
    ATTRIBUTE14,
    ATTRIBUTE15,
    ATTRIBUTE16,
    ATTRIBUTE17,
    ATTRIBUTE18,
    POSTING_STATUS,
    POSTING_ERROR
    )


    I have 3 files I will need to batch/script up, they will all have their own names so can run 3 separate 'threads' for them.

    Just need to check results from the sql loader and put in some checking I think etc.
     
  6. RobDickinson

    RobDickinson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Ah.. oracle version is 10gR2 (10.2.0.3)
     
  7. RobDickinson

    RobDickinson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    OK I'm scripting a batch file around the sqlldr load..

    and I find that the sqlldr returns 0 ( EX_SUCC ) , i.e. successful even when the oracle listener is unavailable!
     
  8. RobDickinson

    RobDickinson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I'm also trying this script to attemtp to detect if the database is up:
    Code (SQL):
    rem -- Set Database SID ---
    SET ORACLE_SID=orcl

    rem  -- Create SQL Script ---
    echo CONNECT sys/orcl AS sysdba;         > %0.tmp
    echo SET echo off feed off pages 0      >> %0.tmp
    echo whenever sqlerror exit SQL.sqlcode >> %0.tmp
    echo SELECT * FROM global_name;         >> %0.tmp
    echo exit;                              >> %0.tmp

    rem -- Test connect to database ---
    sqlplus -s /nolog @%0.tmp >NUL:

    IF {%ERRORLEVEL%} == {0} (
       echo DATABASE %ORACLE_SID% IS available
    ) ELSE (
       echo DATABASE %ORACLE_SID% IS NOT available
    )

    rem -- Cleanup --
    del %0.tmp
    But it seems the errorlevel is always returned at 4 regardless of if it makes a correct connection or not (this with local 11r2 on windows7).