Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What's wrong in this PL/SQL function, its not working

 
Yogesh Gandhi
Ranch Hand
Posts: 226
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


select isHoliday(sysdate) from dual;

eRROR i GET IS

Error starting at line 1 in command:
select isHoliday(sysdate) from dual
Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PL S-382: expression is of wrong type
06552. 00000 - "PL/SQL: %s"
*Cause:
*Action:
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yogesh Gandhi wrote:SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PL S-382: expression is of wrong type

Pure SQL doesn't understand the boolean data type, although PL/SQL does. So although the function will work if invoked from another PL/SQL block, it won't work in a simple SQL SELECT statement.

So if you need to use it in SQL SELECT statements, use the integer data type as return value and return 0 or 1 instead.
 
Yogesh Gandhi
Ranch Hand
Posts: 226
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Yogesh Gandhi wrote:SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PL S-382: expression is of wrong type

Pure SQL doesn't understand the boolean data type, although PL/SQL does. So although the function will work if invoked from another PL/SQL block, it won't work in a simple SQL SELECT statement.

So if you need to use it in SQL SELECT statements, use the integer data type as return value and return 0 or 1 instead.



Thanks Roel,
But when I try to run it in PL/SQL block as well...

as follows




It still doesn't works and says

Error starting at line 1 in command:
declare
x boolean;
begin
select isHoliday(sysdate) into x from dual;
end;
Error report:
ORA-06550: line 4, column 32:
PL S-00382: expression is of wrong type
ORA-06550: line 4, column 8:
PL S-00382: expression is of wrong type
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/ SQL compilation error.
*Action:
 
Dave Tolls
Ranch Hand
Posts: 2107
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're still trying to put a PL/SQL type into a SQL call.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yogesh Gandhi wrote:It still doesn't works and says

Because you are still using a SELECT statement to select an unknown type (as Dave already mentioned as well). The SELECT statement is causing your issue, not the fact that it was not inside a PL/SQL block.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Like Dave and Roel said, you need to use your function correctly. You are returning a PL/SQL data-type (Boolean), so you need to call it like a PL/SQL function:

If you want to use the function in a SQL SELECT, then you need to return a data-type that SQL understands e.g. return 1 or 0.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic