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.
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.
Suggestion:
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With