CODEWITHSUNDEEP
sqloracle-databaseoracle11g
vvekselva
vvekselva

Reputation: 823

Data Analyzer -Sql

We have a student table with student_id integer, name varchar and age as columns. We use oracle as our dbms. We get our data from flat files and load the data using the sql loader. So while loading we get exceptions due to duplication of records and so on. To identify that we thought of designing an analyzer. For that we designed a replica table containing the same columns with an exception all the columns being varchar. So first we load the data in the analyzer table then we identify the records. Since the analyzer table is with varchar columns,it can consume any kind of data such as text for even student_id field. Is there are possible way to identify those records by means of sql queries?

Upvotes: 1

Views: 124

Answers (2)

Kevin Burton
Kevin Burton

Reputation: 11936

How about using LOG ERRORS ? e.g:

Firstly create the table to hold the errors....

DBMS_ERRLOG.CREATE_ERROR_LOG('STUDENT');

next sqlload the data into the tmp table, then run a plsql routine to load the data..... 

INSERT INTO student
    SELECT to_number(ts.student_id), name, to_number(age)
    FROM tmp_student ts
    LOG ERRORS
    REJECT LIMIT UNLIMITED;

To see the errors:

SELECT * FROM err$_student;

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52346

A better approach might be to define an external table based on the file that you are currently uploading with SQL*Loader, and to treat that as the "analyzer" table. The methods of identifying duplicates would be with standard SQL syntax, and would depend on whether you have duplicates within the new data set that you have to check for as well as between the old and the new data sets. If only the latter then the MERGE statement is very efficient for this purpose.

Not sure about that varchar idea -- unless you have corrput data in the file I don't see what it contributes.

Upvotes: 2

Related Questions