Solved

how to validate through a  function or procedure ?

Posted on 2014-02-01
7
436 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
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!

 

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 250 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 250 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 73
Database Design Dilemma 6 65
sql server store procedure contains temp tables need to convert oracle? 3 38
Need help on decision table structure 7 46
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

749 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