CODEWITHSUNDEEP
oracle-databaseplsql
CalcGuy
CalcGuy

Reputation: 319

Error ORA-00923 after trying to generate random number in Oracle SQL Developer

The following procedure is not complete, but I am having trouble trying to generate a random integer between 1 and the size of a table. Any Ideas?

create or replace PROCEDURE AssignPlayersTMs AS

TYPE usertype IS TABLE OF TEAM_MANAGERS.ACCT_USERNAME%TYPE;
UserNM usertype;
TYPE usertype2 IS TABLE OF ROSTERS.PLAYER_ID%TYPE;
Play usertype2;
Rn INT;

BEGIN
    SELECT ACCT_USERNAME BULK COLLECT INTO UserNM FROM TEAM_MANAGERS;
    SELECT PLAYER_ID BULK COLLECT INTO Play FROM PLAYERS;


    for i in 1..UserNM.count
    loop
        SELECT FLOOR(RAND()*(Play.count-1)+1) INTO Rn;


    end loop;
END;

Compiler Error:

Error(17,9): PL/SQL: SQL Statement ignored
Error(17,54): PL/SQL: ORA-00923: FROM keyword not found where expected

Upvotes: 0

Views: 229

Answers (1)

user330315
user330315

Reputation:

A SELECT needs a FROM clause in SQL - just like the errors says. But you don't need a SELECT to assign a value to a variable. Just assign it:

Also, Oracle has no rand() function. You have to use dbms_random for that

create or replace PROCEDURE AssignPlayersTMs AS

  TYPE usertype IS TABLE OF TEAM_MANAGERS.ACCT_USERNAME%TYPE;
  UserNM usertype;
  TYPE usertype2 IS TABLE OF ROSTERS.PLAYER_ID%TYPE;
  Play usertype2;
  Rn INT;
BEGIN
    SELECT ACCT_USERNAME BULK COLLECT INTO UserNM FROM TEAM_MANAGERS;
    SELECT PLAYER_ID BULK COLLECT INTO Play FROM PLAYERS;

    for i in 1..UserNM.count
    loop
        rn := dbms.random.value(1, Play.count-1);
    end loop;
END;

Upvotes: 2

Related Questions