This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Sep 4, 2012 8:53 AM by 881285 Go to original post RSS
  • 15. Re: DATA MERGING
    sb92075 Guru
    Currently Being Moderated
    Nii Moi wrote:
    help me out with the loader then cuz i have no idea what u are talking of
    COMMIT;
    might make things better
  • 16. Re: DATA MERGING
    Etbin Guru
    Currently Being Moderated
    This example about using [url http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#ADMIN11705]external tables is pretty straightforward.

    Regards

    Etbin
  • 17. Re: DATA MERGING
    William Robertson Oracle ACE
    Currently Being Moderated
    As others have suggested, use an external table + the MERGE command unless there is some important reason why you can't.

    If you can't use an external table - because you don't have access to the database server for example - then consider SQL*Loader, which is part of the Oracle Client software (and also available on the server). However this will require creation of a separate staging table and a slightly more complicated approach, as it's a command-line tool that you can't run from within PL/SQL.
  • 18. Re: DATA MERGING
    881285 Newbie
    Currently Being Moderated
    thanks you all for your support



    CREATE TABLE PUBLICSUPPORT_ext_table
    (
    ITEM VARCHAR2(200),
    QUANTITY NUMBER ,
    PLAN_FMLE NUMBER,
    PLAN_MLE NUMBER ,
    ACTUAL_FMLE NUMBER,
    ACTUAL_MLE NUMBER ,
    COST NUMBER ,
    COST_SPNT NUMBER ,
    COMP_STATUS VARCHAR2(200),
    TECH VARCHAR2(200),
    PHARVERST VARCHAR2(200),
    EXCERT VARCHAR2(200),
    START_DATE DATE,
    END_DATE DATE,
    REGIONCODE VARCHAR2(10),
    DISTRICTCODE VARCHAR2(10),
    DEZONE VARCHAR2(20),
    OPERATIONAL_AREA VARCHAR2(100),
    COMMUNITY VARCHAR2(100))
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY DR
    ACCESS PARAMETERS
    (records delimited by newline
    fields terminated by ','
    optionally enclosed by '"'
    missing field VALUES are NULL
    (
    ITEM,
    QUANTITY,
    PLAN_FMLE,
    PLAN_MLE,
    ACTUAL_FMLE,
    ACTUAL_MLE,
    COST,
    COST_SPNT,
    COMP_STATUS,
    TECH,
    PHARVERST,
    EXCERT,
    "START_DATE" DATE "mm dd yyyy",
    "END_DATE" DATE "mm dd yyyy",
    REGIONCODE,
    DISTRICTCODE,
    DEZONE,
    OPERATIONAL_AREA,
    COMMUNITY))
    LOCATION ('PUBLICSUPPORT.csv')) REJECT LIMIT UNLIMITED;
1 2 Previous Next

Legend

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