Further Processing of data as returned in given dbms_output

Question

**I have created plsql program which will give the output date of certain date as dbms_output Now i want to calculate the difference between two dates of dbms_output, how can i use this in dbms_output englishcalendar - lag(englishcalendar)(over order by englishcalendar) **

SET SERVEROUTPUT ON;
declare

start_date    date:='01-02-2019';
end_date       date:='01-05-2019';

cursor c_data is
    SELECT
        cregflow6.*
    FROM
    cregflow6
    WHERE
    englishcalendar BETWEEN start_date
    AND  end_date
    and  m_flg ='M'
    or englishcalendar= end_date
    or englishcalendar= start_date ;

type t__data is table of c_data%rowtype index by binary_integer;
t_data t__data;

begin

open c_data;
loop
    fetch c_data bulk collect into t_data limit 10000;

    exit when t_data.count = 0;

    for i in t_data.first .. t_data.last loop
        dbms_output.put_line(t_data(i).englishcalendar);    
    end loop;

end loop;
close c_data;

end;
/

SAMPLE OF DBMS_OUTPUT

EXPECTED IN DBMS_OUTPUT

01-02-2019          0
12-02-2019          11          [01-02-2019-12-02-2019  ]
14-03-2019          33          [12-02-2019 -14-03-2019 ]
13-04-2019          32          [14-03-2019 -13-04-2019 ]
01-05-2019          17          [13-04-2019 - 01-05-2019]

START DATE AND END DATE MAY VARY IN DIFFERENT CONDITION

Yor date strings are day-month-year? Can you explain the "expected in dbms_output" info? — alvalongo
– alvalongo, Commented May 22, 2019 at 20:45

hotfix · Accepted Answer · 2019-05-24 07:49:29Z

2

you can declare a variable where you will save last date. you can use it in the next iteration.

declare
  v_last_date date; -- is null
  ...
begin
  ...

  for i in t_data.first .. t_data.last loop
    if v_last_date is null then

      dbms_output.put_line(to_char(t_data(i).englishcalendar,'dd-mm-yyyy')||' 0' ); 
    else
      dbms_output.put_line(to_char(t_data(i).englishcalendar,'dd-mm-yyyy')||' ' 
                          ||to_char(t_data(i).englishcalendar - v_last_date)
                          ||' [' ||to_char(t_data(i).englishcalendar,'dd-mm-yyyy')||'-'
                          || to_char(v_last_date ,'dd-mm-yyyy')||']' ); 
    end if;
    v_last_date := t_data(i).englishcalendar; -- save a date of the iteration   
  end loop;
end;

i didn't test it.

edited May 24, 2019 at 7:49

answered May 22, 2019 at 18:47

hotfix

3,39624 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suman Over a year ago

I got this errror during execution PLS-00306: wrong number or types of arguments in call to ' SYS$EXTRACT_FROM' @hotfix

hotfix Over a year ago

@Suman i have edit my answer. just do subtraction without extract

Collectives™ on Stack Overflow

Further Processing of data as returned in given dbms_output

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related