Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Location
    Mumbai (India)
    Posts
    11

    Unanswered: External Table Error Trapping

    Hi All,

    Database:ORACLE 10g
    O/s: Windows 2000

    I have created a procedure to upload data from text file. it traps error and writes in text file at defined folders.
    Now i want to trap the error at procedure level and find out the records or related columns and keep a log in data base.

    Please advise me on this issue.

    The procedure, table data and error logs are as follows:

    table data in text file as :
    SRNO|NAME|SURNAME|ADDRESS|PIN|PHONE|
    01|VIKRANT|PATIL|CHIPLUN|400035|9833406777|
    02|VINAY|PATIL|CHIPLUN|400035|9833406777|
    03|ABHIJIT|PATIL|39|2,CHIPLUN, DIST RATNAGIRI|400035|9833406777|
    04|SURENDRA|PATIL|CHIPLUN|400035|9833406777|

    ----================================================== ======
    Record Log in BAD_DIR folder :

    BAD_DIR:: myADDRESS_1060_44712.bad

    03|ABHIJIT|PATIL|39|2,CHIPLUN, DIST RATNAGIRI|400035|9833406777|

    ----================================================== ======
    Record Log in LOG_DIR folder :

    LOG file opened at 11/22/07 18:58:57

    Field Definitions for table MYADDRESS
    Record format DELIMITED BY NEWLINE
    Data in file has same endianness as the platform
    Rows with all null fields are accepted

    Fields in Data Source:

    SRNO CHAR (255)
    Terminated by "|"
    Trim whitespace same as SQL Loader
    NAME CHAR (255)
    Terminated by "|"
    Trim whitespace same as SQL Loader
    SURNAME CHAR (255)
    Terminated by "|"
    Trim whitespace same as SQL Loader
    ADDRESS CHAR (255)
    Terminated by "|"
    Trim whitespace same as SQL Loader
    PIN CHAR (255)
    Terminated by "|"
    Trim whitespace same as SQL Loader
    PHONE CHAR (255)
    Terminated by "|"
    Trim whitespace same as SQL Loader
    error processing column PIN in row 4 for datafile C:\UPLOAD\myADDRESS.TXT
    ORA-12899: value too large for column PIN (actual: 25, maximum: 20)



    These errors i want to trap in exception block if possible. My procedure is as follows:

    create or replace
    PROCEDURE A_TEST_EXT_TABLE AS
    v_TABNAME VARCHAR2(10):='myADDRESS';
    v_CREATE_STRING VARCHAR2(500);
    v_TAB_Counter INTEGER;
    BEGIN

    BEGIN
    SELECT COUNT(1) INTO v_TAB_Counter FROM user_objects WHERE Object_Type = 'TABLE' AND Object_Name = 'myADDRESS_EXT';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_TAB_Counter:= 0;
    END;

    IF v_TAB_Counter > 0 THEN
    EXECUTE IMMEDIATE 'DROP TABLE myADDRESS_EXT';
    END IF;

    v_CREATE_STRING:= 'CREATE TABLE ' || v_TABNAME ||' (
    SRNO VARCHAR2(20),NAME VARCHAR2(20),SURNAME VARCHAR2(20),ADDRESS VARCHAR2(20),PIN VARCHAR2(20),PHONE VARCHAR2(20)
    )';
    v_CREATE_STRING := v_CREATE_STRING || ' ORGANIZATION EXTERNAL (DEFAULT DIRECTORY UPLOAD ACCESS PARAMETERS';
    v_CREATE_STRING := v_CREATE_STRING || ' (RECORDS DELIMITED BY NEWLINE SKIP 1 BADFILE BAD_DIR:'''|| v_TABNAME ||'%a_%p.bad'' LOGFILE LOG_DIR:'''|| v_TABNAME ||'%a_%p.log'' FIELDS TERMINATED BY ''|'') LOCATION ('''|| v_TABNAME ||'.TXT''))';
    v_CREATE_STRING := v_CREATE_STRING || ' REJECT LIMIT UNLIMITED';
    DBMS_OUTPUT.PUT_LINE(v_CREATE_STRING);
    EXCEPTION
    WHEN others THEN
    raise_application_error(-20000, 'Unknown Exception Raised: ' || SQLCODE || ' ' || sqlerrm);
    END A_TEST_EXT_TABLE;



    Please reply,

    ukupatil

    Mumbai, India

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, dropping and creating a table every time this procedure is executed is a BAD idea. Either use an ordinary table or a global temporary table (GTT) (you may research this concept by reading Oracle documentation at http://tahiti.oracle.com).

    If you are going to trap every error, make sure every record is loaded into a temporary table (not necessarily GTT). It may even have only one column, probably of a VARCHAR2(size_large_enough) type to hold a whole input file record. Then, using a PL/SQL capabilities, read line by line, parse it and load into a "real" table. This PL/SQL block can log errors in several different way - you can do something like
    Code:
    IF LENGTH(some_substring) > 20 THEN
       INSERT INTO error_log (...) VALUES (..., 'string is too long');
    ELSIF ...
    END IF;
    Also, use exception handlers to log Oracle native errors, such as
    Code:
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        INSERT INTO error_log (...) VALUES (..., 'such a value already exists in the table');
     
      WHEN ...

Posting Permissions

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