Home » SQL & PL/SQL » SQL & PL/SQL » Data Type to return decimal values in function (Oracle 11g)
Data Type to return decimal values in function [message #578287] Tue, 26 February 2013 10:14 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Hi,

I have the following database function.

GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);

The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78

what data type I should use so that my output is returns all the decimal values?

Thanks
Re: Data Type to return decimal values in function [message #578288 is a reply to message #578287] Tue, 26 February 2013 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

how can we reproduce what you report?
Re: Data Type to return decimal values in function [message #578290 is a reply to message #578287] Tue, 26 February 2013 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59146
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78


The problem is either in your procedure either in the way you test it.
It is NOT in the parameter types.

Regards
Michel
Re: Data Type to return decimal values in function [message #578384 is a reply to message #578290] Wed, 27 February 2013 06:58 Go to previous messageGo to next message
bharathi89
Messages: 39
Registered: May 2012
Location: chennai
Member
HI, gentleman777us.

Take Below Coding As Example:

CREATE OR REPLACE FUNCTION cv_fn1 (ido IN NUMBER, osal OUT NUMBER)
RETURN NUMBER
AS
BEGIN
SELECT commision
INTO osal
FROM employee
WHERE empno = ido;

RETURN osal;
END;

Execute It:

DECLARE
retval NUMBER;
ido NUMBER;
osal NUMBER;
BEGIN
ido := 1;
osal := NULL;
retval := psdba.cv_fn1 (ido, osal);
DBMS_OUTPUT.put_line (osal);
END;

Working Right?.. Still you Have Problem Means..Try to Change Your Query...Try To Remove "TRUNC" If You Used...
Re: Data Type to return decimal values in function [message #578385 is a reply to message #578384] Wed, 27 February 2013 07:03 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
@cvbharathi89

Please read and follow How to use [code] tags and make your code easier to read?

What's the point of the out parameter? You're returning the value twice.
Re: Data Type to return decimal values in function [message #578414 is a reply to message #578385] Wed, 27 February 2013 15:03 Go to previous message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
run the following command. Does it return a whole number?

SELECT commision
FROM employee
WHERE empno = 1;


and your function should be

CREATE OR REPLACE FUNCTION cv_fn1 (ido IN NUMBER)
RETURN NUMBER
AS
osal number;
BEGIN
SELECT commision
INTO osal
FROM employee
WHERE empno = ido;

RETURN osal;
END;

and your test should be

DECLARE
retval NUMBER;
ido NUMBER;
BEGIN
ido := 1;
retval := psdba.cv_fn1 (ido);
DBMS_OUTPUT.put_line (retval);
END;

[Updated on: Wed, 27 February 2013 15:10]

Report message to a moderator

Previous Topic: Regular expression
Next Topic: ORA-00922: missing or invalid option
Goto Forum:
  


Current Time: Sat Sep 20 11:03:57 CDT 2014

Total time taken to generate the page: 0.07674 seconds