Use function as default value for column in Oracle11g

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.

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.

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.

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") 
) 

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';