Requ:- If table do not have records then need to be inserted if have already the END DATE column only update with an no.of years based on Terms( For example If Term year is 10, then 10*12=120 Months, means..10 years needs to added to the END DATE col.
CREATE TABLE USA_ASC11
( POLICY_NO NUMBER,
START_DATE DATE,
TERMS_OF_YEAR NUMBER,
frequ number,
END_DATE DATE
)
INSERT INTO USA_ASC11 VALUES(101, '2-5-2019' , 10 , 1, '22-5-2019' );
When i executed my below procedure am getting an error. Please correct and send me perfect procedure.
create or replace procedure I_U_Ploicy
(
ip_start_date varchar2, ip_terms number, I_terms_of_year NUMBER
)
Is
v_pol number;
v_strtdate date;
v_terms number;
v_enddate Date;
p_frequ number;
vnct number;
Begin
select POLICY_NO , START_DATE , TERMS_OF_YEAR ,add_months(to_date(ip_start_date, 'DD-MM-YYYY'),ip_terms*12)-1
as End_date
INTO v_pol, v_strtdate, v_terms , v_enddate FROM usa_asc11 WHERE FREQU=p_frequ;
if vnct =0 then
update usa_asc11
set end_date = v_enddate where POLICY_NO = v_pol;
else
insert into policy_p1(POLICY_NO , START_DATE , TERMS_OF_YEAR, End_date, FREQU)values
(1, '2-6-2019', 10, '2-6-2019' , 1);
commit;
End if;
Exception
When Others Then
Raise_Application_Error(-20000, 'There is no data to be update');
End;
Thanks in advance broo.
Yeah, I had problems too
SQL> create or replace procedure I_U_Ploicy
2 (
3 ip_start_date varchar2, ip_terms number, I_terms_of_year NUMBER
4 )
5 Is
6 v_pol number;
7 v_strtdate date;
8 v_terms number;
9 v_enddate Date;
10 p_frequ number;
11 vnct number;
12 Begin
13 select POLICY_NO , START_DATE , TERMS_OF_YEAR ,add_months(to_date(ip_start_date, 'DD-MM-YYYY'),ip_terms*12)-1
14 as End_date
15 INTO v_pol, v_strtdate, v_terms , v_enddate FROM usa_asc11 WHERE FREQU=p_frequ;
16 if vnct =0 then
17 update usa_asc11
18 set end_date = v_enddate where POLICY_NO = v_pol;
19 else
20 insert into policy_p1(POLICY_NO , START_DATE , TERMS_OF_YEAR, End_date, FREQU)values
21 (1, '2-6-2019', 10, '2-6-2019' , 1);
22 commit;
23 End if;
24 Exception
25 When Others Then
26 Raise_Application_Error(-20000, 'There is no data to be update');
27 End;
28 /
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE I_U_PLOICY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
20/1 PL/SQL: SQL Statement ignored
20/13 PL/SQL: ORA-00942: table or view does not exist
Test case...Test case...Test case...Test case...Test case...Test case...
:-(