Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    23

    Unanswered: variables with SQLLOADER

    Hi

    I have a process whereby I pick up a file from an FTP server. I would like to pass a variable file name to a control file and also strip out another piece of information from the file name to use to load as a value to the oracle table. Is it possible to
    - pass variables to a control file
    - reference variables within a control file
    - pass variables as values to the table

    I would appreciate any information on the feasibility of this and any examples if it is possible

    many thanks Kerrie

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    the only answer I ever found regarding this was to dynamically create
    the control file using sql-plus since you can pass parameters into sqlplus.

    I hadn't yet figured out how to get a control file to load the params I
    was passing in using a shell script.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Hey Duck ...
    I attached a script file to dynamically creat a controlfile on a prior
    post (duplicate post). Maybe it'll help

    Gregg

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    this solved a long-time problem I had.
    Since I am on east coast time (EST and EDT dending on the date)
    we store all our dates in the db as GMT.

    I use sqlloader to load the data but couldn't figure out how to pass
    in the timezone from the shell script into the sqlloader file.

    by editing your handy script it was a piece of cake.

    for the curious:
    code:
    PHP Code:
    SELECT
    'LOAD DATA'||chr(10)
    ||
    'INFILE ''$REFRESH/pricing/good_data.lst'' '||chr(10)
    ||
    'BADFILE ''$REFRESH/pricing/load_pricing.bad'' '||chr(10)
    ||
    'DISCARDFILE ''$REFRESH/pricing/load_pricing.dis'' '||chr(10)
    ||
    'APPEND'||chr(10)
    ||
    'INTO TABLE MARKET_PRICE'||chr(10)
    ||
    'FIELDS TERMINATED BY '','' optionally enclosed by ''"'' '||chr(10)
    ||
    'TRAILING NULLCOLS'||chr(10)
    ||
    '('||chr(10)
    ||
    'dummy                FILLER,'||chr(10)
    ||
    'MKT_LOCTN_ID            CHAR,'||chr(10)
    ||
    'MKT_PRICE_DT            "new_time(to_date(:MKT_PRICE_DT,''YYYY-MM-DD HH24:MI:SS''),''&1'',''GMT'')",'||chr(10)
    ||
    'MKT_PRCNG_TYPE_CD    constant ''5M'','||chr(10)
    ||
    'ENERGY_NBR              CHAR,'||chr(10)
    ||
    'CNGSTN_NBR              CHAR,'||chr(10)
    ||
    'MRGNL_LS_NBR            CHAR,'||chr(10)
    ||
    'LMP_NBR                 CHAR,'||chr(10)
    ||
    'ENERGY_MKT_CD           constant ''NEISO'''||chr(10)
    ||
    ')'
    FROM dual
    allowed me to pass in the timezone to be converted to GMT.
    output:
    PHP Code:
    LOAD DATA                                                                             
    INFILE 
    '$REFRESH/pricing/good_data.lst'                                               
    BADFILE '$REFRESH/pricing/load_pricing.bad'                                           
    DISCARDFILE '$REFRESH/pricing/load_pricing.dis'                                       
    APPEND                                                                                
    INTO TABLE MARKET_PRICE                                                               
    FIELDS TERMINATED BY 
    ',' optionally enclosed by '"'                                   
    TRAILING NULLCOLS                                                                     
    (                                                                                     
    dummy    FILLER,                                                                      
    MKT_LOCTN_ID   CHAR,                                                                  
    MKT_PRICE_DT   "new_time(to_date(:MKT_PRICE_DT,'YYYY-MM-DD HH24:MI:SS'),'EDT','GMT')",
    MKT_PRCNG_TYPE_CD constant '5M',                                                      
    ENERGY_NBR        CHAR,                                                               
    CNGSTN_NBR        CHAR,                                                               
    MRGNL_LS_NBR      CHAR,                                                               
    LMP_NBR           CHAR,                                                               
    ENERGY_MKT_CD     constant 'NEISO'                                                    


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

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Glad to be of service !!

Posting Permissions

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