Data validations

user-caintegra

Hi I kind of newbie in this stuff, I have a situation I'd like to be helped.

I have a table A with columns name, id, phone, etc.. I need to make validation for earch columns in order to prove tha the data exist in another table B with reference information, I need to do this in order to send the data from table A to a new table C.

For example, In table A in row 1 in the column name there is a "John" , column id "12345", column phone "0180012345". I need to check in column B if John exists, if it doesn´t exist then stop the next validations and display tha John doesn´t exist in Table B, When "John" be corrected, the first validation will be passed with no problem so the next validation can be released and it goes the same situation: check if the "123455" from table A exists in the ID column of Table B and if it doesn´t the stop the next validations and so on...The point is: I need to check that all data from table A exist in table B and they pass all the validations in order to be fill in the new table C.

I have the validations in separate ways, I know I need to create a package with all the validations, but I don´t know how to make a "break" in one validation and STOP the next validation and display all the records from the column that don´t comply with the validation.

I hope I can be supported with this matter.

John

Frank Kulash

Hi, John,

Sorry, it's not clear what you want.

You may not need any PL/SQL at all: a SQL INSERT statement can check for referential integrity, and, if some rows fail, it can still INSERT the valid rows.

Whenever you have a problem, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas.  In this case, include CREATE TABLE and INSERT statements for all the tables involved, including the look-up tables, to show what they are before table C gets populated.  Also post what you want table C to look like after it's populated.  Explain why each row of A did or did not get copied into C.

Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

See the forum FAQ: