Results 1 to 15 of 15

Thread: Etl

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Etl

    I'm running my ETL scripts on huge tables which contains about 20mil records.
    I wait for long hours for the ETL to finish and finally end up with an error and the data is not populated. The error is either due to
    "cannot insert null into....ORA 01400" OR
    "max value for the datatype is 32 and I gave varchar2(30)"

    How can handle such situations. If I write exceptions, these will be captured and I will get a message but will the data load continue minus this column being populated?
    Please let me know.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You didn't mention which database version you are on. If it is Oracle 10g R2, you could use Error Logging feature (link will show you an example of how to do it).

    If your database version is lower, you'll have to develop a strategy of error handling. As far as I can tell, no - you can not insert only valid columns and omit invalid ones (perhaps you might, but I think it would require a fair amount of programming). The simplest way is to omit the whole record. It might look like this:
    Code:
    BEGIN
      FOR cur_r IN (SELECT empno FROM emp) 
      LOOP
        BEGIN                                   | inner BEGIN-EXCEPTION-END
          process your data here                | block will ensure that
        EXCEPTION                               | processing will continue
          WHEN OTHERS THEN                      | even though an error
            INSERT INTO err_log (cur_r.empno);  | has occurred
        END;                                    |
      END LOOP;
    END;
    Once it is finished, check what is in the 'err_log' table (which should be created by you, depending on information you want to store - error code/message would be more than welcome) and see how to fix those errors.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking



    Are you using SQL*Loader?

    If you are, set the OPTIONS (ERRORS=0) parameter in the control file to allow loading of ALL records -- Records not loaded end up in the BAD file which you can then edit and re-process.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Quote Originally Posted by Littlefoot
    You didn't mention which database version you are on. If it is Oracle 10g R2, you could use Error Logging feature (link will show you an example of how to do it).

    If your database version is lower, you'll have to develop a strategy of error handling. As far as I can tell, no - you can not insert only valid columns and omit invalid ones (perhaps you might, but I think it would require a fair amount of programming). The simplest way is to omit the whole record. It might look like this:
    Code:
    BEGIN
      FOR cur_r IN (SELECT empno FROM emp) 
      LOOP
        BEGIN                                   | inner BEGIN-EXCEPTION-END
          process your data here                | block will ensure that
        EXCEPTION                               | processing will continue
          WHEN OTHERS THEN                      | even though an error
            INSERT INTO err_log (cur_r.empno);  | has occurred
        END;                                    |
      END LOOP;
    END;
    Once it is finished, check what is in the 'err_log' table (which should be created by you, depending on information you want to store - error code/message would be more than welcome) and see how to fix those errors.
    Very interesting piece on DML error logging.
    Now my next question would be if something similar would exist in SQL server 2005? (I know , the wrong forum, but perhaps you're multi-database experts )
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm sorry, but I wouldn't know the answer to your question. Someone, though, might know it.

    You could, of course, open a thread on MS SQL Server and link, or even copy this piece of code; perhaps you'll find someone who will be able to help you.

  6. #6
    Join Date
    Jul 2005
    Posts
    276
    I'm not using SQL loader Lkbrwn_dba.
    Thank you so much Littlefoot. It was helpful.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by blom0344
    Very interesting piece on DML error logging.
    Now my next question would be if something similar would exist in SQL server 2005? (I know , the wrong forum, but perhaps you're multi-database experts )
    Something like this:
    Code:
    UPDATE ...
    
    IF (@@ERROR <> 0)
      INSERT INTO ...
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I disklike using WHEN OTHERS *sometimes*.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I agree; but, in this case, I'd say that it doesn't really matter. As I've understood it, Nandinir wants to insert all valid records. This approach will also enable him to save invalid ones (along with error code) and later, knowing which error code applies to which record, take necessary action(s).

  10. #10
    Join Date
    Jul 2005
    Posts
    276
    Another question, I have NULL values in the base tables whereas I dont want these NULL columns in the destination tables.

    My script is like this:

    insert into source
    (col1 ,col2 ,col3....col10)
    select d.col2,d.col2,d.col3.....t.col10
    from destination d, tab1 t
    where d.col1=t.col1 and
    d.col2=t.col2
    col1 is not null in destination whereas col1 has null values in source.
    When I'm runnning the ETL to load into source, I dont want columns(col1) with NULL values. How can I acheive this?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How can I acheive this?
    NVL perphaps?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jul 2005
    Posts
    276
    But using NVL, I will be getting those rows and use some dummy value for NULL.
    But what if I dont want to get those rows which has a NULL. I want only valid rows.

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DUH?
    AND COL1 IS NOT NULL
    perphaps?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Jul 2005
    Posts
    276
    yes, col1 is not null in the source tab and col1 in destination is null. I dont want these null columns in source tab to be loaded when I run the ETL. Perhaps, I can use some kind of exception, but not sure though.

  15. #15
    Join Date
    Jul 2005
    Posts
    276
    Or can I use a condition like this; It works, but wanted to know if this is a good way of doing and if there is anything better than this.

    insert into source
    (col1 ,col2 ,col3....col10)
    select d.col2,d.col2,d.col3.....t.col10
    from destination d, tab1 t
    where d.col1=t.col1 and
    d.col2=t.col2 and
    col1 is not null and
    col2 is not null

Posting Permissions

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