This forum is currently locked. You can't register or post questions at this time. (read more)

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://www.oracle-base.com
My blog: http://www.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://www.oracle-base.com
My blog: http://www.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://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron