Home » SQL & PL/SQL » SQL & PL/SQL » Help !! Insert into a table using cursor/
Help !! Insert into a table using cursor/ [message #206943] Sat, 02 December 2006 10:29 Go to next message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

hi all.
i m using oracle 9i + form 6i.
i want to insert the values into a table using the following code.

i want to make the table(BALLOT_RESULT)"empty" each and every time when i call this procedure .

can some one help me to do so.

how can i make table(BALLOT_RESULT) empty after each call and before the next call of this procedure.


PROCEDURE BALLOT_RESULT IS

  CURSOR C IS
  SELECT 
  VOLUME_SR,YEAR,APP_NO,APP_NAME,F_NAME,POSTAL_ADDRESS,NIC,
  TELE_NO,GENDER,TNO 
  FROM V_BALLOT;

  R C%ROWTYPE;

BEGIN

  OPEN C;

  LOOP

    FETCH C INTO R;

    EXIT WHEN C%ROWCOUNT > 10 ;

    INSERT INTO BALLOT_RESULT VALUES
    (R.VOLUME_SR, R.YEAR, R.APP_NO, R.APP_NAME, R.F_NAME,
     R.POSTAL_ADDRESS, R.NIC,R.TELE_NO, R.GENDER,R.TNO);

  END LOOP;

  CLOSE C;

END;


REGARDS.

[Mod-edit: formatted code]

[Updated on: Sat, 02 December 2006 11:36] by Moderator

Report message to a moderator

Re: Help !! Insert into a table using cursor/ [message #206953 is a reply to message #206943] Sat, 02 December 2006 11:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What's the use of emptying the table AFTER this call?
That would mean the inserts are done for nothing?
You could add a delete as a first line of your procedure.
You might have a look at global temporary tables, but those will only help if you either commit between calls or only visit this procedure once per session.

I formatted your code; please take a look here to see how to make code-parts better readable
Re: Help !! Insert into a table using cursor/ [message #206958 is a reply to message #206953] Sat, 02 December 2006 12:44 Go to previous message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

actually i m using this procedure as a program unit in developer,so i attach this with a button,
and then also write a code under the procedure name in the trigger of button.
open_form('c:\ballot_result.fmx',active);

so when ever i press the button first the procedure execute and then i got a new form (ballot_result), here i got the values inserted by cursor.but at back end in sql*plus i got nothing in table(ballot_result).so i put a button to save this result.(commit).
Now i want to remove /vanish all the values in the table which are stored using cursor (in ballot_result)through the procedure and insert new values each time when ever i press the first button in the new form/(to execute procedure again).

this is the problem/
Previous Topic: computing and inserting values into table??
Next Topic: Create a trigger that is triggered by an UPDATE
Goto Forum:
  


Current Time: Mon Dec 05 19:00:09 CST 2016

Total time taken to generate the page: 0.08779 seconds