Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, james.

Asked: November 13, 2016 - 10:40 am UTC

Last updated: November 14, 2016 - 3:44 am UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

How to write a validation procedure for three conditions which are dependent on each other .
first condition will be for country zone in that there will be condition for branch for specific date range .

and Connor said...

Suffice to say you havent provided a lot of detail :-) but here's an example using a table


SQL> create table val_rules
  2    ( country varchar2(10),
  3      branch int,
  4      date_from date,
  5      date_to date
  6     );

Table created.

SQL>
SQL> insert into val_rules values ('AUS',1, date '2016-01-01', date '2017-01-01');

1 row created.

SQL> insert into val_rules values ('AUS',2, date '2015-01-01', date '2017-01-01');

1 row created.

SQL> insert into val_rules values ('ENG',3, date '2018-01-01', date '2019-01-01');

1 row created.

SQL>
SQL> create or replace
  2  procedure validator(p_country varchar2, p_branch int, p_dte date ) is
  3    l_is_valid int;
  4  begin
  5    select count(*)
  6    into   l_is_valid
  7    from   val_rules
  8    where  country = p_country
  9    and    branch = p_branch
 10    and    date_from <= p_dte
 11    and    date_to   >= p_dte;
 12
 13    if l_is_valid = 0 then
 14      raise_application_error(-20000,'Invalid data');
 15    end if;
 16  end;
 17  /

Procedure created.

SQL>
SQL> exec validator('AUS',1,sysdate);

PL/SQL procedure successfully completed.

SQL> exec validator('AUS',1,sysdate+1000);
BEGIN validator('AUS',1,sysdate+1000); END;

*
ERROR at line 1:
ORA-20000: Invalid data
ORA-06512: at "MCDONAC.VALIDATOR", line 13
ORA-06512: at line 1


SQL>
SQL>



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library