CODEWITHSUNDEEP
oracle-databaseplsql
fearox
fearox

Reputation: 31

pl/sql block, weird errors with simple script

I'm writing very simple block code in pl/sql:

DECLARE
  dateof21 DATE;
  dzien number;
  dzien_tyg number;
BEGIN

dateof21:= '2001-01-01';

WHILE dateof21 != '2101-01-01' LOOP

 SELECT EXTRACT(day from date dateof21) INTO dzien from dual;

 select to_char(date dateof21,'D') INTO dzien_tyg from dual;

if ((dzien=13) AND (dzien_tyg=5)) THEN

  dbms_output.put_line(to_char(dateof21));

end if;

  dateof21:= dateof21+1;

END LOOP;

END;

but i'm getting very annoying errors:

ORA-06550: linia 8, kolumna 26:
PL/SQL: ORA-00936: brak wyrażenia
ORA-06550: linia 8, kolumna 2:
PL/SQL: SQL Statement ignored
ORA-06550: linia 9, kolumna 17:
PL/SQL: ORA-00936: brak wyrażenia
ORA-06550: linia 9, kolumna 2:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"

I really tried to find whats wrong, but everything seems just fine. Can anybody help? it should write on output all fridays which are 13th day of month btw.

Upvotes: 1

Views: 66

Answers (1)

user330315
user330315

Reputation:

There are many things wrong in your code:

First: you should use proper date literals. I prefer ANSI literals, like DATE '2001-01-01' but you can also use the to_date()function: `to_date('2001-01-01', 'yyyy-mm-dd')

Second: to_char() returns a varchar value, not a number. So you can't assign the result of that to a number variable, you need to use to_number(to_char(dateof21,'D')).

You also don't need to use select ... into to call a function.

And finally: the extract() method does not require the use of a date prefix: EXTRACT(day from dateof21)

Putting that all together, gives us:

DECLARE
  dateof21 DATE;
  dzien number;
  dzien_tyg number;
BEGIN
  dateof21 := date '2001-01-01';
  -- alternatively: dateof21 := to_date('2001-01-01', 'yyyy-mm-dd');

  WHILE dateof21 <> DATE '2101-01-01' 
  LOOP
    dzien := EXTRACT(day from dateof21);
    dzien_tyg := to_number(to_char(dateof21,'D'));

    if ((dzien=13) AND (dzien_tyg=5)) THEN
      dbms_output.put_line(to_char(dateof21, 'yyyy-mm-dd'));
    end if;

    dateof21 := dateof21+1;
  END LOOP;
END;
/

Note that the return value of to_char(dateof21,'D') depends on your NLS settings. You can't rely on it to always return 5 for a friday in all configurations (e.g. on my computer it returns 6).

Upvotes: 1

Related Questions