Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Assign result of PL/SQL function to DEFINE variable

592acd6d-75c8-4d67-be32-6da67672485dMay 22 2020 — edited May 22 2020

Hello,

I have written the following SQL function for generating a random UUID:

CREATE OR REPLACE FUNCTION GENERATE_UUID

   RETURN VARCHAR2

IS

   uuid VARCHAR2(255 BYTE);

BEGIN

uuid:= lower(dbms_random.string('X', 8) || '-' || dbms_random.string('X', 4) || '-' || dbms_random.string('X', 4) || '-' || dbms_random.string('X', 4) || '-' || dbms_random.string('X', 12));

RETURN uuid;

EXCEPTION

WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20501, 'An error was encountered! - '||SQLCODE||' -ERROR- '||SQLERRM);

END;

/

show errors

The thing that I must do, is to use the DEFINE keyword and assign a random UUID from the function call to a variable RANDOM_UUID. But none of the ways I tried work:

DEFINE RANDOM_UUID = GENERATE_UUID;

or

DEFINE MERCHANTID = EXEC GENERATE_UUID;

Your help is much appreciated! Thanks in advance!

Comments
Post Details
Added on May 22 2020
9 comments
2,087 views