Validate External Partitioned Data
To validate an external partitioned table, you can use the
procedure DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE
. This
procedure includes a parameter that lets you specify a specific partition to
validate.
Before validating an external partitioned table you need to create the external
partitioned table. To create an external partitioned table use the
procedure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
(see Query External Partitioned Data (with Partitioning Clause) for more details):
BEGIN
DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE
(
table_name => 'PET1',
partition_name => 'P1');
END;
/
This procedure scans your source files for partition P1 and validates them using the format options specified when you create the external partitioned table.
The validation of a partitioned table by default validates all the
partitions sequentially until rowcount
is reached.
If you specify a partition_name
then only a
specific partition is validated.
The validate operation, by default, scans all the rows in your source files and stops
when a row is rejected. If you want to validate only a subset of the
rows, use the rowcount
parameter. When the
rowcount
parameter is set the validate
operation scans rows and stops either when a row is rejected or when
the specified rowcount
number of rows are validated
without errors.
For example, the following validate operation scans 100 rows and stops when a row is rejected or when 100 rows are validated without errors:
BEGIN
DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE
(
table_name => 'PET1',
rowcount => 100 );
END;
/
If you do not want the validate to stop when a row is rejected and you want to see
all rejected rows, set the stop_on_error
parameter
to FALSE
. In this case DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE
scans all rows and reports all rejected rows.
If you want to validate only a subset of rows use the rowcount
parameter. When rowcount
is set and stop_on_error
is set to FALSE
, the validate operation scans rows and stops either when the specified number of rows are rejected or when the specified number of rows are validated without errors. For example, the following example scans 100 rows and stops when 100 rows are rejected or when 100 rows are validated without errors:
BEGIN
DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE
(
table_name => 'PET1',
rowcount => 100
stop_on_error => FALSE );
END;
/
See VALIDATE_EXTERNAL_PART_TABLE Procedure for more information on DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE
.
See View Logs for Data Validation to see the results of validate operations in
the tables dba_load_operations
and
user_load_operations
.
Parent topic: Query External Data with Autonomous Database