Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Data load validation - number of records

user6444109Jun 10 2013 — edited Jun 11 2013


Hello all,

I'm not a SQL developer, I just know the basics so I need your help.

I'm trying to validate the number of records loaded into the Oracle DB table we have against the number of records extracted from our source system.  We receive a flat file to be loaded and I will have the record count at the end of the file as last row with a particular value in the first column so I can segregate it from the actual data content.

I'd like the load script to fail if the number of records in the file does not match the number I retrieve in the last row.

In the load process, we first define an external table in Oracle linking to the flat file at the OS level and then we load from this external table into actual tables.

One option I thought was to create a TEMP table called DATA_LOAD_CHECK and put a constraint on it using the number of records and try insert a new record using the record count of the external table since It would fail if the constraint is not fullfilled.

In my simple mind, I thought of this:

CREATE TABLE DATA_LOAD_CHECK

  (

    REC_TYPE      VARCHAR2(256 BYTE) NOT NULL,

    REC_COUNT     NUMBER(12,0),

    CONSTRAINT "DATA_LOAD_CHECK_PK" PRIMARY KEY (REC_TYPE)

  );

If I create a constraint such as:

ALTER TABLE DATA_LOAD_CHECK ADD CONSTRAINT MATCHING_NUMBER_OF_RECORDS CHECK(REC_COUNT = 265631);

Then, try to insert a record into the table

INSERT INTO DATA_LOAD_CHECK (REC_TYPE, REC_COUNT) VALUES ('Testing', 123456);

It fails as expected...  I thought of using this dynamically and get the value from the last row of the file I receive...

So, I need to be able to CREATE/ALTER the constraint dynamically from the control record I receive using a sub SQL statement instead of hard coded value (e.g. 265631).  Such as

ALTER TABLE DATA_LOAD_CHECK ADD CONSTRAINT MATCHING_NUMBER_OF_RECORDS CHECK(REC_COUNT = (select count(*) from EXT_ORDER_FILL));

Is there a way I could use sub statements instead of constant?

or is there a better/simpler way of validating the number of records against a control value that I receive?

Thanks in advance,

Cuneyt

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 9 2013
Added on Jun 10 2013
11 comments
2,813 views