Skip to Main Content
Forums

Oracle Forms

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Boolean : Number, Char, Varchar2 or Nvarchar2 ?

Mohamed Ali SoltaniNov 27 2017 — edited Nov 28 2017

Hello,

In the source code of the application I'm developing using Oracle Forms, I need to use the boolean type that does not exist in Oracle SQL.

I am a little confused, then, between the use of type Number, Char, Varchar2 or Nvarchar2.

What do you recommend ?
This post has been answered by Amatu Allah Neveen Ebrahim on Nov 27 2017
Jump to Answer

Comments

Holger.Lehmann

Hi,

you have to transfer either a number (1,0) or varchar2(Y, N) to boolean true or false.

But always take the same solution for the whole application, keeps things easier.

Regards

Holger

1 person found this helpful

Al-Salamu Alikum We Rahmatu Allah We Barakatu,

Mohmed, what are you trying to do ?

May be you need to use Boolean in a function logic from forms or db. No  ?

If you need such a flag for such a logic (yes or no) you can use a Number or a Char Datatype.

Then, if the value column  is 1 then return Boolean function with TRUE & vise versa.

If it is 0 You can return False.

need to change the db item types.

Amatu Allah.

1 person found this helpful
Mohamed Ali Soltani

Hi @"Holger.Lehmann",

W Alikum Al-Salamu We Rahmatu Allah We Barakatu @"Amatu Allah Neveen Ebrahim",

Thank you for your answer.

My main question is : Does any of both can make a bug later ? Does both have same effect on performance ?

Holger.Lehmann

Hi Mohamed,

regarding performance this should have no impact.

In most cases I've seen usage of a varchar2(1) flag.

And in my current project there is a PL/SQL function, that converts Boolean values to a varchar2(1) flag.

  FUNCTION myBool_To_Flag_Function (PBoolean IN boolean) RETURN varchar2 IS

1 person found this helpful
Mohamed Ali Soltani

Hi Holger,

I haven't thought about it. It's really a good idea.

Thank you !

Amatu Allah Neveen Ebrahim
Answer

   You don't have to think about any database field as a Boolean value.

Just create a db field as Number, Char or Varchar2 datatype.

Number data type is a db type which i prefer it's faster than other data types.

I recommend using a pl/sql function with a Boolean return value using if statement to switch between two values either 0 or 1.

-------------------------------------------------------------

EXAMPLE:

========

FUNCTION TEST RETURN BOOLEAN IS
v_flage  NUMBER;
BEGIN
     BEGIN
      SELECT your_db_flg
      INTO  v_flage  
      FROM  your_table
     WHERE -- condition--- ;
  EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN 0;
  END;
 IF v_flage   = 1 THEN
     
           RETURN TRUE;
 ELSIF v_flage  = 0 THEN
     
         RETURN FALSE;
 END IF;
END;

Amatu Allah.

Marked as Answer by Mohamed Ali Soltani · Sep 27 2020
1 person found this helpful
Mohamed Ali Soltani

Thank you

You are welcome.

Andreas Weiden

Would you mind prooving this statement?

- Number data type is a db type which i prefer it's faster than other data types.

I don't think that there is any remarkable performance-difference with using a a NUMBER storing 0 and and a VARCHAR storing Y or N

A datatype decision should always be made to best fit its purpose.

Hello Andreas,

I agree.

Thanks for your comments.

1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 26 2017
Added on Nov 27 2017
#forms, #moved-from-devops
10 comments
2,088 views