Solved

how to validate through a  function or procedure ?

Posted on 2014-02-01
7
433 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
  • 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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now