Electronic Balloting

I do not understand the requirements.

For example:  What is the 'A' loop of 50000 do?
What is :EM supposed to be?

To get two random values from the table try this:
SELECT id_card_number FROM (
SELECT id_card_number, row_number() over(order by dbms_random.value) myrn FROM ballot
)
where myrn <= 2;

Sir records will be display in text_field like 1,3,6,8,9 like speedy counter plz check in the picture when i click button one id_card_number, name should come in winner text_fieldas far as

FOR A IN 1..50000 LOOP-------counter speed controlling
                 :EM:=C;---------text field




thanks
hina mansoor
BALLOT.PNG

If you just want to choose a random winner and runner-up, the SQL I posted above should to it.  Not understanding the need for a counter.

Sir your code is error showing "Encountered sysmbol (order by dbms_random.value)"



Thanks

I'm assuming this error is coming from Forms.

If so, confirm the SQL syntax using sqlplus.

Going from memory Forms 6i doesn't understand the Data Warehouse Window functions.

If this is the case, just create a view in the database and select from the view in Forms.

create or replace view top_two_random_numbers 
as
SELECT id_card_number FROM (
SELECT id_card_number, row_number() over(order by dbms_random.value) myrn FROM ballot
)
where myrn <= 2; 


--then in Forms:
select * from top_two_random_numbers;

Select all Open in new window

Thanks sir my last question is i have created view and called on canvas now number should be run like stop watch i think i am not explaining proper i mean record will be in loop, counter or like stop watch when i click button 1 number should come out in another text_item



Thanks

Every time you click the button the view will return random entry's for the top two.

I don't see the need for the stopwatch functionality.

Sir i made above code which is running counter randomly in text_item but its not giving me number  in another text_item when i click button  just hanging forms. Sir if you have form built then you can test this code.

DECLARE
     C VARCHAR2(20);
     CURSOR C1 IS SELECT id_card_number FROM ballot ORDER BY dbms_random.value;
   BEGIN
         FOR A IN C1 LOOP
         c:=a.id_card_number;
         synchronize;
   FOR A IN 1..50000 LOOP
                 :EM:=C;
        END LOOP;
             end loop;

END;

Thanks

That looks like the same code in the original question.  I do not understand what that code is trying to do.

Based on what you have explained here is what I understand:
You have 5000 rows of data and want to select a random 'winner' and a random 'runner up'.

So basically the top two rows from a random order.  What is what the SQL I posted does.

If my assumption of the requirements is wrong, please explain the requirements.

Sir my requirement is in text_item field records will display one by one automatically no need to press any button like this 1,3,4,5,6,7,8,9,9,0,90,0,0 will not stop untill  unless i would not press any winner button. will reset the for loop counter. like TIME SECONDS in running



Thanks

Are you looking for something like a slot machine that will continue to 'scroll' numbers in a field until you click the 'Stop' button then take the number you stopped on and display the associated name?

Exactly sir :-)

Sorry but I'm not a Forms Expert.

I don't know how to set up an infinite loop then exit out of it when a button is clicked.

I'll see if I can locate some Forms Experts.

Thanks Sir for helped me a lot

Selecting 5,000 records from a database table into a form when you really only want two records (a winner and a runner-up) looks like a very in-efficient way to get this job done.

I would try to write a PL\SQL procedure that does this work in the database and returns two rows.  Then you can base your forms block on the procedure.  This approach will save a lot of network traffice and database server I/O.

>>write a PL\SQL procedure that does this work in the database and returns two rows

I think the view above should replace the need for a procedure.

Yes, a database view would be an equally-good way to solve this problem (and you had proposed that first).

Don't loop directly true the numbers.  oracle forms first finishes the loop and after that reacts to a button pressed.

create a database package
init procedure : Gather all values in a pl/sql-collection
function show_next return number : return the next value of the collection, keep track of the current collection index
function choose return number
   get the next value of the collection,  remove the record from the collection


create a repeating timer :
on the firing of the when-timer_expires
execute the show_next function and fill the changing field

on the button-pressed
execute the choose function of the collection an fill both fields

Sir i know i should not ask question again you already briefed in detail i have created repeat timer, loop but i could not reached to my target . and planned to delete this question too.


Thanks
Hina mansoor

There is no need to delete this question and ask it again.

The call for help I sent out would notify the same people a new question would notify.

There just aren't a lot of Forms users around.  Especially Forms 6i.

I am an experienced Forms6i user and developer.  We've used it for 13 years, and I worked with the very-similar Forms5 for a couple years before that.  We do have an application yet that still uses Forms6i.  I find Forms6i to be a very powerful and flexible data entry and query tool.  I would say those are the tasks it does best (high speed, high volume data entry and query support).

You may be able to use Forms6i to present an application that you describe as "electronic balloting"  but that doesn't mean that you should do all of the programming for it in Forms6i.  Most of the SQL and/or PL\SQL tasks for this application (and for most Oracle Forms applications) should be done via database stored procedures or views.

Try writing views and/or stored procedures to do most of the work that you need, then ask specific questions here if you have trouble with those procedures or views.

delete the timer before you exit the form (timers are on application level and will continue to fire while a form session exists)

Thank you very much Sir you are really gr8, owsome,  Sir everything is working fine except with my table. Kindly explain small thing which i have attached this code"package body "  with my table "BALLOT"  but form is hanging. like in picture except table its working 100% fine and  sir whatever id_Card_number we have selected like as winner number should be delete from table automatically.

i changed only table name instead of "dual" i put my table name "BALLOT"



create or replace package body balloting
is

pck_index pls_integer := 0;

cursor c_random
is
select level id_card_number
from ballot
connect by level < 11
order by dbms_random.value;

type t_bal_type  is table of c_random%rowtype index by pls_integer;

t_bal t_bal_type;

procedure init
is
begin
 open c_random;
 fetch c_random bulk collect into t_bal;
 close c_random;
end;

function show_next return number
is
  n_ballot pls_integer;
begin
  if t_bal.count = 0 then
    raise no_data_found;
  else  
    pck_index := t_bal.next(pck_index);
    if pck_index is null then
       pck_index := t_bal.first;
    end if;  
    n_ballot :=  t_bal(pck_index).id_card_number;
  end if;
  return n_ballot;  
end;

function choose return number
is
  n_ballot pls_integer;
begin
  n_ballot := show_next;
  t_bal.delete(pck_index);
  return n_ballot;
end;

end;
/
Sir anything else i have to change in code.

Thanks again
Hina mansoor
hanging.PNG

This message indicates that the package *DID NOT* compile successfully:

"Warning: Package Body created with compilation errors."

Run this statement:
alter package balloting compile body;

Then run:
show error

That should give you the exact problem something like this example:

LINE/COL ERROR
-------- --------------------------------------------------------
313/5    PL/SQL: SQL Statement ignored
323/16   PL/SQL: ORA-00904: "D"."DIST_REF_MF_DIST_FLAG": invalid
         identifier

Post the output of "show error" here if you need help correcting this problem in your system.

remove the
connect by level < 11
in combination with dual  it gives the possibility  to select more rows (10 in this case) using the dual table that is available everywhere

so your cursor should be

cursor c_random
is
SELECT id_card_number
FROM ballot
ORDER BY dbms_random.value;

And beware: if you change your package while in your form you will get an error.
Reconnect to use a changed package.

The winner is deleted from pl/sql-collection  t_bal in the function choose
by the sentence
tab1.delete(pck_index);

If you want it deleted from your table too create a procedure

procedure delete_id_card(p_id_card_number)
is
  pragma autonomous_transaction;  --  to get immediate removal without
     -- using transaction processing of oracle forms
begin
  delete from ballot where id_card_number = p_id_card_number; --
         -- if there is another primary key you should change the procedure
         -- AND select it in the cursor too
  commit;
end;

in your package body (it should be before the function choose)


and execute it after the
tab1.delete(pck_index);
delete_id_card(n_ballot);

Sir i have changed according to your code Package body created.successfully

i put this code in when-new-form-instance and also tried when-button-pressed

DECLARE
ballot TIMER;
OneSec CONSTANT NUMBER := 1000;
BEGIN
ballot := CREATE_TIMER('ballot',OneSec,REPEAT);
END;

balloting.init;
 

its giving me error like in picture

Sir without table its working fine but wih table its giving error

----------------------------------DELETE PROCEDURE-------------------------

SQL> CREATE OR REPLACE procedure delete_id_card(p_id_card_number)
  2        is
  3    pragma autonomous_transaction;  --  to get immediate removal without
  4      
  5  begin
  6    delete from ballot where id_card_number = p_id_card_number; --
  8   COMMIT;    
   9  end;
/

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE DELETE_ID_CARD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/42     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         in out <an identifier> <a double-quoted delimited-identifier>
         ... LONG_ double ref char time timestamp interval date binary
         national character nchar
         The symbol "<an identifier>" was substituted for ")" to continue.

Thanks
Hina mansoor
error1.PNG

procedure delete_id_card(p_id_card_number)
should be
procedure delete_id_card(p_id_card_number ballot.id_card_number%type)

I would not make it a procedure of it's own.  Add it the package body after the init procedure.

And there is no need of running the form if your package or procedures have compilation errors : solve them first.  
And : try it by yourself , i'm here to help , not to do your work.

Thanks Sir everything is working fine with table also only my question is to you same procedure will work also for "VARCHAR2" DATA TYPE and 2nd delete procedure is also created successfully and next step  which is i am trying


ThankQ very much once again

Hina mansoor

You can do the something alike for varchar2, but be sure to change it in all necessary places.  
If the type's in the package specification are changed you will have to recompile your form (and maybe change the recieving datatypes there too)

ThankQ very much sir trying to apply delete procedure and will get back to you shortly if faced any problem. Sir you really deserved for 2000 points but i have only option to give you 500 points maximum you are really kind.


Thanks
Hina mansoor

sir i put delete here is it correct

function choose return number
is
  n_ballot pls_integer;
begin
  n_ballot := show_next;
  t_bal.delete(pck_index);
  --tab1.delete(pck_index);------------what is the function of this
  delete_id_card(n_ballot); ---------------is it correct
  return n_ballot;
end;


Sir this is not deleting record from table

thanks