1

I need to use Oracle loader to load data to a table, but I am unable to handle how the file needs to be picked. In the Location field I have mentioned the share-path from where the file needs to be picked up(BUY_IN) and next to it I am forced to give a file's name*(OutAck_20140131_02_31_16PM.csv)*. But in the actual scenario the file name doesn't remain a constant one. How can i handle this here ?

I have pasted my table script below:

CREATE TABLE OUT.BUY_RESPONSE_TEST16
(
 ORDER_CATEGORY          VARCHAR2(30 BYTE),
 ORIGINATING_SYS_REF_ID  VARCHAR2(30 BYTE),
 IBUY_ORDER_ID           VARCHAR2(30 BYTE),
 REQUISITION_ID          VARCHAR2(30 BYTE),
 ITEM_ERROR_ITEM_ID      NUMBER,
 ORDER_ITEM_ID           NUMBER,
 QUANTITY                NUMBER
)
ORGANIZATION EXTERNAL
(  TYPE ORACLE_LOADER
 DEFAULT DIRECTORY EZBUY_IN
 ACCESS PARAMETERS 
   ( RECORDS DELIMITED BY NEWLINE
 SKIP 1
 BADFILE 'badfile.log'
 DISCARDFILE 'discardfile.log'
 NOLOGFILE
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 MISSING FIELD VALUES ARE NULL
 )
 LOCATION (BUY_IN:'OutAck_20140131_02_31_16PM.csv')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
Winz
  • 237
  • 3
  • 6
  • 14
  • 2
    Since SQL Loader does not have an API which is callable through a procedure (e.g. plsql) one would want to write a shell script as a variation of this, http://stackoverflow.com/questions/10317521/bat-to-sh-converting-for-sqlloader-start-on-unix-pc – Patrick Bacon May 21 '14 at 22:13
  • You can add multiple datafiles in the location clause: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3015912000346648463 – ady May 06 '15 at 10:42

0 Answers0