Home » RDBMS Server » Server Utilities » External loader with Load When clause (oracle 11gr2)
External loader with Load When clause [message #676312] Thu, 30 May 2019 05:02 Go to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Dear Oracle Gurus,

I have a file where i have few header line and external table skip claus can't skip those line so i am adding a load when claus which is not working for me, if someone can help me to know the cause will be great help

CREATE TABLE STG_SAP.EXT_ABS
(
  A          VARCHAR2(250 BYTE),
  B             VARCHAR2(250 BYTE),
  C             VARCHAR2(250 BYTE),
  D             VARCHAR2(250 BYTE),
  E               VARCHAR2(250 BYTE),
  F                 VARCHAR2(250 BYTE),
  G             VARCHAR2(250 BYTE),
  H  VARCHAR2(250 BYTE),
  I            VARCHAR2(250 BYTE),
  J                 VARCHAR2(250 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY PDW_SOURCE
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE 
   PREPROCESSOR PDW_SOURCE:'pdwpws_dos2unix.sh'
          LOAD WHEN (A REGEXP_LIKE '[0-9]' )
   BADFILE PDW_BAD:'zpdw_aabs.bad'
   LOGFILE PDW_LOG:'zpdw_aabs.log' 
   skip 11
   FIELDS TERMINATED BY "|" 
   LRTRIM
   MISSING FIELD VALUES ARE NULL   
    ( A         CHAR(250), 
      B         CHAR(250),
      C         CHAR(250),
      D         CHAR(250),
      E         CHAR(250),
      F         CHAR(250),
      G         CHAR(250),
      H         CHAR(250),
      I         CHAR(250),
      J         CHAR(250)
    ) )
     LOCATION (PDW_SOURCE:'ZPDW_AABS.CSV')
  )
REJECT LIMIT 10
PARALLEL ( DEGREE 4 INSTANCES 1 )
NOMONITORING;

Directory:    \\psapmnt\sapmnt\interfaces\PDW\BWP
Name:         ZPDW_AABS.CSV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
09806199|0085|||20190418|20190418|1.000 ||00000000|
09806697|0085|||20190418|20190418|1.000 ||00000000|
09806968|0085|||20190418|20190418|1.000 ||00000000|
09807031|0085|||20190418|20190418|1.000 ||00000000|
09807067|0085|||20190418|20190418|1.000 ||00000000|
09807082|0085|||20190418|20190418|1.000 ||00000000|
09807522|0085|||20190418|20190418|1.000 ||00000000|
09807559|0085|||20190418|20190418|1.000 ||00000000|
09807581|0085|||20190418|20190418|1.000 ||00000000|
09807590|0085|||20190418|20190418|1.000 ||00000000|
09808237|0085|||20190418|20190418|1.000 ||00000000|
09808607|0085|||20190418|20190418|1.000 ||00000000|
09809608|0085|||20190418|20190418|1.000 ||00000000|
09809937|0085|||20190418|20190418|1.000 ||00000000|
09810336|0085|||20190418|20190418|1.000 ||00000000|
09810471|0085|||20190418|20190418|1.000 ||00000000|
09810591|0085|||20190418|20190418|1.000 ||00000000|
09810946|0085|||20190418|20190418|1.000 ||00000000|
09811126|0085|||20190418|20190418|1.000 ||00000000|
09811168|0085|||20190418|20190418|1.000 ||00000000|
09811186|0085|||20190418|20190418|1.000 ||00000000|
09811319|0085|||20190418|20190418|1.000 ||00000000|
09811378|0085|||20190418|20190418|1.000 ||00000000|
09811423|0085|||20190418|20190418|1.000 ||00000000|
Re: External loader with Load When clause [message #676315 is a reply to message #676312] Thu, 30 May 2019 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66654
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Thu, 28 February 2019 17:43

Why didn't you feedback and thank people who spent time to help you in your previous topics?

Your answer:
guddu_12 wrote on Fri, 01 March 2019 10:59
Hello Michel ,

I am not so bad to appreceiate for the help, I didn't check post as I was away form office. I will try the link give above and post you the finding

You did not.



Re: External loader with Load When clause [message #676318 is a reply to message #676315] Thu, 30 May 2019 05:50 Go to previous message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hello Michel,

I am really sorry for that, i was taken out of the project so didn't try the option and suggestion.
Previous Topic: Script to run EXPDP
Next Topic: Error_Stack...ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
Goto Forum:
  


Current Time: Wed Oct 23 20:43:12 CDT 2019