• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You're still trying to put a PL/SQL type into a SQL call.
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic