Home » SQL & PL/SQL » SQL & PL/SQL » problem with Function
problem with Function [message #280199] Mon, 12 November 2007 15:26 Go to next message
yrkrish9
Messages: 1
Registered: November 2007
Junior Member
Hi guys,

I have to create a function which should convert the given currency into the various currencies.

I have a source table AMSSYS.LK_CURRENCY with columns COUNTYRID , CURRENCYID ,CURRENCY NAME,EXCHANGERATE , EXCHANGE FLAG.

FOR EXAMPLE : The exchange_rate is 2.0143, and the exchange_flag is 'M', that means if you have 1 unit of 'GBP', the corresponding USD will be 1*2.0143 (M means mutiply); another case, lk_currency_id 'HKD', the exchange_rate is 7.7995, and the exchange_flag is 'D', that means if you have 1 unit of 'HKD', the corresponding USD will be 1/7.7995 (D means divide).

Verify the following function n let me know the changes .

CREATE OR REPLACE FUNCTION Currency_Converter (
p_amount NUMBER,
p_country_id amssys.lk_currency.lk_country_id%TYPE,
p_src_currency_id amssys.lk_currency.lk_currency_id%TYPE,
p_dst_currency_id amssys.lk_currency.lk_currency_id%TYPE
)
RETURN NUMBER
AS
lp_sourceid amssys.lk_currency.lk_country_id%TYPE;
lp_destid amssys.lk_currency.lk_country_id%TYPE;
lp_country_id amssys.lk_currency.lk_country_id%TYPE;
l_sou_ex_rate amssys.lk_currency.exchange_rate%TYPE;
l_des_ex_rate amssys.lk_currency.exchange_rate%TYPE;
l_sou_ex_flg amssys.lk_currency.exchange_flag%TYPE;
l_des_ex_flg amssys.lk_currency.exchange_flag%TYPE;
l_converted_amount NUMBER (10, 4);
country_not_found EXCEPTION;
NO_DATA_FOUND EXCEPTION;

BEGIN
lp_country_id := p_country_id ;
lp_sourceid := p_src_currency_id;
lp_destid := p_dst_currency_id ;
BEGIN
SELECT exchange_rate, exchange_flag
INTO l_sou_ex_rate, l_sou_ex_flg
FROM amssys.lk_currency
WHERE lk_country_id = lp_country_id AND lp_sourceid= lk_currency_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Country is not found for : ' || p_country_id);
RAISE country_not_found;

SELECT exchange_rate, exchange_flag
INTO l_des_ex_rate, l_des_ex_flg
FROM amssys.lk_currency
WHERE lk_country_id = lp_country_id AND lp_destid= lk_currency_id;
IF (UPPER (l_sou_ex_flg ) = 'M')
AND (UPPER(l_des_ex_flg) ='M')
THEN
l_converted_amount := (p_amount * l_sou_ex_rate)/ (l_des_ex_rate);



ELSE IF (UPPER (l_sou_ex_flg ) = 'M') AND (UPPER(l_des_ex_flg) ='D')
THEN
l_converted_amount := (p_amount * l_sou_ex_rate)* (l_des_ex_rate);
END IF;
END IF;


BEGIN
SELECT exchange_rate, exchange_flag
INTO l_des_ex_rate, l_des_ex_flg
FROM amssys.lk_currency
WHERE lk_country_id = lp_country_id AND lp_destid= lk_currency_id;
IF (UPPER(l_des_ex_flg) ='M') AND (UPPER (l_sou_ex_flg) = 'D')
THEN
l_converted_amount := (p_amount / l_sou_ex_rate)/ (l_des_ex_rate);
ELSE IF (UPPER(l_des_ex_flg) ='D') AND (UPPER (l_sou_ex_flg) = 'D')

THEN
l_converted_amount := (p_amount / l_sou_ex_rate)*(l_des_ex_rate);
END IF;
END IF;
EXCEPTION
WHEN ZERO_DIVIDE
THEN
DBMS_OUTPUT.put_line (' Exchange Rate is 0 for ' || p_country_id);
RAISE;

END ;
END;
RETURN (l_converted_amount);

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error in conversion_USD function: ' || SQLERRM);
RAISE ;

END Currency_Converter;
/



Thanks,
Raj.
Re: problem with Function [message #280202 is a reply to message #280199] Mon, 12 November 2007 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as specified above.
Re: problem with Function [message #280206 is a reply to message #280199] Mon, 12 November 2007 15:52 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:

Verify the following function n let me know the changes

Yep, It's a function. I'll let n (whoever that is) notify you of what changes he/she made
Re: problem with Function [message #280445 is a reply to message #280199] Tue, 13 November 2007 11:49 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Do you really think that this forum is your personal QA department ?
Re: problem with Function [message #280449 is a reply to message #280445] Tue, 13 November 2007 12:19 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Above all when you did even not make the effort to read and follow OraFAQ Forum Guide.

Regards
Michel

[Updated on: Tue, 13 November 2007 12:21]

Report message to a moderator

Previous Topic: Query help
Next Topic: Index question
Goto Forum:
  


Current Time: Fri Dec 09 02:03:09 CST 2016

Total time taken to generate the page: 0.05495 seconds