Oracle : receiving ORA-06550 and PLS-00905

Question

i have a holiday table which contains the data are

    HOLIDAYDA DESCRIPTION
   --------- --------------------
   19-JAN-11 to
   17-JAN-11 to
   10-JAN-11 new day

Now I want the first business day of the week. IE: If I pass "12-JAN-2011" as input, I want the o/p as 11-JAN-2011 as the 1st business day because 10-JAN-2011 is holiday.

here is my code :

create or replace procedure sample as
   l_dStartDay date;
   l_dHolidayDate date;
begin

    select trunc(to_date(sysdate),'Day') 
      into l_dStartday 
      from dual;

 dbms_output.put_line('first day of the week ');
 dbms_output.put_line(l_dStartDay);

 for i in 2..5 Loop
   select holidaydate 
     from holiday 
     into l_dHolidayDate 
    where holidaydate = (l_dStartDay + i);

  if(l_dHolidaydate is null) then
    dbms_output.put_line(l_dStartDay+i);
  end if;
exit;
end loop;
end;

i compiled the above program but with "Procedure created with compilation errors."

Newly Added : Compliation errors :

 LINE/COL ERROR
 -------- -----------------------------------------------------------------
 9/1      PL/SQL: SQL Statement ignored
 9/33     PL/SQL: ORA-00933: SQL command not properly ended

Error:

BEGIN sample; END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.SAMPLE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

can any one tell me the reason for the error? if possible tell me the solution?

Answer 1

"i compiled the above program but with Procedure created with compilation errors"

If you are using an IDE such as TOAD or SQL Developer it would show the compilation errors automatically. Otherwise they are accessible in SQL*Plus using this command:

SQL>    show errors

There are also views such as USER_ERRORS which we can query.

The problem is most likely the SELECT statement, as the INTO clause should follow immediately after the projection:

   select holidaydate 
     into l_dHolidayDate 
     from holiday 
    where holidaydate = l_dStartDay + i);

---

Mind you, this also looks wrong:

select trunc(to_date(sysdate),'Day') 

SYSDATE is a DATE already, although the more recent versions of Oracle tend to be more forgiving of using TO_DATE on a DATE column. When truncating the time element from a date it is not necessary to include a format mask as this is the default behaviour:

trunc(some_date_variable)

We only need to include a mask if (say) we want the first day of the month:

trunc(some_date_variable, 'MON')

If you want to find the first day of the week, this will do it:

SQL> select
  2      trunc(to_date('01-DEC-2010', 'DD-MON-YYYY'), 'D') start_of_wk
  3  from dual
  4  /

START_OF_
---------
29-NOV-10

SQL>

Note that the first day of the week is dependent on the territory setting. In some territories the first day of the week is a working day (for instance Monday in the UK) in others it is not (Sunday is day 1 in the US). So it may be necessary to add an offset.

---

Once you solve the compilation errors you'll find soem runtime errors, probably relating to unhandled NO_DATA_FOUND exceptions. This is because your lookup query won't return NULL when it doesn't find a matching record, it will fail.

This is a simple procedure. It uses a SQL solution, because SQL is the most efficient way of doing things. The inner query uses the CONNECT BY trick to generate a result set of dates. This is then reduced by the MINUS set operator, which will filter out any holidays in that week's range. Finally the outer query returns the earliest date from the query.

create or replace procedure get_first_working_day 
    ( p_tgt_date in date )
is
    l_st_day date := trunc(p_tgt_date, 'D');
    l_working_day date := trunc(p_tgt_date, 'D');
begin
    dbms_output.put_line('first day of week = '||l_st_day);

    select min(day_of_wk)
    into l_working_day
    from ( select l_st_day + (level-1) as day_of_wk
           from dual
           connect by level <= 5
           minus
           select holidaydate
           from hols
           where holidaydate between l_st_day and l_st_day + 4 );

    dbms_output.put_line('first working day of week = '||l_working_day
                        ||'::'|| to_char(l_working_day, 'DAY'));

end get_first_working_day;
/

Given this test data (which reflects the byzantine state of British bank holidays) ...

SQL> select holidate from hols
  2  order by 1
  3  /

HOLIDAYDA
---------
25-DEC-10
26-DEC-10
27-DEC-10
28-DEC-10
01-JAN-11
03-JAN-11

6 rows selected.

SQL>

... here's the procedure in action:

SQL> set serveroutput on size unlimited
SQL>
SQL> exec get_first_working_day (sysdate)
first day of week = 10-JAN-11
first working day of week = 10-JAN-11::MONDAY

PL/SQL procedure successfully completed.

SQL>
SQL> exec get_first_working_day (to_date( '04-JAN-2011', 'DD-MON-YYYY'))
first day of week = 03-JAN-11
first working day of week = 04-JAN-11::TUESDAY

PL/SQL procedure successfully completed.

SQL>
SQL> exec get_first_working_day (to_date( '01-JAN-2011', 'DD-MON-YYYY'))
first day of week = 27-DEC-10
first working day of week = 29-DEC-10::WEDNESDAY

PL/SQL procedure successfully completed.

SQL>

---

Incidentally, this is very bad practice:

PLS-00905: object SYSTEM.SAMPLE is invalid

Don't use the built-in SYS or SYSTEM accounts for your own work. There is too great a chance of breaking something. Create a new user account instead.