Hi Tom,
i have a table like an employee in the three fields 1) ename 2)empno 3)salary
create table details(ename varchar2(20),empno number(12),salary number(12));
I am receiving data from the file(clob) by the administrator for the three field. But they might have given wrong data for ename like more than 20 characters, empno more than 12 numbers or character, for salary more than 12 numbers or character. if any wrong data the data will not insert into the table, but how to store all errors and need to provide the user-friendly error message and code to the output
1)How to validadate data before inserting into a table the fields
2)The Oracle will validate only one error, it will not do to other validate if we encounter the error (if ename fails to insert, it will not go to empno), but I need to capture all the error like ename and empno and salary.
Please help on this.
You only get the details of one exception from a statement; even if there are many problems with it!
If you want some basic validation to avoid data type errors and check constraint violations you can pull this information out of the data dictionary:
create table details(
ename varchar2(20),empno number(12),salary number(12) check ( salary > 0 )
);
select column_name, data_type, data_length, data_precision, data_scale, nullable
from user_tab_cols
where table_name = 'DETAILS';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE
ENAME VARCHAR2 20 <null> <null> Y
EMPNO NUMBER 22 12 0 Y
SALARY NUMBER 22 12 0 Y
select ucc.constraint_name, ucc.table_name, ucc.column_name, search_condition
from user_constraints uc
join user_cons_columns ucc
on uc.constraint_name = ucc.constraint_name
where ucc.table_name = 'DETAILS'
and uc.constraint_type = 'C';
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME SEARCH_CONDITION
SYS_C0013182 DETAILS SALARY salary > 0
But there are many other types of exception you could encounter. In particular unique key and foreign key violations. There isn't really a good, easy way to check these other than to run the statement!