Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to validate through a  function or procedure ?

Posted on 2014-02-01
7
Medium Priority
?
439 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
Independent Software Vendors: 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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