This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Aug 6, 2012 5:14 AM by Hoek RSS

How can I load data into a table from .ldr file?

BS2012 Newbie
Currently Being Moderated
Hi All,
Please tell me how can I load data into a table from *.ldr* file? How exactly I can use such files to run in loader?


Regards,
Bibhuprasad.S
  • 1. Re: How can I load data into a table from .ldr file?
    asahide Expert
    Currently Being Moderated
    Use sqlldr tool.
  • 2. Re: How can I load data into a table from .ldr file?
    BS2012 Newbie
    Currently Being Moderated
    Hi Ash,
    That's fine but how can I do that? Do I need to call the file name or what? Do I need to create a .ctl file or what's the process.

    Regards,
    Bibhuprasad.
  • 3. Re: How can I load data into a table from .ldr file?
    Hoek Guru
    Currently Being Moderated
    Search the documentation @ http://www.oracle.com/pls/db102/homepage or http://www.oracle.com/pls/db112/homepage
    Or go to the appropriate forum @ Export/Import/SQL Loader & External Tables
    Of even better: use an external table, SQL*Loader is soooooo 1980...
    http://www.oracle-base.com/articles/9i/external-tables-9i.php
  • 5. Re: How can I load data into a table from .ldr file?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Hoek wrote:

    Of even better: use an external table, SQL*Loader is soooooo 1980...
    Disagree with that statement.

    For example, how do you load a 60+ files/minute (in parallel) when with an external table a DDL statement (exclusive data dictionary lock) is needed to issue an alter table in order to set location to the file to load?

    SQL*Loader is superior in a number of ways to using the SQL*Loader module via an external table.
  • 6. Re: How can I load data into a table from .ldr file?
    Hoek Guru
    Currently Being Moderated
    Well, you can always list multiple filenames....but I'm pretty sure you know that as well...so perhaps I'm missing your point.
    I've always found external tables far more superior and flexible than SQL*Loader.
    Might be from a more 'Developer'point of view, whereas you tend to have a more 'DBA-ish' perspective ;)

    Edited by: Hoek on Aug 6, 2012 10:03 AM typo's (not @ desired coffee-level yet)
  • 7. Re: How can I load data into a table from .ldr file?
    BS2012 Newbie
    Currently Being Moderated
    Hi Billy and All,
    That is an awsome article and really helpful. But my question is if the exported data file format is in *.ldr* then what should I do to run that file from command line because I guess *"creating .ctl file is meaning less here."* So how should I invoke that file from command line? Please correct me if I am wrong?


    Regards,
    Bibhuprasad

    Edited by: Bibhuprasad on Aug 6, 2012 2:51 PM
  • 8. Re: How can I load data into a table from .ldr file?
    BluShadow Guru Moderator
    Currently Being Moderated
    What is the format of your .ldr file? Just giving us a file extension doesn't demonstrate the format of the data within it.

    Just quick google and the following possibilities are found...

    >
    The LDR file type is primarily associated with 'LDraw' by Estate of James Jessiman. LDraw is an open standard for LEGO CAD programs that allow the user to create virtual LEGO models and scenes.
    >

    >
    Other applications associated with file type LDR:
    •Symantec Ghost (Template File) by Symantec Corporation
    •WindLDR by IDEC Corporation, .LDR extension is a data file used for Applications developed by IDEC Corp. with their software WINLDR ,
    •Yamaha (Live Drum Kits) by Yamaha Corporation of AmericaThis file type is used by the Tyros2 and other high-end Yamaha keyboards. This association is classified as Audio.
    >

    So, you tell us what format it is. Perhaps give us an example of the data format.
  • 9. Re: How can I load data into a table from .ldr file?
    BS2012 Newbie
    Currently Being Moderated
    Hi Blu,
    The format is *".ldr"* and here is an example of that kind of file gbs_employee_tbl_data.ldr. If you export data from SQL developer with loader format then, the file will be created in *".ldr"* extension.


    Regards,
    Bibhuprasad
  • 10. Re: How can I load data into a table from .ldr file?
    BluShadow Guru Moderator
    Currently Being Moderated
    Bibhuprasad wrote:
    Hi Blu,
    The format is *".ldr"*
    No, that's a file extension not a format. As I demonstrated above, the file extension can relate to many different file formats depending on who or what generates it.
    and here is an example of that kind of file gbs_employee_tbl_data.ldr.
    That's not a file. It's a filename. Pointless on the forum and demonstrates nothing to us.
    If you export data from SQL developer with loader format then, the file will be created in *".ldr"* extension.
    Ok, so it's a file generated by SQL Developer. I don't have SQL Developer and neither will many people. Is it too hard for you to put a sample of some data on the forums for people to see that actual format of the data? You really are making this hard for people to help you. If you want help, you have to give sufficient information so that people can see what you are trying to do...

    {message:id=9360002}
  • 11. Re: How can I load data into a table from .ldr file?
    Karthick_Arp Guru
    Currently Being Moderated
    Bibhuprasad wrote:
    Hi All,
    Please tell me how can I load data into a table from *.ldr* file? How exactly I can use such files to run in loader?


    Regards,
    Bibhuprasad.S
    When you create a ".ldr" file using SQL Loader it will also generate a ".ctl" file, This is the control file that you will be using with the SQL Loader tool.

    ".ldr" is just a Delimited file format and can also be used with external table.
  • 12. Re: How can I load data into a table from .ldr file?
    BS2012 Newbie
    Currently Being Moderated
    Hi Karthik and All,
    Please do have a look at this. If you open the *".ldr"* file, it looks like this.....then what should I do in command line to use this file.....

    LOAD DATA
    INFILE *
    TRUNCATE
    CONTINUEIF NEXT(1:1) = '#'
    INTO TABLE "SSPG_PROD"."GBS_EMPLOYEE_TBL"
    FIELDS TERMINATED BY'|'
    OPTIONALLY ENCLOSED BY '"' AND '"'
    TRAILING NULLCOLS (
    EID ,
    FIRST_NAME ,
    LAST_NAME ,
    STATUS ,
    EMAIL ,
    COMPANY_CODE ,
    COMPANY_ADDRESS ,
    COMPANY_ADDRESS2 ,
    COMPANY_ADDRESS3 ,
    COMPANY_ADDRESS4 ,
    CITY ,
    COUNTRY ,
    BAND ,
    STATE ,
    BUSINESS_TITLE ,
    SBG_ID ,
    SBU_ID ,
    SBE_ID ,
    JOB_FUNCTION ,
    DEPARTMENT_ID ,
    DEPARTMENT_DESP ,
    HIRE_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    TERMINATION_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    MANAGER_EID ,
    MANAGER_NAME ,
    MANAGER_EMAIL ,
    LID ,
    TRAINED_STATUS ,
    CERTIFIED_STATUS ,
    LOCATION_ID ,
    CURRENT_CODE_ID ,
    LATEST_TRAIN_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    NEW_FLAG ,
    LOCATION_NAME ,
    COMPANY_DESCRIPTION ,
    PLANT_CODE ,
    PLANT_DESCRIPTION ,
    STD_HOURS ,
    CENSUS_FUNCTION ,
    CENSUS_SUBFUNCTION ,
    CERT_TARGET_ID ,
    CERT_TARGET_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    ACQ_EFFEC_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    TARGET_TRAIN_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    TARGET_CERT_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    TARGET_DT_COMMENTS ,
    STACS_DT_COMMENTS ,
    ESCAPE_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    RESPONSIBLE ,
    MODIFIED_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    MODIFIED_BY ,
    CREATED_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    CREATED_BY ,
    PROMISE_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    TIME_WATCH_COMMENTS ,
    TIME_WATCH_STARTED_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    TIME_WATCH_END_DATE DATE "DD-MON-RR HH24:MI:SS" ,
    AUTO_EMAIL_TRACK_COMMENT ,
    PD_TRACK_COMMENT )
    begindata
    "E294720"|"Vinayak"|"Shenoy"|"T"|"Disabled_Vinayak.Shenoy@ABCDl.com"|"IE11"|"151/1, Doraisanipalya,"|"Bannerghatta Road"|||"Bangalore"|"IND"|3|"10"|"Senior Credit Analyst"|4|324|3110||"IH1033"|"GCTS2"|02-FEB-05 00:00:00|27-NOV-07 00:00:00|"E213864"|"Archana,Kunde"|"Archana.Kunde@abcd.com"|"IE11"|1|0|101|13|20-APR-05 00:00:00|0|"ABCD India, Bangalore"|"ABCD India, Bangalore"||||||3|06-MAY-09 19:30:20||06-MAY-11 19:30:20|06-MAY-11 19:30:20|||06-MAY-11 19:30:20||24-NOV-11 18:33:13|"Updated through Cronjob"|06-MAY-09 19:30:20|"Updated through Cronjob"|||||||

    Edited by: Bibhuprasad on Aug 6, 2012 5:35 PM
  • 13. Re: How can I load data into a table from .ldr file?
    Hoek Guru
    Currently Being Moderated
    Are you aware that you're posting emailaddresses? You might want to edit them for security/privacy reasons.
  • 14. Re: How can I load data into a table from .ldr file?
    BS2012 Newbie
    Currently Being Moderated
    Hi All,
    normally we do like this, please correct me if I am wrong.

    sqlldr username/password control=sample.ctl log=sample.log;

    So if your filename extension is like *".ldr"*, what needs to be done here?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points