Home » SQL & PL/SQL » SQL & PL/SQL » Total newbie to PL/SQL should be an easy one for you gurus.....
icon9.gif  Total newbie to PL/SQL should be an easy one for you gurus..... [message #204090] Fri, 17 November 2006 13:57 Go to next message
truegilly
Messages: 3
Registered: November 2006
Location: leicester UK
Junior Member
Hi there

Got a bit of a problem that im sure lots will know instantly where im going wrong.

Ive made a simple function that works out this mathematical expression.

h = √ ( aČ + bČ )

This is my function which works fine…

CREATE OR REPLACE FUNCTION myFunc (a NUMBER, b NUMBER)
RETURN NUMBER IS return_value NUMBER(3,2);
BEGIN
IF a IS NOT NULL THEN
return_value:= SQRT(POWER(b,2) + POWER(a,2));
END IF;
RETURN return_value;
END;

What im trying to do is set variable a to 23.67 and variable b to 79.94. as below

DECLARE
a NUMBER(2,2);
b NUMBER(2,2);
BEGIN
a := 23.67;
b := 79.94;
DBMS_OUTPUT.PUT_LINE('the length of H is ' || myFunc(a,b));
END;

But when I execute the code above I get this error…

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "MMC03JG.MYFUNC", line 5
ORA-06512: at line 7

It works fine with integer numbers but doesn’t like decimal values however much I adjust the NUMBER() values it wont work.

Also do any of you know how I can input values into a or b during runtime ?? I have got this working before but carnt remember how this is done. I was trying things like
a := ‘’; and b :=; but it don’t like it.

Any help would be much appreciated.

Jonathan
Re: Total newbie to PL/SQL should be an easy one for you gurus..... [message #204106 is a reply to message #204090] Fri, 17 November 2006 19:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
NUMBER(2,2) means "2 significant digits, with 2 behind the decimal point. ie. It can store numbers ranging from -0.99 to 0.99.

I think you want NUMBER(4,2), and NUMBER(5,2) for return_value.

Ross Leishman
Re: Total newbie to PL/SQL should be an easy one for you gurus..... [message #204187 is a reply to message #204090] Sat, 18 November 2006 12:31 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi,

to pass the values dynamically you have to create stored procedure and pass parameter through the sp.

Bye
Ashu
Re: Total newbie to PL/SQL should be an easy one for you gurus..... [message #204189 is a reply to message #204187] Sat, 18 November 2006 12:49 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
u can try this ...

CREATE OR REPLACE PROCEDURE myproc (a in NUMBER, b in NUMBER, c out number)
as
BEGIN
IF a IS NOT NULL THEN
c := SQRT(POWER(b,2) + POWER(a,2));
END IF;
DBMS_OUTPUT.PUT_LINE('the length of H is ' || c);
END;
/


SQL> variable g_out number
SQL> exec myproc(23.67,79.94,:g_out);

Bye
Ashu
Re: Total newbie to PL/SQL should be an easy one for you gurus..... [message #204306 is a reply to message #204189] Mon, 20 November 2006 01:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Ab_trivedi - In what way has your post helped the OP? You haven't solved his problem - youve just provided him with a piece of code that calculates the function he wants, but doesn't give him the answer in a way that his code can use.
Re: Total newbie to PL/SQL should be an easy one for you gurus..... [message #204386 is a reply to message #204306] Mon, 20 November 2006 07:22 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi JRowbottom,


OP has asked about :How he dynamically passed the input? If I wrong then I will try to suggest some other way.

Bye
Ashu

Re: Total newbie to PL/SQL should be an easy one for you gurus..... [message #204388 is a reply to message #204386] Mon, 20 November 2006 07:25 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Surely it would have been easier to suggest
SELECT myfunc(23.67,79.94) from dual;
rather than to get him to rewrite his function as a procedure?
Previous Topic: select access to table from different users
Next Topic: Subtotal & Grand Total at the last in output.
Goto Forum:
  


Current Time: Fri Dec 02 12:05:11 CST 2016

Total time taken to generate the page: 0.08420 seconds