7 Replies Latest reply on Aug 15, 2019 3:08 AM by Gaz in Oz

    Defining external table

    hvaidya

      Hello.

       

      I need to create an external with a date column in it.  Unfortunately, the date column format is different on some of the rows.  Some rows have date format of YYYYMMDD but some of them have MMDDYYYY.  How can I handle this is external table definitions?

       

      Thanks

      Hiten

        • 1. Re: Defining external table
          hvaidya

          This is my table definition:

           

          CREATE TABLE ACTLLIFE.tblVNContract_x

          (

          vchSOURCE_SYSTEM VARCHAR2(10),

          vchEAF_FEED_ID VARCHAR2(150),

          NIROWN_NUM Number,

          dtORIGINAL_POLICY Date

          )

                ORGANIZATION EXTERNAL

               (TYPE ORACLE_LOADER

                DEFAULT DIRECTORY EXTTBLFILES_ACTLLIFE

                ACCESS PARAMETERS

                  ( RECORDS DELIMITED BY NEWLINE

                    DNFS_ENABLE

                    DNFS_READBUFFERS 10

                    FIELDS TERMINATED BY '|'

                    MISSING FIELD VALUES ARE NULL

                  (         

          vchSOURCE_SYSTEM,

          vchEAF_FEED_ID,

          NIROWN_NUM,

          dtORIGINAL_POLICY CHAR(8) DATE_FORMAT DATE MASK "YYYYMMDD"  NULLIF(dtORIGINAL_POLICY='null' or dtORIGINAL_POLICY='99999999' or dtORIGINAL_POLICY='00000000')

          )

          )

                LOCATION ('Test.CONTRACT_OUT')

               )

          REJECT LIMIT 0

          PARALLEL 8

          NOROWDEPENDENCIES

          /

           

          This is my input file:

           

          SystemX|3036193|15601|20180430

          SystemX|3036193|15602|20180430

          SystemX|3036193|15603|20180530

          SystemX|3036193|15604|20180630

          SystemX|3036193|15605|04132019

          SystemX|3036193|16098|20180730

          SystemX|3036193|16116|20180430

          SystemX|3036193|16117|20180428

          SystemX|3036193|16118|20181222

           

          In the above, the bold line is line with bad data format in the last column.

           

          Thans

          Hiten

          • 2. Re: Defining external table
            EdStevens

            What prevents you from identifying and fixing the bad-format data?

            • 3. Re: Defining external table
              hvaidya

              I cannot update the bad-format data.  It's a file we're getting from an external system and we're using what they send us.

              • 4. Re: Defining external table
                Gaz in Oz

                hvaidya wrote:

                 

                I cannot update the bad-format data.  It's a file we're getting from an external system and we're using what they send us.

                ...then get the sender to send you data in a known and consistent format.

                 

                Even if you change the external table "date" column to VARCHAR2(8), and use that as a staging table, you still have to deal with the fact that you can not reliably get the date format right as a string of numbers might equate to a valid date, but different to what was intended. So you have to make assumptions of some sort and you may be wrong.

                For example

                SQL> ed
                Wrote file afiedt.buf      
                                                                                            
                  1  with tblvncontract_x (vchsource_system, vcheaf_feed_id, nirown_num, dtoriginal_policy) as (
                  2     select 'SystemX', '3036193', 15605, '20111102' from dual
                  3  )
                  4  select x.*,
                  5         to_date(dtoriginal_policy, 'ddmmyyyy') backwards,
                  6         to_date(dtoriginal_policy, 'yyyymmdd') forwards
                  7* from   tblvncontract_x x
                SQL> /
                
                VCHSOUR VCHEAF_ NIROWN_NUM DTORIGIN BACKWARDS  FORWARDS
                ------- ------- ---------- -------- ---------- ----------
                SystemX 3036193      15605 20111102 1102-11-20 2011-11-02
                
                1 row selected.
                
                SQL>
                

                So now you have to use logic to create rules on which format mask you are going to use.

                • 5. Re: Defining external table
                  EdStevens

                  +10

                   

                  And even if one tries to 'code their way out of it', how would one reliably interpret '05/04/2012'?  Any given element could legitimately be a month, a day, or a year.  And it would be even worse if provided with only a 2-digit year:  '05/04/12'.  Now, not only can you not be sure of which component is supposed to be the year, you don't even know what century!

                  • 6. Re: Defining external table
                    michael.sakayeda-Oracle

                    Hi,

                     

                    Are you absolutely sure that there are only 2 date formats possible?

                     

                    Are you sure that will not change in the future?

                     

                    I tend to agree with Gaz and Ed - dealing with ambiguous data is dicey at best.

                     

                    If you are sure there are only 2 possible date formats you could try something like below by creating 2 external tables and doing a union of the two.  The first external table has the "YYYYMMDD" format and the second table has the "MMDDYYYY" format.  Note this requires changing your reject limit to "unlimited".

                     

                    An alternate solution might be to have a single external table definition that takes 2 bind variables - one is the "date_format" format and the other is the file in the location clause.  Change your reject limit to "unlimited" and specify a badfile (e.g. Test.CONTRACT_OUT_1) in the access parameters.

                     

                    In your example you would pass "YYYYMMDD" and "Test.CONTRACT_OUT". This would load 8 rows and reject the row with the "MMDDYYYY" format.  This last record would be placed into the badflie Test.CONTRACT_OUT_1.

                     

                    Then invoke the external table again with "MMDDYYYY" and "Test.CONTRACT_OUT_1".

                     

                    I'm not sure what you are doing with the external table - if you are doing an IAS then this approach may work.  If you are querying the table and expect to see all of the rows in one query you might want to try the previously mentioned example.

                     

                    Also note that both approaches require modifying the reject limit.  I'm not sure if that is acceptable for your application.

                     

                    Hope that helps.

                     

                    -Mike

                     

                    ade:[ msakayed_main45 ] [msakayed@den02tlt work]$ sqlplus "/as sysdba" @foo

                    SQL*Plus: Release 20.0.0.0.0 - Development on Wed Aug 14 18:38:25 201

                    Version 20.1.0.0.0

                     

                    Copyright (c) 1982, 2019, Oracle.  All rights reserved.

                     

                    Connected to:

                    Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Development

                    Version 20.1.0.0.0

                     

                    SQL> set feedback on

                    SQL>

                    SQL> create or replace directory EXTTBLFILES_ACTLLIFE as '/net/den02tlt/scratch/msakayed/view_storage/msakayed_main45/work';

                     

                    Directory created.

                     

                    SQL>

                    SQL> DROP TABLE tblVNContract_x

                      2  /

                     

                    Table dropped.

                     

                    SQL> DROP TABLE tblVNContract_y

                      2  /

                     

                    Table dropped.

                     

                    SQL>

                    SQL> CREATE TABLE tblVNContract_x

                      2  (

                      3  vchSOURCE_SYSTEM VARCHAR2(10),

                      4  vchEAF_FEED_ID VARCHAR2(150),

                      5  NIROWN_NUM Number,

                      6  dtORIGINAL_POLICY Date

                      7  )

                      8        ORGANIZATION EXTERNAL

                      9       (TYPE ORACLE_LOADER

                    10        DEFAULT DIRECTORY EXTTBLFILES_ACTLLIFE

                    11        ACCESS PARAMETERS

                    12        ( RECORDS DELIMITED BY NEWLINE

                    13          DNFS_ENABLE

                    14          DNFS_READBUFFERS 10

                    15          FIELDS TERMINATED BY '|'

                    16          MISSING FIELD VALUES ARE NULL

                    17          (

                    18            vchSOURCE_SYSTEM,

                    19            vchEAF_FEED_ID,

                    20            NIROWN_NUM,

                    21            dtORIGINAL_POLICY CHAR(8) DATE_FORMAT DATE MASK "YYYYMMDD"  NULLIF(dtORIGINAL_POLICY='null' or dtORIGINAL_POLICY='99999999' or dtORIGINAL_POLICY='00000000')

                    22          )

                    23        )

                    24        LOCATION ('Test.CONTRACT_OUT')

                    25       )

                    26  REJECT LIMIT UNLIMITED

                    27  PARALLEL 8

                    28  NOROWDEPENDENCIES

                    29  /

                     

                    Table created.

                     

                    SQL>

                    SQL> CREATE TABLE tblVNContract_y

                      2  (

                      3  vchSOURCE_SYSTEM VARCHAR2(10),

                      4  vchEAF_FEED_ID VARCHAR2(150),

                      5  NIROWN_NUM Number,

                      6  dtORIGINAL_POLICY Date

                      7  )

                      8        ORGANIZATION EXTERNAL

                      9       (TYPE ORACLE_LOADER

                    10        DEFAULT DIRECTORY EXTTBLFILES_ACTLLIFE

                    11        ACCESS PARAMETERS

                    12        ( RECORDS DELIMITED BY NEWLINE

                    13          DNFS_ENABLE

                    14          DNFS_READBUFFERS 10

                    15          FIELDS TERMINATED BY '|'

                    16          MISSING FIELD VALUES ARE NULL

                    17          (

                    18            vchSOURCE_SYSTEM,

                    19            vchEAF_FEED_ID,

                    20            NIROWN_NUM,

                    21            dtORIGINAL_POLICY CHAR(8) DATE_FORMAT DATE MASK "MMDDYYYY"  NULLIF(dtORIGINAL_POLICY='null' or dtORIGINAL_POLICY='99999999' or dtORIGINAL_POLICY='00000000')

                    22          )

                    23        )

                    24        LOCATION ('Test.CONTRACT_OUT')

                    25       )

                    26  REJECT LIMIT UNLIMITED

                    27  PARALLEL 8

                    28  NOROWDEPENDENCIES

                    29  /

                     

                     

                    Table created.

                     

                     

                    SQL>

                    SQL>

                    SQL> select * from tblVNContract_x

                      2  union

                      3  select * from tblVNContract_y

                      4  /

                     

                     

                    VCHSOURCE_

                    ----------

                    VCHEAF_FEED_ID

                    --------------------------------------------------------------------------------

                    NIROWN_NUM DTORIGINA

                    ---------- ---------

                    SystemX

                    3036193

                         15601 30-APR-18

                     

                    SystemX

                    3036193

                         16116 30-APR-18

                    VCHSOURCE_

                    ----------

                    VCHEAF_FEED_ID

                    --------------------------------------------------------------------------------

                    NIROWN_NUM DTORIGINA

                    ---------- ---------

                    SystemX

                    3036193

                         15602 30-APR-18

                     

                    SystemX

                    3036193

                     

                    VCHSOURCE_

                    ----------

                    VCHEAF_FEED_ID

                    --------------------------------------------------------------------------------

                    NIROWN_NUM DTORIGINA

                    ---------- ---------

                         15604 30-JUN-18

                     

                    SystemX

                    3036193

                         15605 13-APR-19

                     

                    SystemX

                     

                    VCHSOURCE_

                    ----------

                    VCHEAF_FEED_ID

                    --------------------------------------------------------------------------------

                    NIROWN_NUM DTORIGINA

                    ---------- ---------

                    3036193

                         15603 30-MAY-18

                     

                    SystemX

                    3036193

                         16098 30-JUL-18

                     

                     

                    VCHSOURCE_

                    ----------

                    VCHEAF_FEED_ID

                    --------------------------------------------------------------------------------

                    NIROWN_NUM DTORIGINA

                    ---------- ---------

                    SystemX

                    3036193

                         16117 28-APR-18

                     

                    SystemX

                    3036193

                         16118 22-DEC-18

                     

                    VCHSOURCE_

                    ----------

                    VCHEAF_FEED_ID

                    --------------------------------------------------------------------------------

                    NIROWN_NUM DTORIGINA

                    ---------- ---------

                     

                    9 rows selected.

                     

                    SQL> exit

                    Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Development

                    Version 20.1.0.0.0

                    • 7. Re: Defining external table
                      Gaz in Oz

                      michael, showing an example, creating tables as sysdba isn't good...

                      Also consider formatting your code via the "Use advanced editor" so it is more concise and easier to read and make good use of sqlplus formatting commands.

                      For example:

                      . Use sqlplus column format options to display heading and data on single rows

                      . set pagesize as necessary so as not to repeat headings

                      . set linessize as necessary

                      . when posting use advanced editor

                      . Choose Currier New as the font for code or use the ">> -> Syntax Highlighting -> SQL" option.

                      . Loose the superfluous blank lines that get added when pasting into threads.

                      Above is good especially for lengthy posts like yours.