Home » SQL & PL/SQL » SQL & PL/SQL » Cursor update (Oracle 10g)
Cursor update [message #355622] Sun, 26 October 2008 10:33 Go to next message
shanks009
Messages: 2
Registered: October 2008
Junior Member
I need a urgent help on the pl/sql written below



declare

rate integer;

cursor c_f is

select treccy,treamt from tredtl where treccy not in ('YEN,'EUR');

begin

select fxrate into rate from fxrate where fxfrccy='EUR'

for b in c_f

loop

select fxrate into rate2 from fxrate where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;

if b.treccy<>'YEN' and b.treccy<>'EUR' then

update tredtl

set eur=b.treamt*rate/rate2

where treccy=b.treccy

end loop;

end;



Please anyone let me know where i am going wrong

here my requirement is that i need to update a column in tredtl table.

first i am fetching all the data set of tredtl into cursor where treccy is not equal to eur and yen.

In rate1 variable i am taking a value from fxrate table which is having fxfrccy=EUR

Inside for loop i am fetching in the select statement fxrate into rate2 one by one where fxfrccy =b.treccy.

say if am fetching from the cursor treccy=USD and rate1=154.95

and rate2=98 where fxfrccy=b.treccy=USD

Now update eur=b.treamt*rate/rate2. Rite now its taking cursor is taking the last value only and updating the whole.The iteration is not working.

with regards
Re: Cursor update [message #355626 is a reply to message #355622] Sun, 26 October 2008 11:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

I need a urgent help on the pl/sql written below


Since this request this deemed as urgent I assume this would have sorted by this time.

Good luck

Regards

Raj
Re: Cursor update [message #355629 is a reply to message #355626] Sun, 26 October 2008 11:55 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If cursor selects everything but YEN and EUR, what is the purpose of the IF-THEN-ELSE construct? "b.treccy" will never be YEN nor EUR.

Quote:
its taking cursor is taking the last value only and updating the whole
Hm? The last value only? What is "the last value"? What is "the whole"?

Besides, if it is not a problem, would you, please, read the OraFAQ Forum Guide before posting your next message? It might help you write a better post (easier to understand, using [code] tags to preserve code formatting).

P.S. Forgot to mention: the code you have posted is NOT the one you use. It is full of syntax errors (single quotes not closed, semicolons at the end of a statement, END IF is missing, ...). If you expect any help, well, you won't get it unless you provide accurate code.

[Updated on: Sun, 26 October 2008 12:01]

Report message to a moderator

Re: Cursor update [message #355631 is a reply to message #355629] Sun, 26 October 2008 12:23 Go to previous messageGo to next message
shanks009
Messages: 2
Registered: October 2008
Junior Member
Hi,

Sorry for that.
Actually i have written the code now only and not copied from the toad.Sorry again
The last value i mean is that i am taking about the cursor dataset.
Let say the last dataset is treccy='AUD' in cursor and TREAMT=12

select fxrate into rate from fxrate where fxfrccy='EUR'-->rate =154.95

select fxrate into rate2 from fxrate where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;-->rate2=123 where fxrate.fxfrccy=treccy of tredtl

Now update statement will update eur_equivalent_amount when TRECCY<>'YEN' and TRECCY<>'EUR' and treccy=b.treccy refering to the cursor dataset.
After it exists the loop.
I dont know where i am getting wrong coz data problem is not there.
can u suggest any other way around if possible?

waiting for reply

regards
shanks


declare

rate integer;

cursor c_f is

select treccy,treamt from tredtl where treccy not in ('YEN,'EUR');

begin

select fxrate into rate from fxrate where fxfrccy='EUR'

for b in c_f

loop

select fxrate into rate2 from fxrate where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;

if b.treccy<>'YEN' and b.treccy<>'EUR' then

update tredtl

set eur_equivalent_amount=b.treamt*rate2/rate

where treccy=b.treccy;

end if;

end loop;

end;



Please anyone let me know where i am going wrong

here my requirement is that i need to update a column in tredtl table.

first i am fetching all the data set of tredtl into cursor where treccy is not equal to eur and yen.

In rate1 variable i am taking a value from fxrate table which is having fxfrccy=EUR

Inside for loop i am fetching in the select statement fxrate into rate2 one by one where fxfrccy =b.treccy.

say if am fetching from the cursor treccy=USD and rate1=154.95

and rate2=98 where fxfrccy=b.treccy=USD

Now update eur=b.treamt*rate2/rate. Rite now its taking cursor is taking the last value only and updating the whole.The iteration is not working.

with regards
Re: Cursor update [message #355634 is a reply to message #355631] Sun, 26 October 2008 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Cursor update [message #355643 is a reply to message #355631] Sun, 26 October 2008 15:56 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is absurd.

I have asked you to post exact code you use, but you have posted an invalid PL/SQL block once again. Copying exactly the same "explanation" doesn't help at all.

So, if you refuse to provide relevant information, why would I bother answering your question?

Besides, it would not surprise me if there's the WHEN OTHERS exception handler in there.
Previous Topic: want to put range on output
Next Topic: Regarding DEGREE column in ALL_TABLES
Goto Forum:
  


Current Time: Sat Dec 03 16:26:11 CST 2016

Total time taken to generate the page: 0.23914 seconds