Skip to Main Content
  • Questions

  • Calling function from SQL Statement with boolen parameter

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rafael.

Asked: January 27, 2005 - 1:31 pm UTC

Last updated: January 31, 2007 - 1:25 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,


I have a function with these parameters:

FUNCTION reserva_anterior RETURNS DATE
Argument Name Type In/Out Default?
------------------------------ ------------------ ------ --------
DATA DATE IN
FER_MTM BOOLEAN IN DEFAULT
NRO_RESERVA NUMBER IN DEFAULT
FER_NY BOOLEAN IN DEFAULT
FER_LONDON BOOLEAN IN DEFAULT

when i call this function

SELECT reserva_anterior(dt_vencimento_oper,null,2,TRUE) ,
TBRM_OPER.DT_VENCIMENTO_OPER,
TBMD_PER.DT_REF,
TBRM_CR.DS_LOCATION,
TBRM_CR.DS_SUBSIDIARY .....


I get this message :

SELECT reserva_anterior(dt_vencimento_oper,null,2,TRUE) ,
*
ERROR at line 1:
ORA-00904: invalid column name

In the other hand, passing 'null' instead of TRUE or FALSE, it runs well.

Thank you..

Rafael

and Tom said...

SQL cannot deal with BOOLEANS, boolean is known only to plsq.

you cannot pass true or false from SQL to plsql.

in facdt, you cannot bind true or false with plsql from any language other than plsql.

you will need to write a wrapper routine that calles this, the wrapper will accept a type that SQL understand such as a number or a varchar2.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

David Phillips, January 29, 2007 - 11:29 am UTC

How does SQL*Plus execute this command?

EXEC DBMS_JOB.BROKEN(1234, FALSE);

Am I correct in assuming that it is using a special OCI call and not simply translating it into a SQL CALL?
Tom Kyte
January 31, 2007 - 1:25 pm UTC

I'm not sure what you mean by that.

Chris, February 01, 2007 - 2:03 am UTC

SQL*Plus will execute it as:

BEGIN
DBMS_JOB.BROKEN(1234, FALSE);
END;

It is not an SQL statement, but a PL/SQL block.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.