Home » SQL & PL/SQL » SQL & PL/SQL » Exception Handling problem
Exception Handling problem [message #280703] Wed, 14 November 2007 10:04 Go to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
Hi!

Just for practise I created this procedure:

procedure true_false(my_bool boolean) AS
begin
  if my_bool then
    dbms_output.put_line('true');
  elsif not my_bool then
    dbms_output.put_line('false');
  end if;
  
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Error');
end true_false;

Now, if I call the procedure with true_false(true) then everything is allright (output: true). If I call the procedure with true_false(true) then also everything is allright.
BUT if I call the procedure with true_false(tru) or true_false(fal) then the exception handler should catch this error but it doesn't.

Please tell my what did I wrong to catch this error? Or how to create the right exception?

thanks a lot

Alex
Re: Exception Handling problem [message #280705 is a reply to message #280703] Wed, 14 November 2007 10:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There are only 3 values you can pass to the procedure TRUE, FALSE and NULL.

If you try to call it passing a string 'TRU' then you'll get an error in the calling procedure telling you that you're passing the wrong datatype.
Re: Exception Handling problem [message #280706 is a reply to message #280703] Wed, 14 November 2007 10:18 Go to previous messageGo to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
Actually this is what I wanted to catch with an exception. So, can I catch this error with exceptions?

[Updated on: Wed, 14 November 2007 10:20]

Report message to a moderator

Re: Exception Handling problem [message #280708 is a reply to message #280706] Wed, 14 November 2007 10:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm assuming that you're calling this function from an anonymous pl/sql block, and getting an error like this:
ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'BOOL_TEST'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored


This is a compile time error, not an error encountered when the code is run, and is not trappable.
Re: Exception Handling problem [message #280711 is a reply to message #280703] Wed, 14 November 2007 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Actually this is what I wanted to catch with an exception. So, can I catch this error with exceptions?

But you have an exception but it is raise BEFORE you enter your procedure so you can't catch it INSIDE the procedure.

If you explain what is the REAL problem maybe we can help you.

Regards
Michel
Re: Exception Handling problem [message #280720 is a reply to message #280703] Wed, 14 November 2007 10:32 Go to previous messageGo to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
OK, thank you for trying.
The problem is easy to understand.

I just want to build a procedure where I use a boolean as a parameter (like in my example above).
So how to catch an error when the users types instead "false" or "true" something wrong: tru, fals, alse, ... ?
I just want to catch this error, so that the user gets a message something like: "The parameter tru is not boolean. Please use a boolean value (true, false)!"

Hope you understand my problem.
Re: Exception Handling problem [message #280729 is a reply to message #280720] Wed, 14 November 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

just want to build a procedure where I use a boolean as a parameter

No you don't want that.
You want something and you think you can solve it with that.
So what is the REAL, ORIGINAL need?

Regards
Michel
Re: Exception Handling problem [message #280733 is a reply to message #280703] Wed, 14 November 2007 10:56 Go to previous messageGo to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
No, it is true. I'm a beginner and try to understand PL/SQL.
I started to learn exceptions and don't know how to handle the problem I explained above.
But I think it is not possible to solve this problem because it is a runtime error as explained above.
Re: Exception Handling problem [message #280735 is a reply to message #280720] Wed, 14 November 2007 11:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're passing a string that the users type in as a parameter to this procedure, then you'll get this error every time no matter what they type, unless your front end software intervenes.

The string 'TRUE' is never a boolean TRUE
The string 'FALSE' is never a boolean FALSE
Even to_char(null) is not a boolean NULL
Re: Exception Handling problem [message #280738 is a reply to message #280703] Wed, 14 November 2007 11:09 Go to previous messageGo to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
I now that and that is the problem that I DON'T want to pass it as a string but as a real boolean. Look at the example-code above!
Re: Exception Handling problem [message #280746 is a reply to message #280738] Wed, 14 November 2007 11:37 Go to previous message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But if you want to pass a boolean you can only pass TRUE, FALSE or NULL nothing else.
It is the same thing that when an ATM ask you to enter your code and you say I want to enter a A. How do you do this?

Regards
Michel
Previous Topic: Dynamic Column Name
Next Topic: Use of Case in Order by
Goto Forum:
  


Current Time: Mon Dec 05 04:56:29 CST 2016

Total time taken to generate the page: 0.25342 seconds