?
Solved

how to validate through a  function or procedure ?

Posted on 2014-02-01
7
Medium Priority
?
438 Views
Last Modified: 2014-02-03
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
0
Comment
Question by:deve_thomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39826469
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39826496
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)?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39827018
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?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:deve_thomos
ID: 39827695
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 ??
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 1000 total points
ID: 39827936
Hi!

You can call functions within a trigger which is triggered before either insert or update to do the data validation.
If data validation is complex and you want to be able to reuse that validation elsewhere then I suggest you use functions otherwise you can code simple logic in the triggers.

You can move data from EMP to EMP1 something like this using functions to do some conversion/validation.

insert into emp1
select col1, col2, myfunction(col3) as col3, ....
from emp
where col4 =  myotherfunc(col4)

Regards,
   Tomas Helgi
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39828449
I asked two additional questions that you did not answer.

Before we can suggest what we think you may need, you need to tell us about your situation.

creating triggers is pretty straight forward and there are TONS of examples out there.

I'm guessing you want a before insert or update trigger.
0
 

Author Closing Comment

by:deve_thomos
ID: 39829535
thanks a trigger solved this issue
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question