Home » SQL & PL/SQL » SQL & PL/SQL » The follwing function is giving an error (numeric or value error) in returning a value
icon6.gif  The follwing function is giving an error (numeric or value error) in returning a value [message #215091] Fri, 19 January 2007 04:56 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
No Message Body

[Updated on: Sat, 20 January 2007 04:24]

Report message to a moderator

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215095 is a reply to message #215091] Fri, 19 January 2007 05:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The error isn't happening in that function.
If it were, it would be caught by that horrible When Others block, which wouldn't raise the exception again, and you wouldn't get a 6502 error raised.
I reckon the error is in whatever function called Test_Genupd1.
Are you sure that you are putting the funtions return into a CLOB, and not a Varchar2?
function errored out as saying exp not an identifier [message #215097 is a reply to message #215095] Fri, 19 January 2007 05:16 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
No Message Body

[Updated on: Sat, 20 January 2007 04:24]

Report message to a moderator

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215098 is a reply to message #215097] Fri, 19 January 2007 05:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Remove ALL your so called exception handlers. They do nothing except ruin your code and the ability to debug anything.
Remove them from both the function and the program unit calling the function.
As JRowbottom said, there must be an error in the calling program unit.
(the one that so totally useless does an output of both sqlcode and sqlerrm in its exception block).
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215102 is a reply to message #215098] Fri, 19 January 2007 05:45 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
I havent found any error in any of the calling program. I have executed each and every calling program individually with same parametrs. They executed with out any error.

I think there must be an error in sqlupd statement or else in return statements.

Thanks
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215118 is a reply to message #215098] Fri, 19 January 2007 06:46 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Frank wrote on Fri, 19 January 2007 05:22
Remove ALL your so called exception handlers. They do nothing except ruin your code and the ability to debug anything.
Remove them from both the function and the program unit calling the function.
As JRowbottom said, there must be an error in the calling program unit.
(the one that so totally useless does an output of both sqlcode and sqlerrm in its exception block).

I couldn't agree more. DBMS_OUTPUT is a debug tool for developers. How on Earth is this supposed to help anyone:

EXCEPTION
    WHEN others THEN
    DBMS_OUTPUT.PUT_LINE('ERROR:IN GETUPDATE FUNCTION ' || sqlerrm);

The default behaviour (if you removed the exception handler) would be to display the same information, plus the full error stack, along with the actual line number where the exception occurred. It would also usefully cascade the exception up the calling stack, so that a calling procedure would know it had failed and not just continue on regardless.

I can never understand why people feel the need to report SQLCODE when it is just the number from the start of SQLERRM.
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215120 is a reply to message #215118] Fri, 19 January 2007 06:55 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Can any one help me in regarding this. I tried a lot but not able to get succeded.

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215128 is a reply to message #215120] Fri, 19 January 2007 07:18 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Did you remove the exception handlers and re-test it?

You might also try running it through a debugger.

[Updated on: Fri, 19 January 2007 07:18]

Report message to a moderator

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215130 is a reply to message #215128] Fri, 19 January 2007 07:32 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
I tried eventhough it is producing the smw error.

Thanks for ur help
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215133 is a reply to message #215130] Fri, 19 January 2007 07:36 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
One problem earlier was that it was hard to see where the problem was occurring. With the exception handlers removed you will still hit the same exception, but the error stack it produces should now give you more information about what is happening.
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215135 is a reply to message #215133] Fri, 19 January 2007 07:40 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Its giving the same error. It havent given any detailed message.

I think its because of return datatype. Can anyone let me know where the problem is?

Thanks
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215137 is a reply to message #215135] Fri, 19 January 2007 07:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you or did you not remove ALL of your exception handlers?
If you did, copy and paste the outcome of your sqlplus session in which you call the function.
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215140 is a reply to message #215137] Fri, 19 January 2007 07:50 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
After removing all my exception handlers. Its giving the same error i,e
line1
line2
line3
line4
line5
line6
line7
line8
line9
line10
line11
line12
line13
-6502
ORA-06502: PL/SQL: numeric or value error
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215152 is a reply to message #215140] Fri, 19 January 2007 08:21 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
It could be useful to see a cut and paste of the complete call, so we can see you you are executing it etc.

Somewhere there is still an exception handler that goes something like:

WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE);

It would be helpful to get rid of that.

[Updated on: Fri, 19 January 2007 08:23]

Report message to a moderator

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215157 is a reply to message #215152] Fri, 19 January 2007 08:41 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
In the above function i have return null value instead of sqlupd.
Its working without any error.

Just let me know how to return clob's . I think there the problem lies in returning a clob value.

Thanks in advance
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215160 is a reply to message #215157] Fri, 19 January 2007 08:58 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
This is what JR suggested at the start:

JRowbottom wrote on Fri, 19 January 2007 05:07
I reckon the error is in whatever function called Test_Genupd1.
Are you sure that you are putting the function's return into a CLOB, and not a Varchar2?

You should be able to just assign the function's result to a CLOB variable. Is that what you are doing?
Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215236 is a reply to message #215160] Sat, 20 January 2007 01:11 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
No Message Body

[Updated on: Sat, 20 January 2007 04:25]

Report message to a moderator

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215244 is a reply to message #215236] Sat, 20 January 2007 02:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
vikram1780 wrote on Sat, 20 January 2007 08:11

declare
sql1 varchar2(32000);
handle utl_file.file_type;
begin
sql1:=test_genupd2(intable=>'MST_ITEM',nctable=>'NC_OUT_ITEM_ORCL', outtable=>'TGT_ITEM_ORCL');
handle:=utl_file.fopen('LOGAREA','1.txt', 'w',32767);
utl_file.put_line(handle,sql1 );
utl_file.fflush(handle);
utl_file.fclose(handle);
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;


Could you please tell us what the last 5 lines of your code are called?
Could you now reread the entire thread?

If you'd had followed our advice from the start, you would have noticed that the error is NOT in your function.
What is (according to the docs) the datatype of the second argument of UTL_FILE.PUT_LINE?

[Edit: upon rereading: sql1 is defines as varchar2, I did not see that. Your function returns a clob. These don't match. sql1 should be a clob and should be converted to a varchar2 before calling utl_file]

[Updated on: Sat, 20 January 2007 07:01]

Report message to a moderator

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215246 is a reply to message #215244] Sat, 20 January 2007 02:29 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
No Message Body

[Updated on: Sat, 20 January 2007 04:25]

Report message to a moderator

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215248 is a reply to message #215244] Sat, 20 January 2007 02:44 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
No Message Body

[Updated on: Sat, 20 January 2007 04:25]

Report message to a moderator

Re: The follwing function is giving an error (numeric or value error) in returning a value [message #215283 is a reply to message #215248] Sat, 20 January 2007 08:14 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@vikram1780, why did you delete some of your previous messages? It makes this topic difficult to follow and understand.
Previous Topic: Query Help
Next Topic: [psp] -> trigger
Goto Forum:
  


Current Time: Sat Dec 10 07:19:23 CST 2016

Total time taken to generate the page: 0.11488 seconds