asked on

how to validate through a function or procedure ?

Hello expert ,
I have a two table emp and emp1 having columns company_id ,company_name,com_date which are having no constraints.
but i need to validate data when i will load data from one emp to emp1 table.
my question is how can i validate data through a procedure or function. or anything else
can you please suggest me and need code for that.

below is my validation part
Company Id is Mandatory and Unique
Company name is Mandatory
Date formats should be MM/DD/YYYY,
Valid Until date must be current or future date
Thanks
Thomos

Tomas Helgi Johannsson

Hi!

The best way is to use functions for data validations/transformations as they can be used in queries and furthermore in any DML type of statements.
Therefore functions are widely used in DML statements where data is moved between two tables , transformed to other format or validated.

Example:

CREATE OR REPLACE FUNCTION ISNUMERIC (PARAM IN CHAR) RETURN NUMBER AS
    DUMMY VARCHAR2(100);
BEGIN
    DUMMY:=TO_CHAR(TO_NUMBER(PARAM));
    RETURN (1);
EXCEPTION
    WHEN OTHERS THEN
        RETURN (0);
END;

And its usage : 
SELECT * FROM EMP WHERE ISNUMERIC(PHONE) = 1;

Open in new window

Regards,
Tomas Helgi

awking00

What is the datatype for com_date in the two tables? You say date formats should be MM/DD/YYYY but date datatypes don't have formats, but are displayed in various formats. Also, since you are concerned with data being mandatory and/or unique, creating not null and unique constraints on the table would prevent data which do not match that criteria from being inserted and readily defined in a procedure. Can you show the create statements for the two tables (the relevant fields are all that's necessary)?

slightwv (䄆 Netminder)

What are you expecting the procedure or function to do besides 'validate'?

How are you planning on getting the data from emp to emp1?

If you are writing code to do this, just code for the validation.

If you are planning on an insert from a select using straight SQL, you'll need a trigger. Just code the checks into the trigger.

I do agree with the post above: Constraints can handle a lot of this. Any reason you don't want to use them?

deve_thomos

ASKER

Hello expert ,
My business requirement is like this that both tables don't have any constraint.
so how can i do that through a trigger or procedure or a function ??

ASKER CERTIFIED SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION