1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Function that returns random integers

Discussion in 'SQL PL/SQL' started by svidotto, Oct 4, 2013.

  1. svidotto

    svidotto Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    i'm making my first steps in oracle and plsql and i'm not able to make a function that returns random integers from 10 to 100.
    i have tried to write something like this but i have compilation errors, where is the problem?
    THX

    Code (Text):

    CREATE OR REPLACE FUNCTION random_int RETURN NUMBER;
    IS

    num NUMBER(3,0):=0;

    BEGIN

    SELECT TRUNC(dbms_random.value(10,100) INTO num FROM DUAL);
    RETURN num;

    END;
    /

     
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,
    There was syntax errors. so Try this.....


    CREATE OR REPLACE FUNCTION random_int RETURN NUMBER
    IS

    num NUMBER(3,0):=0;

    BEGIN

    SELECT TRUNC(dbms_random.value(10,100)) INTO num FROM DUAL;
    RETURN num;

    END;
     
    svidotto likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would have been nice had you actually explained the syntax errors rather than simply stating they existed.

    The first error in your original code was the ';' at the end of the CREATE FUNCTION line -- semicolons terminate lines of code and SQL Statements, not the initial CREATE TABLE line. That first semicolon creates the second error, the "misplaced" IS keyword. The third error is the ) after DUAL -- that should be after the (10,100) code to 'close off' the dbms_random.value() function call. The previous errors cause the last error, the unexpected 'end-of-file' from your code. The original code as you posted it:

    Code (SQL):

    CREATE OR REPLACE FUNCTION random_int RETURN NUMBER;IS num NUMBER(3,0):=0; BEGIN SELECT TRUNC(dbms_random.VALUE(10,100) INTO num FROM DUAL);RETURN num; END;/
    The code as it should be written, including changes to eliminate the errors discussed above:

    Code (SQL):
    CREATE OR REPLACE FUNCTION random_int RETURN NUMBER
    IS
     
    num NUMBER(3,0):=0;
     
    BEGIN
     
    SELECT TRUNC(dbms_random.VALUE(10,100)) INTO num FROM DUAL;
    RETURN num;
     
    END;
    /