Home » SQL & PL/SQL » SQL & PL/SQL » cursor problem
cursor problem [message #329784] Thu, 26 June 2008 09:25 Go to next message
wam_ora
Messages: 1
Registered: June 2008
Junior Member
I have following three tables.
VBDA_HISTORY2
(
  BLOCKID  NUMBER(9)                            NOT NULL,
  REFID    NUMBER(9)                            NOT NULL,
  V8       FLOAT(10)                            
)


VBDA_HISTORYREF_RMS_FM
(
  BLOCKID  NUMBER(9)                            NOT NULL,
  REFID    NUMBER(9)                            NOT NULL,
  REFTIME  DATE
)

TABLE VBDA_HISTORY2_RMS_FM
(
  REFID  NUMBER(9)                              NOT NULL,
  V8     FLOAT(10)
)



I shall enter data into VBDA_HISTORY2 by another program.
While entering data into VBDA_HISTORY2, data will also insert automatically into VBDA_HISTORY2_RMS_FM with checking VBDA_HISTORYREF_RMS_FM

For that I have created one trigger which is not working by
Giving error message code Ora-06511, ora06512, ora-04088

Trigger is as below:
CREATE OR REPLACE TRIGGER t_vbda_history2_rms_fm_ins
  after insert on vbda_history2
  for each row
begin
   declare
      cursor c2 is
          select * from vbda_historyref_rms_fm;
          ref_rec  vbda_historyref_rms_fm%ROWTYPE;
   begin
       if c2%isopen then
	      close c2;
	   end if;
       open c2;
       fetch c2 into ref_rec;
       for ref_rec in c2
	   loop
	       if (ref_rec.refid = :new.refid and :new.v8> 0) then
 		      insert into VBDA_HISTORY2_RMS_FM (refid,v8) values (:new.refid,:new.V8);
		   end if;
		   fetch c2 into ref_rec;
        end loop;
	   close c2;
   end;
end;


[Mod-edit: Frank added code-tags to improve readability]

[Updated on: Thu, 26 June 2008 12:23] by Moderator

Report message to a moderator

Re: cursor problem [message #329785 is a reply to message #329784] Thu, 26 June 2008 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guideline as stated in URL above
Re: cursor problem [message #329787 is a reply to message #329784] Thu, 26 June 2008 09:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why new comers are unable to read guidelines BEFORE posting?

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.

Copy and paste your session.

Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Thu, 26 June 2008 09:36]

Report message to a moderator

Re: cursor problem [message #329968 is a reply to message #329787] Fri, 27 June 2008 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
ORA-6511 is 'Cursor Already Open'. Given that you've only got one cursor and you have a check on that, it's sifficult to see where this error could be raised.

Can you cut/paste the entire error stack, preferably from SQL*Plus, so we can see the exact error you're getting.
Re: cursor problem [message #330388 is a reply to message #329784] Mon, 30 June 2008 01:14 Go to previous messageGo to next message
averno9
Messages: 30
Registered: June 2008
Location: Spain
Member
In your code, you open and fetch the cursor, and then you do a for loop. The for loop statement opens the cursor and fetchs it itself, you are opening here the cursor twice then.

So, to solve the problem, you can delete the opening of the cursor or not using a for loop statement.

PS: For all who answered just nonsense about posting "right". Why didn't you answered? I'm a beginner and i know for loop opens the cursor, so what happens? If it's not well written, you don't read it, do you? If you reply to a post, answer to the question, and if you want to say it's bad written, then write it on the PS. If you are just going to say, read the faq, don't answer please.

[Updated on: Mon, 30 June 2008 01:15]

Report message to a moderator

Re: cursor problem [message #330393 is a reply to message #330388] Mon, 30 June 2008 01:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
averno9 wrote on Mon, 30 June 2008 08:14
PS: For all who answered just nonsense about posting "right". Why didn't you answered? I'm a beginner and i know for loop opens the cursor, so what happens? If it's not well written, you don't read it, do you? If you reply to a post, answer to the question, and if you want to say it's bad written, then write it on the PS. If you are just going to say, read the faq, don't answer please.

Didn't you just spend more than half your reply on telling people how to post?
You base your opinion on how to post on your personal judgement, whereas the people you are referring to base it on the forum rules.
Re: cursor problem [message #330400 is a reply to message #329784] Mon, 30 June 2008 01:35 Go to previous messageGo to next message
averno9
Messages: 30
Registered: June 2008
Location: Spain
Member
This forum was created to share knowledge. Not to read the forum rules. In other post, a person replies to a senior member:

Quote:
As an Senior Member who consistently ignores Posting Guidelines, you deserve to be ignored.


I agree to this reply, because the owner has more than 200 messages. But i don't agree to refuse to answer to someone who has less than 20 messages. Of course, it's a good thing to remember the rules, but the essence of the reply must be answering.

In other forums, that would be spam, and they would be banned for a couple of days.
Re: cursor problem [message #330409 is a reply to message #330400] Mon, 30 June 2008 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This forum was created to share knowledge. Not to read the forum rules.

Each forum, each place in the world, has its rules.
When you come into one, you have to follow this one rules not other ones.

Quote:
but the essence of the reply must be answering.

It is your opinion, you are allowed and welcome to post it but you have no right to say what others can or not post and the following:
Quote:
If you are just going to say, read the faq, don't answer please.

is not acceptable even if you were a senior member or a moderator.

Quote:
But i don't agree to refuse to answer to someone who has less than 20 messages.

Do as you want but don't try to impose this to others.

Regards
Michel
Re: cursor problem [message #330421 is a reply to message #329784] Mon, 30 June 2008 02:36 Go to previous messageGo to next message
averno9
Messages: 30
Registered: June 2008
Location: Spain
Member
I agree with your first quote. Every forum and every association has its rules and we must follow them. And if you don't like the rules, don't use use the forum, ok.

The second quote, ok, it's my opinion and i have no right to force anyone to think like that. I admit it.

The third quote intrigates me. Ok, if i were a person who has ten thousand messages, or a moderator, i have no rigth to tell people what they can say or not. But the 'even' word... It's like you can tell me shut up noob, but with very good manners, because i have less than 100 messages. I simply don't understand that, messages don't demonstrate knowledge.

And the fourth quote, if anyone thinks i was trying to impose my opinions, i'm sorry. My only explain is my lack at writing in english, i'm sure i didn't choose my words properly.

PS: I have been in my career, in a java forum, asp.net forum, basic forum, delphi forum and in ora faq. In each one of them, i found what i'm complaining about. People don't answer someone because he didn't put his code in a table, for example. I think it's dumb, and in every single forum i hint that, everyone who answered, said i was right. And by the way, you didn't quote the part i said this would be spam. Because it is.
Re: cursor problem [message #330432 is a reply to message #330421] Mon, 30 June 2008 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And by the way, you didn't quote the part i said this would be spam. Because it is.

Juts because, I don't think reminding the rules and pointing to it is spam and as it is a matter of opinion I didn't want to argue.

Quote:
if anyone thinks i was trying to impose my opinions, i'm sorry. My only explain is my lack at writing in english, i'm sure i didn't choose my words properly.

You said: "don't answer please."

Regards
Michel
Re: cursor problem [message #330436 is a reply to message #330432] Mon, 30 June 2008 03:15 Go to previous messageGo to next message
averno9
Messages: 30
Registered: June 2008
Location: Spain
Member
Yes, that's what i wrote. I think it would be best said, i don't know, please consider it before posting read the faq only, but you are free to post what you want. I didn't want to order or something like that. Again, i'm sorry for my english and i'm sorry for what i've written.
Re: cursor problem [message #330443 is a reply to message #330436] Mon, 30 June 2008 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please note that the answer was not "read the faq" but "read the guidelines".
I don't think I ever post here "please read the faq".

Regards
Michel
Re: cursor problem [message #330450 is a reply to message #329784] Mon, 30 June 2008 03:37 Go to previous message
averno9
Messages: 30
Registered: June 2008
Location: Spain
Member
Ok, i made another mistake. You wrote guidelines, not faq. I apologize for that too.

I hope the answer for the post is clear and can help the author.
Previous Topic: I am unable to create a nested table (merged)
Next Topic: Generic SELECT procedure.
Goto Forum:
  


Current Time: Sat Dec 03 01:19:24 CST 2016

Total time taken to generate the page: 0.13101 seconds