Oracle : Eliminate the error

Question

I have the holiday table whose description are

   Name                                      Null?    Type

 HOLIDAYDATE                                        DATE
 DESCRIPTION                                        VARCHAR2(20)

which contains data are

HOLIDAYDA DESCRIPTION
--------- --------------------
19-JAN-11 to
17-JAN-11 to
10-JAN-11 a

here is my code :

DECLARE a date;
SELECT holidaydate 
  INTO a 
  FROM holiday 
 WHERE holidaydate = SYSDATE;
 DBMS_OUTPUT.PUT_LINE(a);

i am receiving error like this.

ERROR at line 2: ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: begin function package pragma procedure subtype type use form current cursor

Can anyone tell me what is the problem in my code?

GolezTrol GolezTrol · Accepted Answer · 2011-01-10T06:42:39

declare 
  a date;
begin
  select holidaydate into a from holiday where holidaydate = sysdate;
  dbms_output.put_line(a);
end;

But this will probably give you a new error (no data found), because there probably is no record that has Sysdate entered in Holidaydate, because sysdate includes the time as well.

So try this:

declare 
  a date;
begin
  select 
    (select holidaydate from holiday where holidaydate = trunc(sysdate))
  into a 
  from dual;

  dbms_output.put_line(a);
end;

That will cut off the time part, checking for whole dates. If there are whole dates in your table as well, this will more likely give a result. If not, the select from dual will capture that, and make a return NULL.

The only error you may get now, is when there are more than one records containing the same date.

Oracle : Eliminate the error

1 Answers