OraFAQ Forum: SQL & PL/SQL » Stored Procedure using dbms

Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure using dbms_random.value (Oracle Database 11g Enterprise Edition Release 11.1.0.6.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Stored Procedure using dbms_random.value [message #400249]

Mon, 27 April 2009 05:14

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.

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400250 is a reply to message #400249]

Mon, 27 April 2009 05:35

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

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400266 is a reply to message #400249]

Mon, 27 April 2009 06:50

Frank
Messages: 7901
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.

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400291 is a reply to message #400249]

Mon, 27 April 2009 07:49

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400349 is a reply to message #400249]

Mon, 27 April 2009 13:51

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

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400350 is a reply to message #400249]

Mon, 27 April 2009 14:01

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400356 is a reply to message #400249]

Mon, 27 April 2009 15:43

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

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400361 is a reply to message #400249]

Mon, 27 April 2009 17:43

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400368 is a reply to message #400349]

Mon, 27 April 2009 22:24

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

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400837 is a reply to message #400368]

Wed, 29 April 2009 18:34

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

joicejohn wrote on Mon, 27 April 2009 20:24

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>

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #400838 is a reply to message #400356]

Wed, 29 April 2009 18:55

Barbara Boehmer
Messages: 9106
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>

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #401236 is a reply to message #400837]

Sun, 03 May 2009 01:57

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". /forum/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

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #401239 is a reply to message #401236]

Sun, 03 May 2009 02:36

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Stored Procedure using dbms_random.value [message #401241 is a reply to message #401239]

Sun, 03 May 2009 03:11

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

Michel,
That was a clever example. /forum/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

Report message to a moderator

Previous Topic:	Sub Queries
Next Topic:	SQL Operators - Datatypes allowed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 25 10:11:03 CDT 2025