OraFAQ Forum: SQL & PL/SQL » Function with Boolean as IN Parameter

Home » SQL & PL/SQL » SQL & PL/SQL » Function with Boolean as IN Parameter

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Function with Boolean as IN Parameter [message #157560]

Fri, 03 February 2006 05:32

yraghavendra
Messages: 27
Registered: August 2005
Location: India

Junior Member

Hi,

I have the function as below:

1 create or replace function f1(a number)
2 return varchar2
3 as
4 begin
5 IF a >10 then
6 return 'Y';
7 ELSE return 'N';
8 end if;
9* end;
SQL> /

Function created.

I can insert into the table t1 as

SQL> insert into t1 values(f1(11));

1 row created.

select * from t1;
SQL> select * from t1;

A
--------------------
Y

Now I modify the function which accepts the input as Boolean
It throws the following eror:

SQL> create or replace function f1(a boolean)
2 return varchar2
3 as
4 begin
5 IF a = true then
6 return 'Y';
7 ELSE return 'N';
8 end if;
9 end;
10 /

Function created.

SQL> insert into t1 values(f1(true));
ERROR at line 1:
ORA-00984: column not allowed here

can u tell me the solution for this.

Thanks

Report message to a moderator

Re: Function with Boolean as IN Parameter [message #157562 is a reply to message #157560]

Fri, 03 February 2006 05:34

Maaher
Messages: 7065
Registered: December 2001

Senior Member

BOOLEAN is not a valid datatype in SQL. In PL/SQL, however, it is.

MHE

Report message to a moderator

Re: Function with Boolean as IN Parameter [message #157566 is a reply to message #157562]

Fri, 03 February 2006 05:56

yraghavendra
Messages: 27
Registered: August 2005
Location: India

Junior Member

Hello MHE,

Can u tell me the solution in pl/sql.
Actually we are getting the input as boolean from the JAVA end.
How do i validate it in the oracle for true or false values of input as 'y' or 'N'.

Thanks

Report message to a moderator

Re: Function with Boolean as IN Parameter [message #157570 is a reply to message #157566]

Fri, 03 February 2006 06:10

Maaher
Messages: 7065
Registered: December 2001

Senior Member

What I meant was: your function is created properly, as it is a PL/SQL function. Once you try to call it from a SELECT statement (=SQL), you end up with the error you got.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION f1(pib_yn BOOLEAN)
  2    return varchar2 DETERMINISTIC
  3  AS
  4  BEGIN
  5    IF pib_yn = true THEN
  6      return 'Y';
  7    ELSE
  8      return 'N';
  9    END IF;
 10* END;
SQL> /

Function created.

SQL> SELECT f1(true)
  2    FROM dual
  3  /
SELECT f1(true)
          *
ERROR at line 1:
ORA-00904: "TRUE": invalid identifier


SQL> set serverout on
SQL> begin
  2    dbms_output.put_line(f1(true));
  3  end;
  4  /
Y

PL/SQL procedure successfully completed.

As you see, the second call to F1 succeeds because it is a PL/SQL call and not a SQL call like the first one.

Anyway, I'd make the conversion true -> 'Y', false -> 'N' in Java and pass this VARCHAR2 to the database.

MHE

Report message to a moderator

Re: Function with Boolean as IN Parameter [message #157578 is a reply to message #157560]

Fri, 03 February 2006 07:04

yraghavendra
Messages: 27
Registered: August 2005
Location: India

Junior Member

Hi MHE,

Can u tell me any other solution other than changing tha boolean data type in java to varchar2.
This is because it is very late and the java team cannot change the design of transaction objects(TO) which we are getting as input.

Thanks

Report message to a moderator

Re: Function with Boolean as IN Parameter [message #157582 is a reply to message #157578]

Fri, 03 February 2006 07:33

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Nope, not directly. Like I said. PL/SQL does handle it, but SQL not. You could perform the insert in a procedure:

SQL> CREATE TABLE mhe_foo(thecol VARCHAR2(1))
  2  /

Table created.

SQL> 
SQL> CREATE PROCEDURE ins_mhe_foo(pib_val IN BOOLEAN)
  2  IS
  3    v_bool VARCHAR2(1);
  4  BEGIN
  5    -- If it's true -> set to 'Y'
  6    IF pib_val THEN
  7      v_bool := 'Y';
  8    ELSE-- not true -> set to 'N'
  9      v_bool := 'N';
 10    END IF;
 11   
 12    -- The insert, wrapped within my procedure 
 13    INSERT INTO mhe_foo VALUES(v_bool);
 14  END ins_mhe_foo;
 15  /

Procedure created.

SQL> 
SQL> EXEC ins_mhe_foo(true)

PL/SQL procedure successfully completed.

SQL> 
SQL> EXEC ins_mhe_foo(false)

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> 
SQL> SELECT *
  2    FROM mhe_foo
  3  /

T
-
Y
N

SQL> 
SQL> DROP PROCEDURE ins_mhe_foo
  2  /

Procedure dropped.

SQL>   
SQL> DROP TABLE mhe_foo
  2  /

Table dropped.

Now you can call the insert procedure from Java with a boolean.

MHE

Report message to a moderator

Previous Topic:	data flow performance problem
Next Topic:	tokenizing strings

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 03 00:10:38 CDT 2025