Home » SQL & PL/SQL » SQL & PL/SQL » the EXCEPTION didn't work correctly (10g , TOAD V.9 , win XP SP2)
icon5.gif  the EXCEPTION didn't work correctly [message #307084] Mon, 17 March 2008 16:08 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I made that code :-
CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
   RETURN NUMBER
IS
   sal   NUMBER;
BEGIN
   SELECT salary
     INTO sal
     FROM employees
    WHERE employee_id = v_id;

   RETURN sal;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
END;
/


and when I executed it like that :-

SQL> BEGIN
  2     DBMS_OUTPUT.put_line (qq (942));
  3  END;
  4  /
NO such ID for any Employee in this company
BEGIN
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "HR.QQ", line 10
ORA-06512: at line 2


SQL> ed
Wrote file afiedt.buf

  1  BEGIN
  2     DBMS_OUTPUT.put_line (qq (142));
  3* END;
SQL> /
3100

PL/SQL procedure successfully completed.

why the exception line :-
NO such ID for any Employee in this company

come with the error below :-

BEGIN
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "HR.QQ", line 10
ORA-06512: at line 2


I want the exception Line - that I wrote - only appear without oracle errors ........ how can I do that ?
Re: the EXCEPTION didn't work correctly [message #307086 is a reply to message #307084] Mon, 17 March 2008 16:18 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Function returned without value

Isn't it obvious?

DBMS_OUTPUT isn't a way to handle exceptions - RAISE them instead. Doing so, you'd avoid the error you got.
Re: the EXCEPTION didn't work correctly [message #307090 is a reply to message #307084] Mon, 17 March 2008 16:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member

SQL> create or replace function test_func
  2  return number
  3  is
  4  begin
  5     return 0;
  6  exception
  7  when others then
  8    null;
  9  end;
 10  /

Function created.

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE',
  2                                   'ERROR:06002';

Session altered.

SQL> create or replace function test_func
  2  return number
  3  is
  4  begin
  5     return 0;
  6  exception
  7  when others then
  8    null;
  9  end;
 10  /

SP2-0806: Function created with compilation warnings

SQL> show errors
Errors for FUNCTION TEST_FUNC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05005: function TEST_FUNC returns without value at line 9
SQL>


Regards

Raj
icon9.gif  WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307324 is a reply to message #307084] Tue, 18 March 2008 08:21 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I'm confused here Confused Rolling Eyes Question Exclamation

what suppose i do exactly to show only this sentence :-

NO such ID for any Employee in this company

rather than the Oracle Error :-

BEGIN
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "HR.QQ", line 10
ORA-06512: at line 2


I'm still waiting for the answer .......
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307326 is a reply to message #307324] Tue, 18 March 2008 08:22 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Create a front-end application that handles the exception, instead of relying on sqlplus
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307327 is a reply to message #307326] Tue, 18 March 2008 08:29 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
   RETURN NUMBER
IS
   sal   NUMBER;
BEGIN
   SELECT salary
     INTO sal
     FROM employees
    WHERE employee_id = v_id;

   RETURN sal;
EXCEPTION
   WHEN OTHERS
   THEN
      --DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
   raise_application_error(-20001,'NO such ID for any Employee in this company ');
END;
/


But the thing is it will show error with error code
ORA-20001: NO such ID for any Employee in this company

If you want only - NO such ID for any Employee in this company
then it will be handled at the front end only.
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307328 is a reply to message #307324] Tue, 18 March 2008 08:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> create or replace function test_func(parm1 varchar2)
  2    return number
  is
  3    4      test_num number;
  5    begin
  6       test_num := to_number(parm1);
  7       dbms_output.put_line('Input value is : ' || test_num);
  8       return 0;
  9    exception
 10    when others then
 11      dbms_output.put_line('I am masking the actual error .  So please forgive me');
 12      return null;
 13    end;
 14  /


SQL> select test_func('123') from dual;

TEST_FUNC('123')
----------------
               0

Input value is : 123
SQL>


SQL> select test_func('abc') from dual;

TEST_FUNC('ABC')
----------------


I am masking the actual error .  So please forgive me
SQL>


Quote:

ORA-06503: PL/SQL: Function returned without value

Tell me what you didn't understand from this statement ?

Regards

Raj

Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307329 is a reply to message #307328] Tue, 18 March 2008 08:37 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please check the following code.
I hope it will solve your error of
Quote:
ORA-06503: PL/SQL: Function returned without value

CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
   RETURN NUMBER
IS
   sal   NUMBER;
BEGIN
  BEGIN
   SELECT salary
     INTO sal
     FROM employees
    WHERE employee_id = v_id;
  EXCEPTION
   WHEN OTHERS
   THEN
      --DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
   raise_application_error(-20001,'NO such ID for any Employee in this company ');
   end;

   RETURN sal;

END;
/
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307337 is a reply to message #307329] Tue, 18 March 2008 09:05 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why did you reply to me ? I didn't ask the question .

Regards

Raj
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307346 is a reply to message #307329] Tue, 18 March 2008 09:33 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
ora_2007 wrote on Tue, 18 March 2008 14:37
Please check the following code.
I hope it will solve your error of
Quote:
ORA-06503: PL/SQL: Function returned without value

CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
   RETURN NUMBER
IS
   sal   NUMBER;
BEGIN
  BEGIN
   SELECT salary
     INTO sal
     FROM employees
    WHERE employee_id = v_id;
  EXCEPTION
   WHEN OTHERS
   THEN
      --DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
   raise_application_error(-20001,'NO such ID for any Employee in this company ');
   end;

   RETURN sal;

END;
/


This is just nonsense code.
Why the nested block? Why the WHEN OTHERS? What error will be returned when there is more than one employee with a given id?
Re: the EXCEPTION didn't work correctly [message #307364 is a reply to message #307084] Tue, 18 March 2008 10:46 Go to previous messageGo to next message
mnanias
Messages: 40
Registered: September 2007
Location: AMERICA
Member
I see that you want to execute the function normally even when there is an error. You could do this by changing the return type as VARCHAR2 and return the string in the exception section.
Re: the EXCEPTION didn't work correctly [message #307751 is a reply to message #307364] Wed, 19 March 2008 15:46 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

if you mean that :-

CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
RETURN VARCHAR2
IS
sal NUMBER;
BEGIN
SELECT salary
INTO sal
FROM employees
WHERE employee_id = v_id;

RETURN sal;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20012,
'NO such ID '
|| TO_CHAR (v_id)
|| ' for any Employee in this company '
);
END;
/


unfortunately ..... when I tested it , the result became like the following :-

SQL> select qq (1234) from dual;
select qq (1234) from dual
       *
ERROR at line 1:
ORA-20012: NO such ID 1234 for any Employee in this company
ORA-06512: at "HR.QQ", line 17


I'm still waiting for the answer ......... Rolling Eyes
Re: the EXCEPTION didn't work correctly [message #307754 is a reply to message #307751] Wed, 19 March 2008 16:03 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, now you learnt how to use bold tags; get used to use [code] tags as well.

Quote:
unfortunately ..... when I tested it , the result became like the following :

What kind message would you call a "fortunate" one? If that's (still) only one line, I'm afraid you're out of luck - Oracle will always display the whole error buffer.

What did you do about Frank's suggestion?
Frank
Create a front-end application that handles the exception, instead of relying on sqlplus
Re: the EXCEPTION didn't work correctly [message #307769 is a reply to message #307754] Wed, 19 March 2008 18:20 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

Littlefoot wrote on Wed, 19 March 2008 23:03

What did you do about Frank's suggestion?
Frank
Create a front-end application that handles the exception, instead of relying on sqlplus



yes .... about this point ( front-end application that handles the exception )
I don't understand what's that mean ????!!!!!!!
Re: the EXCEPTION didn't work correctly [message #307804 is a reply to message #307769] Thu, 20 March 2008 00:47 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Ok, here is what I mean:
Why are you so keen on only displaying a single line of errors? That can only be of importance if you want dead-end users to use your application.
If that is true, sqlplus/TOAD/PLSQL-Developer and such are NOT the tools to use; these are development tools.
To enable a user, that is scared off by additional error-lines, to use your package, you need to write a wrapper-application. Use Forms, Java or whatever.
User pushes a button, your code is executed, exception is raised (your exception-handler can be removed) and caught by the nice GUI. Next, the GUI strips the extra lines and only displays the error you want to display.
This is called application development. It is, well, what we developers do..
icon6.gif  Re: the EXCEPTION didn't work correctly [message #307824 is a reply to message #307804] Thu, 20 March 2008 01:50 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

OKay ...... that's mean no way to do what I want but only in applications developers like Forms or JDeveloper ... ect.
but in database tools like TOAD or Sql*plus it's impossible Sad

OKay . Neutral

[Updated on: Thu, 20 March 2008 01:52]

Report message to a moderator

Re: the EXCEPTION didn't work correctly [message #307847 is a reply to message #307824] Thu, 20 March 2008 02:53 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You did not understand the point of my post.
I did not say it is impossible in sqlplus or TOAD or whatever, I said these are the wrong tools for end-users.
icon2.gif  please focus in my question [message #307882 is a reply to message #307847] Thu, 20 March 2008 04:19 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I don't care by end user right now Mad .... all what I need just now is appearing only my line :-
NO such ID for any Employee in this company
in any database tools ( sqlplus , isqlplus , toad ... ect.) when employee ID that I entered in QQ function's parameter not identical with any number of employee_id column in employees table.

and as I said before ... I don't want the Oracle error to appear ...... that error :-
BEGIN
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "HR.QQ", line 10
ORA-06512: at line 2


if you have the code that I need , so write it and I will be soooooooooooooooooooo grateful for you .... if not so please DON'T ANSWER.
Re: please focus in my question [message #307887 is a reply to message #307882] Thu, 20 March 2008 04:31 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
If it is not for end-users, why bother?
Re: please focus in my question [message #307896 is a reply to message #307882] Thu, 20 March 2008 05:02 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Hany Freedom wrote on Thu, 20 March 2008 10:19
if you have the code that I need , so write it and I will be soooooooooooooooooooo grateful for you .... if not so please DON'T ANSWER.
Oh, I will answer although there is no code for you. Frank is right: you need a FRONT END application to handle the errors.

Now, I want you to read the the forum guidelines. It's not the first time you display this infantile impatience and frustration.

MHE

PS: changing fonts will not help either.

Edit: Tom Kyte might explain it a little bit further here.

[Updated on: Thu, 20 March 2008 05:11]

Report message to a moderator

Re: please focus in my question [message #307952 is a reply to message #307882] Thu, 20 March 2008 09:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
If you just add "return null" to your exception section as S.Rajaram previously suggested and demonstrated using a different error message, then it does what you are asking for:

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
  2  	RETURN NUMBER
  3  IS
  4  	sal   NUMBER;
  5  BEGIN
  6  	SELECT salary
  7  	  INTO sal
  8  	  FROM employees
  9  	 WHERE employee_id = v_id;
 10  
 11  	RETURN sal;
 12  EXCEPTION
 13  	WHEN OTHERS
 14  	THEN
 15  	   DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
 16  	   RETURN NULL;
 17  END;
 18  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2  	DBMS_OUTPUT.put_line (qq (942));
  3  END;
  4  /
NO such ID for any Employee in this company

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>


However, what the others are trying to get you to understand is that what you are asking for is not the best method in most real-life business situations.

Re: please focus in my question [message #307955 is a reply to message #307896] Thu, 20 March 2008 09:35 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
The actual exception that occurs when there is no matching employee_id is no_data_found, so it would be better to at least trap that exception first, then return sqlerrm only if that is not the problem and there is some other exception instead. That way if there is something else wrong besides no matching employee_id the one-line error message will give you some clue as to what the problem is.


CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
   RETURN NUMBER
IS
   sal   NUMBER;
BEGIN
   SELECT salary
     INTO sal
     FROM employees
    WHERE employee_id = v_id;

   RETURN sal;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
      RETURN NULL;
   WHEN OTHERS 
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      RETURN NULL;
END;
/

Previous Topic: PL/SQL collection with bulk collect
Next Topic: Open Cursor Problem
Goto Forum:
  


Current Time: Wed Feb 12 21:51:48 CST 2025