Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure using dbms_random.value (Oracle Database 11g Enterprise Edition Release 11.1.0.6.0)
Stored Procedure using dbms_random.value [message #400249] Mon, 27 April 2009 05:14 Go to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hey.

I am trying to randomly generate a competition winner. This is my Stored Procedure so far

create or replace PROCEDURE Generate_Comp_Winner_SP (var_compID IN NUMBER, varFirstName OUT Varchar2, varSecondName OUT Varchar2)AS
var_Answer COMPETITIONENTRY."Answer"%TYPE;
BEGIN

SELECT comp.Answer
INTO var_Answer 
FROM Competition comp
WHERE comp.CompetitionID = var_compID;

SELECT cust.FirstName,cust.Surname
( SELECT cust.FirstName,cust.Surname FROM Customer cust
ORDER BY dbms_random.value )
WHERE rownum = 1 
AND cust.CustomerID 
IN (Select compEntry.CustomerID
    FROM COMPETITIONENTRY compEntry
    WHERE compEntry.CompetitionID = var_compID
    AND compEntry.Answer = var_Answer);
END Generate_Comp_Winner_SP;


I get the answer from the appropriate competition then I want to get all customers who have got that competition correct and select one of them.

The errors are
Compilation failed,line 11 (11:06:22)
PL/SQL: ORA-00936: missing expression
Compilation failed,line 10 (11:06:22)
PL/SQL: SQL Statement ignored 


I think I am struggling with the selection of one person from list of winners.

Any help appreciated. Thanks.
Re: Stored Procedure using dbms_random.value [message #400250 is a reply to message #400249] Mon, 27 April 2009 05:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I don't see any FROM clause or INTO clause in your following SELECT query of procedure?
Does it compiles perfectly?

Quote:

SELECT cust.FirstName,cust.Surname
( SELECT cust.FirstName,cust.Surname FROM Customer cust
ORDER BY dbms_random.value )
WHERE rownum = 1
AND cust.CustomerID
...



regards,
Delna
Re: Stored Procedure using dbms_random.value [message #400266 is a reply to message #400249] Mon, 27 April 2009 06:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
[quote title=hooharhar wrote on Mon, 27 April 2009 12:14]
var_Answer COMPETITIONENTRY."Answer"%TYPE;
...
SELECT comp.Answer
INTO var_Answer 
FROM Competition comp
WHERE comp.CompetitionID = var_compID;

This is invalid: either you created a mixed-case column "Answer", in which case your query needs quotes, or you did not use mixed-case, in which case the quotes in the declaration need to go.

Advise: do NOT use mixed-case object names. You definitely will regret if you do.
Re: Stored Procedure using dbms_random.value [message #400291 is a reply to message #400249] Mon, 27 April 2009 07:49 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
As we have told you in every single post so far, when you create objects within double quotes, as Frank has said, you will regret it. You seem to have even admitted that in some previous post, but you continue to do it, so have fun trying to debug your code for non-syntactical errors. You're just digging yourself a deeper hole.
Re: Stored Procedure using dbms_random.value [message #400349 is a reply to message #400249] Mon, 27 April 2009 13:51 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hey, thanks for the replies,

I know using double quotes is bad im just not in the position to change it at the moment.

I changed my stored procedure to

create or replace PROCEDURE Generate_Comp_Winner_SP (var_compID IN NUMBER, varFirstName OUT Varchar2, varSecondName OUT Varchar2)
AS
var_Answer COMPETITIONENTRY."Answer"%TYPE;
BEGIN

SELECT comp."Answer"
INTO var_Answer 
FROM Competition comp
WHERE comp."CompetitionID" = var_compID;

SELECT cust."FirstName",cust."Surname" 
INTO varFirstName,varSecondName
FROM Customer cust
WHERE cust."CustomerID" 
IN (Select compEntry."CustomerID" 
    FROM COMPETITIONENTRY compEntry
    WHERE compEntry."CompetitionID" = var_compID
    AND compEntry."Answer" = var_Answer)
    ORDER BY dbms_random.value;
END Generate_Comp_Winner_SP;


this compiles and works but always returns the same person although there are definately other people it could select.

I think Im suppose to be following this syntax

SELECT column FROM
		 ( SELECT column FROM table
		   ORDER BY dbms_random.value )
    WHERE rownum = 1;


but when I do it I get an error

ORA-00907: missing right parenthesis


This is the stored procedure

create or replace PROCEDURE Generate_Comp_Winner_SP (var_compID IN NUMBER, varFirstName OUT Varchar2, varSecondName OUT Varchar2)
AS
var_Answer COMPETITIONENTRY."Answer"%TYPE;
BEGIN

SELECT comp."Answer"
INTO var_Answer 
FROM Competition comp
WHERE comp."CompetitionID" = var_compID;

SELECT cust."FirstName",cust."Surname" 
INTO varFirstName,varSecondName
FROM Customer cust
WHERE cust."CustomerID" 
IN (SELECT compEntry."CustomerID" 
    FROM COMPETITIONENTRY compEntry
    WHERE compEntry."CompetitionID" = 2
    AND compEntry."Answer" = 1888
    ORDER BY dbms_random.value)
WHERE ROWNUM = <1;


END Generate_Comp_Winner_SP;


Any ideas welcome.Thanks
Re: Stored Procedure using dbms_random.value [message #400350 is a reply to message #400249] Mon, 27 April 2009 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Stored Procedure using dbms_random.value [message #400356 is a reply to message #400249] Mon, 27 April 2009 15:43 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
I am attempting to get a list of people who have answered the given competition correctly and to select one of those people at random so they can be declared the winner. So my desired result from this stored procedure is one persons name.

Here is the SQL for the tables Competition, CompetitionEntry and Customer.

CREATE TABLE  "COMPETITION" 
   (	"CompetitionID" NUMBER(8,0), 
	"Description" VARCHAR2(500), 
	"Question" VARCHAR2(50) NOT NULL ENABLE, 
	"StartDate" DATE NOT NULL ENABLE, 
	"EndDate" DATE NOT NULL ENABLE, 
	"Winner" VARCHAR2(40), 
	"Answer" VARCHAR2(30) NOT NULL ENABLE, 
	 CONSTRAINT "CHK_STARTDATE" CHECK ("StartDate" <= "EndDate") ENABLE, 
	 CONSTRAINT "PK_COMPETITION" PRIMARY KEY ("CompetitionID") ENABLE
   )
/

CREATE TABLE  "COMPETITIONENTRY" 
   (	"CompetitionID" NUMBER(8,0), 
	"CustomerID" NUMBER(8,0), 
	"Answer" VARCHAR2(30), 
	 CONSTRAINT "PK_COMPETITIONENTRIES" PRIMARY KEY ("CompetitionID", "CustomerID") ENABLE
   )
/

CREATE TABLE  "CUSTOMER" 
   (	"CustomerID" NUMBER(8,0), 
	"FirstName" VARCHAR2(30) NOT NULL ENABLE, 
	"Surname" VARCHAR2(30) NOT NULL ENABLE, 
	"HouseNumber" VARCHAR2(5) NOT NULL ENABLE, 
	"Street" VARCHAR2(40) NOT NULL ENABLE, 
	"Town" VARCHAR2(20), 
	"City" VARCHAR2(20) NOT NULL ENABLE, 
	"County" VARCHAR2(20), 
	"Postcode" VARCHAR2(10) NOT NULL ENABLE, 
	"Country" VARCHAR2(20) NOT NULL ENABLE, 
	"HomeTelephone" VARCHAR2(15) NOT NULL ENABLE, 
	"MobileTelephone" VARCHAR2(11), 
	"WorkTelephone" VARCHAR2(15), 
	"Email" VARCHAR2(40) NOT NULL ENABLE,  
	 CONSTRAINT "PK_CUSTOMER" PRIMARY KEY ("CustomerID") ENABLE
   )
/


This is the stored procedure so far
create or replace PROCEDURE Generate_Comp_Winner_SP (var_compID IN NUMBER, varFirstName OUT Varchar2, varSecondName OUT Varchar2)
AS
var_Answer COMPETITIONENTRY."Answer"%TYPE;
BEGIN

SELECT comp."Answer"
INTO var_Answer 
FROM Competition comp
WHERE comp."CompetitionID" = var_compID;

SELECT cust."FirstName",cust."Surname" 
INTO varFirstName,varSecondName
FROM Customer cust
WHERE cust."CustomerID" 
IN (SELECT compEntry."CustomerID" 
    FROM COMPETITIONENTRY compEntry
    WHERE compEntry."CompetitionID" = 2
    AND compEntry."Answer" = 1888
    ORDER BY dbms_random.value)
WHERE ROWNUM = <1;
END Generate_Comp_Winner_SP;


But I get the error
ORA-00907: missing right parenthesis

Thanks
Re: Stored Procedure using dbms_random.value [message #400361 is a reply to message #400249] Mon, 27 April 2009 17:43 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>SELECT compEntry."CustomerID"
>FROM COMPETITIONENTRY compEntry
>ORDER BY dbms_random.value
HUH????
What do you expect this ORDER BY clause to do?
a valid ORDER BY would be
ORDER BY compEntry."CustomerID"

a valid ORDER BY would be
ORDER BY 1

DBMS_RANDOM.VALUE returns a number between 0 and 1
Re: Stored Procedure using dbms_random.value [message #400368 is a reply to message #400349] Mon, 27 April 2009 22:24 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@hooharhar,
hooharhar wrote on Tue, 28 April 2009 00:21


I think Im suppose to be following this syntax

SELECT column FROM
		 ( SELECT column FROM table
		   ORDER BY dbms_random.value )
    WHERE rownum = 1;




You are not following the mentioned syntax in your below codes.

Quote:

SELECT cust."FirstName",cust."Surname" 
INTO varFirstName,varSecondName
FROM Customer cust
WHERE cust."CustomerID" 
IN (SELECT compEntry."CustomerID" 
    FROM COMPETITIONENTRY compEntry
    WHERE compEntry."CompetitionID" = 2
    AND compEntry."Answer" = 1888
    ORDER BY dbms_random.value)
WHERE ROWNUM = <1;




Blackswan,
You are right in this point that DBMS_RANDOM.VALUE returns a number between 0 and 1. I tried a few tests on the sample DEPT table.
SQL> SELECT * FROM
  2  (
  3  SELECT a1.* FROM DEPT a1
  4  ORDER BY DBMS_RANDOM.VALUE)
  5  WHERE ROWNUM = 1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL> SELECT * FROM
  2  (
  3  SELECT a1.* FROM DEPT a1
  4  ORDER BY DBMS_RANDOM.VALUE)
  5  WHERE ROWNUM = 1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

SQL> SELECT * FROM
  2  (
  3  SELECT a1.* FROM DEPT a1
  4  ORDER BY DBMS_RANDOM.VALUE)
  5  WHERE ROWNUM = 1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Tom Kyte has considered order by dbms_random.value as "order by RANDOM_VALUE_ASSIGNED_TO_EACH_ROW".
Topic in AskTom .
I still didn't understand how this works. Maybe someone might be able to help.

Regards,
Jo
Re: Stored Procedure using dbms_random.value [message #400837 is a reply to message #400368] Wed, 29 April 2009 18:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
joicejohn wrote on Mon, 27 April 2009 20:24

Tom Kyte has considered order by dbms_random.value as "order by RANDOM_VALUE_ASSIGNED_TO_EACH_ROW".
Topic in AskTom .
I still didn't understand how this works. Maybe someone might be able to help.



As you figured out, the dbms_random.value generates a random value between 0 and 1 for each row. So, if you order by dbms_random.value, it is the same as ordering by that random value, so the rows are listed in random order. Sometimes it helps to look at the intermediary results to understand, so I have included the random values in a separte column below.

SCOTT@orcl_11g> SELECT a1.*,
  2  	    DBMS_RANDOM.VALUE
  3  FROM   dept a1
  4  ORDER  BY DBMS_RANDOM.VALUE
  5  /

    DEPTNO DNAME          LOC                VALUE
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK       .38729965
        30 SALES          CHICAGO       .551912624
        20 RESEARCH       DALLAS        .619382852
        40 OPERATIONS     BOSTON        .880354533

SCOTT@orcl_11g> /

    DEPTNO DNAME          LOC                VALUE
---------- -------------- ------------- ----------
        40 OPERATIONS     BOSTON        .505740407
        10 ACCOUNTING     NEW YORK      .549143964
        30 SALES          CHICAGO       .577659088
        20 RESEARCH       DALLAS        .859649599

SCOTT@orcl_11g> /

    DEPTNO DNAME          LOC                VALUE
---------- -------------- ------------- ----------
        20 RESEARCH       DALLAS        .153970326
        40 OPERATIONS     BOSTON        .414236313
        10 ACCOUNTING     NEW YORK      .547531027
        30 SALES          CHICAGO       .751749373

SCOTT@orcl_11g>

Re: Stored Procedure using dbms_random.value [message #400838 is a reply to message #400356] Wed, 29 April 2009 18:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
hooharhar,

"rownum = <1" is not valid syntax. It should be "rownum <= 1" or just "rownum = 1".

The number of left and right parenthese must match. You need to use a nested query / inline view of the following format:

select ...
into   ...
from   (select ...
        from   ...
        order  by dbms_random.value)
where  rownum <= 1;


In the above, the inner query orders the rows randomly and the outer query selects the first row of those randomly ordered rows into your variables.

Here is an example that you should be able to adapt to your situation. It randomly selects an employee name from a given department number.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE random_emp_from_dept
  2    (p_deptno IN  emp.deptno%TYPE,
  3  	p_ename  OUT emp.ename%TYPE)
  4  AS
  5  BEGIN
  6    SELECT ename
  7    INTO   p_ename
  8    FROM   (SELECT ename
  9  	       FROM   emp
 10  	       WHERE  deptno = p_deptno
 11  	       ORDER  BY dbms_random.value)
 12    WHERE  ROWNUM <= 1;
 13  END random_emp_from_dept;
 14  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE random_emp VARCHAR2(15)
SCOTT@orcl_11g> SET AUTOPRINT ON
SCOTT@orcl_11g> EXEC random_emp_from_dept (30, :random_emp)

PL/SQL procedure successfully completed.


RANDOM_EMP
--------------------------------
TURNER

SCOTT@orcl_11g> EXEC random_emp_from_dept (30, :random_emp)

PL/SQL procedure successfully completed.


RANDOM_EMP
--------------------------------
ALLEN

SCOTT@orcl_11g> EXEC random_emp_from_dept (30, :random_emp)

PL/SQL procedure successfully completed.


RANDOM_EMP
--------------------------------
MARTIN

SCOTT@orcl_11g> 








Re: Stored Procedure using dbms_random.value [message #401236 is a reply to message #400837] Sun, 03 May 2009 01:57 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Barbara,

Thanks a million. That neat example helped me understand the concept of "order by dbms_random.value". ./fa/1578/0/

But it also landed me in a whole different confusion.
Consider the following examples,

SQL*Plus: Release 11.1.0.6.0 - Production on Sun May 3 11:22:04 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: SCOTT
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT DBMS_RANDOM.VALUE Value_1,
  2  DBMS_RANDOM.VALUE Value_2
  3  FROM Dual;                                -- > (1)

   VALUE_1    VALUE_2
---------- ----------                
.330557515 .261918443

SQL> SELECT a1.*,
  2         DBMS_RANDOM.VALUE
  3    FROM   dept a1
  4    ORDER  BY DBMS_RANDOM.VALUE              --> (2)
  5   /

    DEPTNO DNAME          LOC                VALUE
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK      .012862073
        20 RESEARCH       DALLAS        .581150856
        30 SALES          CHICAGO       .832837741
        40 OPERATIONS     BOSTON        .892832902

SQL>



As you can see in example (1), each call to DBMS_RANDOM.VALUE generates different output in the same row.
What I didn't quiet understand why DBMS_RANDOM.VALUE in the ORDER BY Clause is getting the same magnitude of values as in the SELECT Statement(example (2)).

Regards,
Jo
Re: Stored Procedure using dbms_random.value [message #401239 is a reply to message #401236] Sun, 03 May 2009 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your second statement, optimizer knows the clause in "order by" is the same as the expression in last field. It is then the same as "order by 4".

If you slighty change the statement, you will see the second dbms_random.value coming into place:
SQL> SELECT a1.*,
  2         -1*DBMS_RANDOM.VALUE
  3  FROM   dept a1
  4  ORDER  BY DBMS_RANDOM.VALUE
  5  /
    DEPTNO DNAME          LOC           -1*DBMS_RANDOM.VALUE
---------- -------------- ------------- --------------------
        20 RESEARCH       DALLAS                  -.66084902
        40 OPERATIONS     BOSTON                  -.61633007
        30 SALES          CHICAGO                  -.5539121
        10 ACCOUNTING     NEW YORK                -.39988009

4 rows selected.

SQL> /
    DEPTNO DNAME          LOC           -1*DBMS_RANDOM.VALUE
---------- -------------- ------------- --------------------
        30 SALES          CHICAGO                 -.72597726
        20 RESEARCH       DALLAS                  -.27779268
        10 ACCOUNTING     NEW YORK                -.19282523
        40 OPERATIONS     BOSTON                  -.47275902

4 rows selected.

SQL> /
    DEPTNO DNAME          LOC           -1*DBMS_RANDOM.VALUE
---------- -------------- ------------- --------------------
        20 RESEARCH       DALLAS                  -.83883372
        40 OPERATIONS     BOSTON                  -.68396323
        30 SALES          CHICAGO                 -.43085447
        10 ACCOUNTING     NEW YORK                -.49278145

4 rows selected.

I made this on 10.2.0.4 maybe 11.1.0.7 optimizer is smarter and you have to use a more complex expression.

Regards
Michel
Re: Stored Procedure using dbms_random.value [message #401241 is a reply to message #401239] Sun, 03 May 2009 03:11 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel,
That was a clever example. ./fa/2115/0/ It works in 11.1.0.6.0 as well.
SQL> SELECT a1.*,
  2         DBMS_RANDOM.VALUE * -1, DBMS_RANDOM.VALUE
  3    FROM   dept a1
  4    ORDER  BY DBMS_RANDOM.VALUE
  5  /

    DEPTNO DNAME          LOC           DBMS_RANDOM.VALUE*-1      VALUE
---------- -------------- ------------- -------------------- ----------
        20 RESEARCH       DALLAS                  -.02701316 .315762246
        40 OPERATIONS     BOSTON                  -.64297376  .36531201
        10 ACCOUNTING     NEW YORK                -.13831977  .53806436
        30 SALES          CHICAGO                 -.02364187 .797400395

SQL>

Thanks & Regards,
Jo
Previous Topic: Sub Queries
Next Topic: SQL Operators - Datatypes allowed
Goto Forum:
  


Current Time: Sun Dec 04 02:33:49 CST 2016

Total time taken to generate the page: 0.12420 seconds