Solved

Electronic Balloting

Posted on 2014-01-05
33
290 Views
Last Modified: 2014-01-08
Hi all,

my question is to all gurus i want to make application which is electronic balloting means i have create one table

create table ballot(s_no number(5),
         id_Card_number varchar2(20),
         name varchar2(25));

  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;
       -- pause;
         synchronize;
   FOR A IN 1..50000 LOOP
                 :EM:=C;
        END LOOP;
             end loop;
--END LOOP;
END;

i know this code is not correct its only synchronizing id_card_number in text_field nothing else.

now in this table almost 5000 records and we have to choose one for them randomly means all reacords will be in loop/synchronize in text_field when we will click the button name will come out and will blink as winner.then rest of records will keep looping for 2nd winner. example code will be highly appreciated in both forms either oracle forms 6i or forms 10g.



plz need help little urgent..

Thanks in advance
Hina mansoor
0
Comment
Question by:hinamansoor
  • 13
  • 9
  • 7
  • +1
33 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39758019
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;
0
 

Author Comment

by:hinamansoor
ID: 39758762
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39759314
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.
0
 

Author Comment

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



Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39759964
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;

Open in new window

0
 

Author Comment

by:hinamansoor
ID: 39760076
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39760115
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.
0
 

Author Comment

by:hinamansoor
ID: 39760141
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39760160
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.
0
 

Author Comment

by:hinamansoor
ID: 39760194
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39760210
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?
0
 

Author Comment

by:hinamansoor
ID: 39760240
Exactly sir :-)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39760251
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.
0
 

Author Comment

by:hinamansoor
ID: 39760255
Thanks Sir for helped me a lot
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39760318
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39760327
>>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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39760370
Yes, a database view would be an equally-good way to solve this problem (and you had proposed that first).
0
 
LVL 20

Expert Comment

by:flow01
ID: 39760373
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
0
 

Author Comment

by:hinamansoor
ID: 39761866
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39761945
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39762030
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.
0
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39762862
I created a package balloting (zie attached files for testing)
In your form:

execute
balloting.init;  -- either in the when new_form_instance_trigger or in a when_button_pressed_trigger to start the balloting
in the same place create your repeating timer

create a when_timer_expired trigger

begin
   :your_show_item := balloting.show_next;
  -- if you use other timers as well get the timer name of the expired timer
  -- to execute the desired code for each timer
end;

attach a when_button_pressed trigger on your winner_button

begin
    :your_winner_item := balloting.show_next;
    :your_show_item := :your_winner_item;
   -- depending on what you want you can delete your timer here
end;

-- the creation and delete of the timer you will have to choose , showing new_values after a winner is selected or stopping it.
Depending on that choise you wil have to recreate the timer when starting the balloting again.

-- at the end you wil have to change the cursor in the package to use the table you want to use
balloting.pck.txt
test-balloting.sql
0
 
LVL 20

Expert Comment

by:flow01
ID: 39762874
delete the timer before you exit the form (timers are on application level and will continue to fire while a form session exists)
0
 

Author Comment

by:hinamansoor
ID: 39763186
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
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39763220
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.
0
 
LVL 20

Expert Comment

by:flow01
ID: 39763240
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.
0
 
LVL 20

Expert Comment

by:flow01
ID: 39763283
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);
0
 

Author Comment

by:hinamansoor
ID: 39763372
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
0
 
LVL 20

Expert Comment

by:flow01
ID: 39763497
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.
0
 

Author Comment

by:hinamansoor
ID: 39763559
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
0
 
LVL 20

Expert Comment

by:flow01
ID: 39763581
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)
0
 

Author Comment

by:hinamansoor
ID: 39763609
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
0
 

Author Comment

by:hinamansoor
ID: 39763632
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now