sqloracleora-00904newid

Trying to figure why NewID function stopped working on Oracle

I have used SELECT NEWID() FROM DUAL to generate new (random) guids in the past.
However, today when tried I am getting the below Error:

ORA-00904: "NEWID": invalid identifier

I am not able to find this Particular Error by googling. So I guessed the case must be any of the two:

  1. Either this has been somehow blocked by my System Admin or somehow the instance of Oracle is unable to find the Function due to some installation/ version update issues.
    -or-
  2. Oracle has stopped support for NEWID() and wants us to only use SYS_GUID() for Guid generation.
    (if yes, then I'll have to implement a REGEXP_REPLACE as GUIDs in my system are '-' -separated.
    Also - I'll have to update all existing codes that use NEWID()).

Any suggestion will be helpful. Thx.

Solution

  • Oracle has never had a built-in newID function. That is a function that exists in SQL Server so it is entirely possible that someone had previously created a custom newID function that you were accustomed to calling. Whether that function was just calling sys_guid under the covers or whether it was replicating the format of the GUIDs in SQL Server like this implementation is something you'd have to determine. Frequently, tools that help you migrate code from one database engine to another will install a library of functions that emulate the built-in functions of the source database engine in the target database in order to make migrations easier. So it is possible that the function you're accustomed to calling was installed by some migration tool.

    Since you talk about "version/ installation issues" my guess is that you are connected to a new/ different database that doesn't have the function you are accustomed to. If so, you can probably just go to the previous database where the code worked and copy the code for the custom function to the new database. If you are connected to the same database with the same user where this previously worked, that would imply that someone has revoked your user's access to the function or dropped the function entirely in which case you'd need to talk to your DBA/ DevOps team to see what changed and why.