Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Naga.

Asked: November 28, 2019 - 3:27 am UTC

Last updated: December 18, 2019 - 6:27 am UTC

Version: 11

Viewed 1000+ times

You Asked

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.

and Connor said...

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...

:-(

Rating

  (2 ratings)

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

Comments

A reader, December 17, 2019 - 3:44 am UTC

One more...

How to insert every nth record into my table , say like if I have 500 rows in my table ..I have to insert of 100th only, 200th only, 300th only...like
Connor McDonald
December 18, 2019 - 6:27 am UTC

test case.....test case.....test case.....test case.....test case.....

Or check out the ROWNUM pseudofunction.

Surprised

A reader, December 17, 2019 - 7:21 pm UTC

I'm surprised you even tried to answer a "please fix my broken code for me" post. Maybe I'm getting too jaded.
Connor McDonald
December 18, 2019 - 6:26 am UTC

Not as jaded as me :-)

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