2 Replies Latest reply: Dec 19, 2013 5:26 AM by Jagdeep Sangwan RSS

    error while assigning dates to associative array of date type

    Jagdeep Sangwan

      Hi All,

       

      I am facing the issue while assigning dates to associative array of date type:

       

      Oracle Version:

      SQL> select * from v$version;

      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
      PL/SQL Release 10.2.0.1.0 - Production
      CORE    10.2.0.1.0      Production
      TNS for Linux: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production

       

      Stored procedure i am trying to write is as following

       

      1. create or replace procedure jp1 (
      2. p_start_date date default trunc(sysdate,'MM')
      3. , p_end_date date default trunc(sysdate)
      4. )
      5. is
      6. l_no_of_days number;
      7. type t_date_id is table of date
      8. index by pls_integer;
      9. l_date_id_arr t_date_id;
      10. begin
      11. l_no_of_days := p_end_date - p_start_date;
      12. for i in 0
      13. .. l_no_of_days - 1
      14. loop
      15.     l_date_id_arr := p_start_date + i;
      16.     dbms_output.put_line(p_start_date + i);
      17. end loop;
      18. end;
      19. /

      I am getting error at line 14 while compiling this. and the error message is as following:

      Errors for PROCEDURE JP1:

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      14/5     PL/SQL: Statement ignored
      14/22    PLS-00382: expression is of wrong type

       

      So while investigating this i tried to output the value of (p_start_date + i) using dbms_output.put_line and the output is date itself.

      1. create or replace procedure jp1 (
      2. p_start_date date default trunc(sysdate,'MM')
      3. , p_end_date date default trunc(sysdate)
      4. )
      5. is
      6. l_no_of_days number;
      7. type t_date_id is table of date
      8. index by pls_integer;
      9. l_date_id_arr t_date_id;
      10. begin
      11. l_no_of_days := p_end_date - p_start_date;
      12. for i in 0 .. l_no_of_days-1

      13. loop
      14.     --l_date_id_arr := p_start_date + i;
      15.     dbms_output.put_line(p_start_date + i);
      16. end loop;
      17. end;
      18. /

      output of the

      1. exec jp1

      is as following:

      01-DEC-13

      02-DEC-13

      03-DEC-13

      04-DEC-13

      05-DEC-13

      06-DEC-13

      07-DEC-13

      08-DEC-13

      09-DEC-13

      10-DEC-13

      11-DEC-13

      12-DEC-13

      13-DEC-13

      14-DEC-13

      15-DEC-13

      16-DEC-13

      17-DEC-13

      18-DEC-13

       

      I see the output as date itself. so why it is throwing error while assigning the same to associative array of date type.

      I tried to google also for the same but to no avail.

       

      Any help in this regard is appreciated or any pointer some other thread on internet or in this forum.

       

      Thanks in advance

      Jagdeep Sangwan