Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06503 (Oracle 10.2.0.1)
ORA-06503 [message #446341] Sun, 07 March 2010 22:54 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I have a very basic error with my function. When i passed wrong value which is not available in one of table i want to raise error like 'please check! value is not importable' message. But my function throws an error of function returned without a value. Why i'm getting this error?

My function is..
 1  CREATE OR REPLACE FUNCTION DDI.F_Metadata_Import_NEW1 (P_Type VARCHAR2,
 2                                              P_Name VARCHAR2,
 3                                              P_Owner VARCHAR2 DEFAULT 'SYS'
 4                                             , P_Target_Owner VARCHAR2 DEFAULT 'SYS')
 5     RETURN number
 6     AUTHID CURRENT_USER
 7  AS
 8     V_Type VARCHAR2(25) := UPPER(P_Type);
 9     V_Name VARCHAR2(25) := UPPER(P_Name);
10     V_Owner VARCHAR2(25) := UPPER(P_Owner);
11     V_Target_Owner VARCHAR2(25) := UPPER(P_Target_Owner);
12     V_Objid     NUMBER;
13     V_Scmid NUMBER;
14     V_Objtpid NUMBER;
15     v_number NUMBER := 0;
16     SQL_ERROR     NUMBER := SQLCODE;
17     SQL_ERRMESS   VARCHAR2 (250) := SUBSTR (SQLERRM, 1, 200);
18  BEGIN
19      DBMS_OUTPUT.PUT_LINE('Before execution ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
20     -- Get metadata from source database.
21     SELECT   Scm_Id INTO   V_Scmid FROM   DDI.Ddi_Scm_TRGT_T
22     WHERE   Scm = V_Owner AND SCM_IMP = 'Y';
23     DBMS_OUTPUT.PUT_LINE('Schema is importable');
24       BEGIN
25          DBMS_OUTPUT.PUT_LINE('check the obj type');
26          SELECT   Obj_Tp_Id INTO   V_Objtpid  FROM              DDI.Ddi_Obj_Tp_T
27          WHERE   Obj_Tp_Nm = V_Type AND SCM_IMP = 'Y';
28          DBMS_OUTPUT.PUT_LINE('Pointer in execution block');
29          return V_number;
30      EXCEPTION
31        WHEN NO_DATA_FOUND   THEN
32        DBMS_OUTPUT.PUT_LINE('Pointer comes into BEGINING OF no data found block');
33        RAISE_APPLICATION_ERROR (-20010,'Please check! ' || V_Type || ' is not an Importable Object t
34        DBMS_OUTPUT.PUT_LINE('Pointer comes to end of no data block');
35        WHEN OTHERS
36        THEN
37        DBMS_OUTPUT.PUT_LINE (SQL_ERROR || 'Message : ' || sql_errmess);
38      END;
39  EXCEPTION
40     WHEN NO_DATA_FOUND   THEN
41        RAISE_APPLICATION_ERROR (-20001,'Please check! ' || V_Owner || ' is not an Importable schema'
42     WHEN OTHERS
43     THEN
44        DBMS_OUTPUT.PUT_LINE (SQL_ERROR || 'Message : ' || sql_errmess);
45* END;

error is..
Quote:

SELECT F_Metadata_Import_NEW1 ('ABC','EMP','SCOTT') FROM DUAL;
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "DDI.F_METADATA_IMPORT_NEW1", line 45
ORA-06512: at line 1

Pointer comes to exception block but didn't raise the application error.
Quote:

Before execution 08-MAR-2010 19:20:09
Schema is importable
check the obj type
Pointer comes into BEGINING OF no data found block
0Message : ORA-0000: normal, successful completion

How to get a message without ORA-06503?
Regards,
Madhavi.
Re: ORA-06503 [message #446351 is a reply to message #446341] Sun, 07 March 2010 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
It appears your logic is flawed.
Build a small, simple test case to learn how code works the way you intend it to work.
Re: ORA-06503 [message #446352 is a reply to message #446341] Sun, 07 March 2010 23:32 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Sorry for distrubance. I got solution for above thing. I just remove 'WHEN OTHERS' exception from blocks, it works fine (get a required message when pass wrong data). But i dont know why it happens like that.
Regards,
Madhavi.
Re: ORA-06503 [message #446353 is a reply to message #446341] Sun, 07 March 2010 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have any exception the code of the function has no RETURN statement; add it.

By the way, REMOVE those WHEN OTHERS, they are a bug, if the user does not enable dbms_output, it will think the function does its job when it does not; it it enables it, it can't see from where the error come from.

Regards
Michel
Re: ORA-06503 [message #446359 is a reply to message #446341] Sun, 07 March 2010 23:49 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
Thanks for reply. But i didn't get you. Could you please send me any link regarding this?
Once again thank you for reply.
Regards,
Madhavi.
Re: ORA-06503 [message #446361 is a reply to message #446359] Sun, 07 March 2010 23:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which part?

Regards
Michel
Re: ORA-06503 [message #446365 is a reply to message #446341] Mon, 08 March 2010 00:02 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
How 'remove WHEN OTHERS Clause' solves above problem?

Regards,
Madhavi.
Re: ORA-06503 [message #446373 is a reply to message #446365] Mon, 08 March 2010 00:36 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It solves the problem you remove the parts that do not return any value.
A function MUST return a value or end with an exception.
This is/was not the case of your WHEN OTHERS clause.

Regards
Michel
Previous Topic: need to show last 4 digits
Next Topic: Query to fill data from top records
Goto Forum:
  


Current Time: Tue Dec 06 14:35:27 CST 2016

Total time taken to generate the page: 0.05617 seconds