How to increase the sysdate on each variable passed of fun.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

shoan
Senior Member
Posts: 123
Joined: Thu Mar 17, 2011 6:27 am

How to increase the sysdate on each variable passed of fun.

Postby shoan » Wed Apr 23, 2014 1:20 pm

Hi Team,

Can any one suggest me on the below scenarios ?

The below BLOD mark data in plsql code needs to be changed (dynamically paassed the id and date will increase for each id ).

o_payload := ACTIVITYTRACKER.ACTIVITYTRACKER_EXPORTCHANGE(102301, SYSDATE,1,2,5000);

The first parameter should be from a list of ids(below) and for each of these ids , The date should increment from Sep 1, 2013 to March 31, 2014.

{102301,11,12,1938393,230388,420,400,1,444455,2332,700,2028,}

Code: Select all

DECLARE
r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle RAW(16);
o_payload ACTIVITYTRACKER.ACTIVITYTRACKER_EXPORTCHANGE;
BEGIN
o_payload := ACTIVITYTRACKER.ACTIVITYTRACKER_EXPORTCHANGE(102301, SYSDATE,1,2,5000);
DBMS_AQ.ENQUEUE(
queue_name => 'ACTIVITYTRACKER.EXPORT_QUEUE',
enqueue_options => r_enqueue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
COMMIT;
END;
/


Thanks in Advance
Shoan

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: How to increase the sysdate on each variable passed of

Postby Tim... » Wed Apr 23, 2014 3:04 pm

Hi.

Sorry. I'm not sure what you are asking for. Do you just mean you want that call in a loop?

Code: Select all

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab t_tab;
  l_start_date DATE := TO_DATE('01-SEP-2013','DD-MON-YYYY');
 
   r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
   r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
   v_message_handle RAW(16);
   o_payload ACTIVITYTRACKER.ACTIVITYTRACKER_EXPORTCHANGE;
BEGIN
  l_tab := t_tab(102301,11,12,1938393,230388,420,400,1,444455,2332,700,2028);
 
  FOR i IN l_tab.FIRST .. l_tab.LAST LOOP
    DBMS_OUTPUT.put_line(l_tab(i) || ' : ' || TO_CHAR(l_start_date+i-1, 'DD-MON-YYYY'));
   
     o_payload := ACTIVITYTRACKER.ACTIVITYTRACKER_EXPORTCHANGE(l_tab(i), l_start_date+i-1,1,2,5000);
     DBMS_AQ.ENQUEUE(
         queue_name => 'ACTIVITYTRACKER.EXPORT_QUEUE',
         enqueue_options => r_enqueue_options,
         message_properties => r_message_properties,
         payload => o_payload,
         msgid => v_message_handle
      );
      COMMIT;
  END LOOP;
END;
/


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://oracle-base.com
My blog: http://oracle-base.com/blog

shoan
Senior Member
Posts: 123
Joined: Thu Mar 17, 2011 6:27 am

Re: How to increase the sysdate on each variable passed of

Postby shoan » Wed Apr 23, 2014 5:49 pm

Hi Team,

Thanks a lot for the quick reply!!

Now the i got the o/p like this .

102301 : 01-SEP-2013
11 : 02-SEP-2013
12 : 03-SEP-2013
1938393 : 04-SEP-2013
230388 : 05-SEP-2013
420 : 06-SEP-2013
400 : 07-SEP-2013
1 : 08-SEP-2013
444455 : 09-SEP-2013
2332 : 10-SEP-2013
700 : 11-SEP-2013
2028 : 12-SEP-2013

But the out put shoud be like this as per the requirement .

for eg (id :-102301)

102301 : 01-SEP-2013
102301 : 02-SEP-2013
102301 : 03-SEP-2013
102301 : 04-SEP-2013
…. All the way to
102301 : 31-MAR-2014


For eg the id:-11

11 : 01-SEP-2013
11: 02-SEP-2013
11: 03-SEP-2013
11: 04-SEP-2013
….All the way to
11 : 31-MAR-2014

Thanks
Shoan

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: How to increase the sysdate on each variable passed of

Postby Tim... » Wed Apr 23, 2014 7:12 pm

Hi.

Oh. So you want each element for all dates... That should be like this then.

Code: Select all

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab             t_tab;
  l_start_date    DATE := TO_DATE('01-SEP-2013','DD-MON-YYYY');
  l_end_date      DATE := TO_DATE('31-MAR-2014','DD-MON-YYYY');
  l_days          NUMBER := l_end_date - l_start_date;
 
   r_enqueue_options       DBMS_AQ.ENQUEUE_OPTIONS_T;
   r_message_properties    DBMS_AQ.MESSAGE_PROPERTIES_T;
   v_message_handle          RAW(16);
   o_payload                   ACTIVITYTRACKER.ACTIVITYTRACKER_EXPORTCHANGE;
BEGIN
  l_tab := t_tab(102301,11,12,1938393,230388,420,400,1,444455,2332,700,2028);
 
  FOR i IN l_tab.FIRST .. l_tab.LAST LOOP
    FOR days IN 0 .. l_days LOOP
       DBMS_OUTPUT.put_line(l_tab(i) || ' : ' || TO_CHAR(l_start_date+days, 'DD-MON-YYYY'));
      
        o_payload := ACTIVITYTRACKER.ACTIVITYTRACKER_EXPORTCHANGE(l_tab(i), l_start_date+days,1,2,5000);
        DBMS_AQ.ENQUEUE(
            queue_name => 'ACTIVITYTRACKER.EXPORT_QUEUE',
            enqueue_options => r_enqueue_options,
            message_properties => r_message_properties,
            payload => o_payload,
            msgid => v_message_handle
         );
         COMMIT;
      END LOOP;
  END LOOP;
END;
/


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://oracle-base.com
My blog: http://oracle-base.com/blog

shoan
Senior Member
Posts: 123
Joined: Thu Mar 17, 2011 6:27 am

Re: How to increase the sysdate on each variable passed of

Postby shoan » Thu Apr 24, 2014 3:55 pm

Hi Team,

Thank you very much!! This worked perfectly.

Thanks
Shoan

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: How to increase the sysdate on each variable passed of

Postby Tim... » Thu Apr 24, 2014 7:04 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://oracle-base.com
My blog: http://oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests