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
Solved

how to validate through a  function or procedure ?

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
This video shows how to recover a database from a user managed backup

856 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