Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Naga.

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

Answered by: Connor McDonald - Last updated: December 18, 2019 - 6:27 am UTC

Category: PL/SQL - Version: 11

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: About terminating characters for SQL statements

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

:-(

and you rated our response

  (2 ratings)

Reviews

December 17, 2019 - 3:44 am UTC

Reviewer: A reader

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

Followup  

December 18, 2019 - 6:27 am UTC

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

Or check out the ROWNUM pseudofunction.

Surprised

December 17, 2019 - 7:21 pm UTC

Reviewer: A reader

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

Followup  

December 18, 2019 - 6:26 am UTC

Not as jaded as me :-)

More to Explore

PL/SQL

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