Use function as default value for column in Oracle11g

Question

We are testing out Oracle at my work and im in charge of building all of the database objects (tables, procs, trigger, etc) on Oracle, and we currently use Microsoft SQL Server 2008 R2. We use uniqueidentifier's for almost all of our ID column's. I used this function to create GUID's:

CREATE OR REPLACE FUNCTION NEWID RETURN CHAR IS guid CHAR(36) ;
BEGIN
    SELECT SYS_GUID() INTO guid FROM DUAL;
    guid :=
               SUBSTR(guid,  1, 8) ||
        '-' || SUBSTR(guid,  9, 4) ||
        '-' || SUBSTR(guid, 13, 4) ||
        '-' || SUBSTR(guid, 17, 4) ||
        '-' || SUBSTR(guid, 21);
    RETURN guid;
END NEWID;
/

But now I cant figure out how to use it as the default value on columns when creating tables. Here is a non-working example:

CREATE TABLE "NonWorkingExample"
(   
  "ID"                              CHAR(36)      NOT NULL DEFAULT   NEWID(),
  "UnitNumber"                      NUMBER(38)    NOT NULL,
  "StartDateTime"                   TIMESTAMP     NOT NULL,
  "EndDateTime"                     TIMESTAMP     NULL,

  CONSTRAINT PK_RentalAgreements  PRIMARY KEY ("ID")
);

And the error:

Error starting at line 1 in command:
CREATE TABLE "NonWorkingExample"
(   
  "ID"                          CHAR(36)      NOT NULL DEFAULT NEWID(),
  "UnitNumber"                  NUMBER(38)    NOT NULL,
  "StartDateTime"               TIMESTAMP     NOT NULL,
  "EndDateTime"                 TIMESTAMP     NULL,

  CONSTRAINT PK_RentalAgreements  PRIMARY KEY ("ID")
)
Error at Command Line:3 Column:58
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

Any help would be much appreciated. Thank you.

Answer 1

I completely agree with the other answers that if you really want to call a function, you'll need to use a trigger or you'll need to embed the function call in the INSERT statement itself. Embedding the function call in the INSERT statement is more efficient than forcing Oracle to execute a trigger for every row that you insert.

I should point out, though, that you can use SYS_GUID() alone as the default value for a column without worrying about a trigger

SQL> create table foo (
  2    col1 varchar2(32) default sys_guid(),
  3    col2 number
  4  );

Table created.

SQL> insert into foo( col2 ) values( 1 );

1 row created.

SQL> select * from foo;

COL1                                   COL2
-------------------------------- ----------
7B64E8AE7404421C80A590F65873CD79          1

Do you really need the extra dashes in your GUID values? Could you potentially add the dashes only when you display the data? Or, since you're on 11g, add a function-based virtual column that converts the dash-free GUID to a GUID in the format that you prefer?

And since you're coming from SQL Server, I should point out that it would be conventional in Oracle to use a sequence to populate synthetic primary keys rather than using a GUID. Using a sequence to populate the key would generally be more efficient than calling SYS_GUID.

Answer 2

Suggestion:

• Use triggers instead.

For example:

CREATE TRIGGER SetGUIDforTableXYZ BEFORE INSERT ON TableXYZ
FOR EACH ROW
BEGIN
    :new.ID := NEWID();
END;

That should do the trick (assuming I haven't messed up the syntax somewhere).

Answer 3

As @Kerri implied, you cannot use a PLSQL function as a default value in a table definition. The relevant statement in the Oracle documentation is "A DEFAULT expression cannot contain references to PL/SQL functions...".

In addition to being the only way to insert a formatted GUID, as you're attempting to do, a trigger provides a second advantage: unlike with a default value, the value set by the trigger cannot be overridden by a careless developer.

As an aside, you should really revise your newid function to use direct assignment rather than select ... from dual:

CREATE OR REPLACE FUNCTION NEWID RETURN CHAR IS 
    guid VARCHAR(36);
BEGIN
    guid := SYS_GUID();
    guid :=
               SUBSTR(guid,  1, 8) ||
        '-' || SUBSTR(guid,  9, 4) ||
        '-' || SUBSTR(guid, 13, 4) ||
        '-' || SUBSTR(guid, 17, 4) ||
        '-' || SUBSTR(guid, 21);
    RETURN guid;
END NEWID;
/

Finally, I would advise you to use varchar2 instead of char. Contrary to popular opinion, there is no storage or efficiency advantage to char, so you might as well use varchar2 for everything, just for simplicity's sake.

Answer 4

You can't use a function for this. You don't see it because your syntax is wrong (DEFAULT before CONSTRAINTS). You should have:

"ID"    CHAR(36)  DEFAULT NEWID()   NOT NULL ,

at which point you would receive the following message (11g):

SQL> create table tt (id varchar2(36) default newid() not null);
create table tt (id varchar2(36) default newid() not null)
                                   *
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here


SQL>

As @Kerri states, it'll take a trigger to do this automatically.

Answer 5

How about removing the function and trigger dependencies by trying something like this:

CREATE TABLE "NonWorkingExample" 
(    
  "ID"                          CHAR(36)      NOT NULL DEFAULT regexp_replace((rawtohex(sys_guid()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '\1-\2-\3-\4-\5'), 
  "UnitNumber"                  NUMBER(38)    NOT NULL, 
  "StartDateTime"               TIMESTAMP     NOT NULL, 
  "EndDateTime"                 TIMESTAMP     NULL, 

  CONSTRAINT PK_RentalAgreements  PRIMARY KEY ("ID") 
) 

Answer 6

If Oracle SYS_GUID()'s somewhat similar/sequential GUIDs bother you, you can wrap java.util.UUID.randomUUID() in a pl/sql function to get less predictable values:

CREATE OR REPLACE FUNCTION Java_Util_Random_UUID RETURN VARCHAR2 AS
language java
name 'java.util.UUID.randomUUID() return String';