This forum is currently locked. You can't register or post questions at this time. (read more)

Need a Help in DBMS_Random Usage

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

mdsirajoddin
Member
Posts: 25
Joined: Mon Sep 09, 2013 4:30 am

Need a Help in DBMS_Random Usage

Postby mdsirajoddin » Thu Jun 05, 2014 10:09 am

Hi Tim,

Hope everything going fine with you..

I have a requirement and expecting you help in this regard, if you don't mind...
I read you page on DBMS_RANDOM, But lack of depth make me less to understand it.

I have a table :

Code: Select all

CREATE TABLE QUESTION_MASTER_TAB
(
  SNO           NUMBER(4),
  SUBJECT_ID    VARCHAR2(5 BYTE)         NOT NULL,
  QUESTION_ID   VARCHAR2(6 BYTE)        NOT NULL,
  QUESTION      VARCHAR2(1000 BYTE)    NOT NULL,
  OPTION1       VARCHAR2(500 BYTE),
  OPTION2       VARCHAR2(500 BYTE),
  OPTION3       VARCHAR2(500 BYTE),
  OPTION4       VARCHAR2(500 BYTE),
  OPTION5       VARCHAR2(500 BYTE),
  ANSWER        VARCHAR2(500 BYTE)     NOT NULL,
  TOUGH_LEVEL   NUMBER(1),
  UPDATED_BY    VARCHAR2(20 BYTE),
  LAST_UPDATED  DATE
-- CONSTRAINT FK_QMT FOREIGN KEY (SUBJECT_ID) REFERENCES SUBJECT_MASTER_TAB (SUBJECT_ID),
 CONSTRAINT PK_QMT PRIMARY KEY (QUESTION_ID);
  )


From this Table based on "QUESTION_ID" I have to select the questions.

I will insert the questions for each subject will be around 200, in that 20 Questions I need to give for each attendee, where they should not match with the questions in the same time with other attendees.

First of all in Each subject I need to select 20 Questions randomly, then after Next Subject 20 questions Randomly. Same like in this way I need to give 100 - 150 Questions per exam.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need a Help in DBMS_Random Usage

Postby Tim... » Thu Jun 05, 2014 10:59 am

Hi.

This is not a question about DBMS_RANDOM.

I have a question. What is the process that is selecting these questions? What I mean by that is,

- Is it a single process that runs and works out all the questions for all the exams, such that it is running as a single Oracle sessions?
or
- Is it like 20 different web page request, one for each student, so it could be serviced by several database sessions simultaneously?

Also

- What indicates that the request for a question is part of the same sitting. How would you know if it is from a separate class, so the same questions can be reused?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mdsirajoddin
Member
Posts: 25
Joined: Mon Sep 09, 2013 4:30 am

Re: Need a Help in DBMS_Random Usage

Postby mdsirajoddin » Tue Jun 10, 2014 11:14 am

Hi Tim,

Q : - Is it a single process that runs and works out all the questions for all the exams, such that it is running as a single Oracle sessions?
or
- Is it like 20 different web page request, one for each student, so it could be serviced by several database sessions simultaneously?

Ans : Yes, it is like 20 different web page request, one for each student, so it could be serviced by several database sessions simultaneously.

Also

Q: What indicates that the request for a question is part of the same sitting. How would you know if it is from a separate class, so the same questions can be reused?

Ans : It is for one organization, and may be more than one centers (various Locations in country). I will use Area_code to trace as per the Location. In Once Center mostly there will be 20-30 machines where this exam runs. I have more than 200 Questions per subject, so 20-30 users cannot get duplicate at the same time (if I am not wrong in this thought).

Subjects will be in sequence and the sequence is same for all. After completion of 20 Questions in Current Subject, It will start taking Next Subject.


Thanks in Advance..
Mohd Sirajoddin

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need a Help in DBMS_Random Usage

Postby Tim... » Tue Jun 10, 2014 12:26 pm

Hi.

1) Multiple separate web requests : In that case, you can not process the question requests in memory. You *must* do this in a database table, so the question allocation is perfect, no matter how many people attempt to sit a test and at what time.

2) End of of test marker : So you only have to worry based on the area, but this still doesn't indicate when a current sample is complete. For example, if you don't have some indicator that you need to start again, how do you know when that area is complete. Image I start in the morning and produce 20 exams, then in the afternoon they sit another 20 people. Should they be the same sitting, or a new one? How does the process know?

You basic method will probably be something like this:

1) Pull back the question IDs into a collection.
2) Select a question at random using DBMS_RANDOM. The random number generated is the index in the collection, so 1-200 for example. TRUNC(dbms_random.value(1,200))
3) Insert a record into a table with a PK or UK on area,question_id,sitting_id. Commit it.
4) If the record fails to insert it means someone in this sitting already had the question, so a new question will need to be selected. If it inserts, you've successfully selected a question for this test.
5) Got back to 2, until you have a full set of questions selected.

Note. For this to work, you *must* have some concept of sitting_id. Some way to indicate this is one unique sitting (group) of the exam.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mdsirajoddin
Member
Posts: 25
Joined: Mon Sep 09, 2013 4:30 am

Re: Need a Help in DBMS_Random Usage

Postby mdsirajoddin » Wed Jun 11, 2014 9:35 am

Hi Tim,

Thanks a lot for your time, patience & information.

Regarding Sitting_id, to be said frankly its not a set based exam, so that all 20-30 systems will get busy at a single point of time. It's concept is who ever come, will start the exam. So there is no exact time to start exam i.e., any time the exam can start, but to be completed with in 100 Minutes as the web page will contain timer.

Comment on 1).
We are tracing each and every click of each user in a Trace table with

I need a procedure based on this things, if possible.

Thanks in Advance.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need a Help in DBMS_Random Usage

Postby Tim... » Wed Jun 11, 2014 9:53 am

Hi.

I've given you the basic approach. I'm not paid to do your job for you. :)

Why don't you start trying to implement something, based on the process I suggested.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mdsirajoddin
Member
Posts: 25
Joined: Mon Sep 09, 2013 4:30 am

Re: Need a Help in DBMS_Random Usage

Postby mdsirajoddin » Wed Jun 11, 2014 10:48 am

Yes, Thanks for your support upto.. Thanks a lot.

I just asked formally, I am sorry if you think bad.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need a Help in DBMS_Random Usage

Postby Tim... » Wed Jun 11, 2014 10:50 am

Hi.

I'm not offended. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests

cron