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