Function problem 1

[Crystalboy1]

hi there,

I have written a function to check if a given date is a working day or not. the body of the function is given below.

function IsWorkingDate(datein varchar2) return boolean is
knt integer;
begin
insert into dmo_pwlb_reports_tt_isworking
select f_date
from tblholid_dat hol
where schedule = 'LONDON'
and (substr(datein, 5, 4) = '0101' or substr(datein, 5, 4) = '1225' or
substr(datein, 5, 4) = '1226' or
trim(to_char(to_date(datein, 'YYYYMMDD'), 'DAY')) = 'SUNDAY' or
trim(to_char(to_date(datein, 'YYYYMMDD'), 'DAY')) = 'SATURDAY');

select count(*) into knt from dmo_pwlb_reports_tt_isworking;
if knt = 0 then
return true;
else
return false;
end if;
end IsWorkingDate;



When i try to use the function

select isworkingdate(20081225) from dual



It gives me the following message.

ORA-06552: PL/SQL : Statment ignored
ORA-06553: PLS-382: Expresssion is of wrong type.

The strange thing is, if i test the function, its gives me the right results.

Please help!!!!

 

[SmpTraining]

Is it just that you need to call the function as follows?

select isworkingdate('20081225') from dual

Steve Phillips, Crystal Trainer/Consultant

 

[Crystalboy1]

HI thanks for your reply. Well i did try dat which give me the same results.
ORA-06552: PL/SQL : Statment ignored
ORA-06553: PLS-382: Expresssion is of wrong type.

i got other functions written in same way, and all of them work. Is this the right way for declaration and returning of a boolean datatype ?

 

[SmpTraining]

It appears SQL cannot handle Booleans.

See the link:

http://pandazen.wordpress.com/2008/02/17/ora-06553-pls-382-expression-is-of-wrong-type/

You will need to change your PL/SQL to return a different datatype.

Steve Phillips, Crystal Trainer/Consultant

 

[Crystalboy1]

Thanks.

 

[SantaMufasa]

CrystalBoy,

When I need an otherwise-boolean function to work in SQL, I simply return a character 'Y' or 'N', or 'T' or 'F', or '1' or '0' from the function, then use:

...WHERE <function>(<args>) = 'Y' (or whatever return codes I want)

Let us know if this works as you want in your SQL invocation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”