0

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.

look4arnab
  • 3
  • 2
  • 1
    Googling around, it seems like the oracle function is actually `sys_guid()` https://stackoverflow.com/questions/3037462/how-to-generate-a-guid-in-oracle perhaps you are using a custom UDF that has been dropped or revoked. – Nick.Mc Aug 02 '21 at 08:11
  • `newid()` is actually T-SQL (SQL Server) – Nick.Mc Aug 02 '21 at 08:12
  • 1
    Oracle has no newid() function. Probably it was a custom function which was dropped or its usage was restricted. – Serg Aug 02 '21 at 08:13
  • I guess it was a copy paste from here https://www.programmersought.com/article/46825150787/ – Nick.Mc Aug 02 '21 at 08:13
  • Thanks all for the help..! I indeed found a custom function NewID in some of the Schemas. silly me. I have done a combination of `sys_guid()` with `REGEXP_REPLACE` to create '-' -separated Guids so I don't have to rely on the custom function. – look4arnab Aug 02 '21 at 10:08
  • Check [here](https://stackoverflow.com/a/36355563/4808122) for mass generation if case *performance* matters and you want to aviod *regexp*. – Marmite Bomber Aug 02 '21 at 11:25

1 Answers1

1

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • You're correct. I indeed managed to find a custom NewID function in some of the Schemas. I have done a combination of sys_guid() with REGEXP_REPLACE to create '-' -separated Guids so I don't have to rely on the custom function. Thanks for the help..! – look4arnab Aug 02 '21 at 10:15