Search code examples
sqloraclecoalesceisnull

How to convert this script to an Oracle

I have to create a script that would be executed both in SQL Server and Oracle databases. The script is:

update my_view
set value = 'Approved'
where token_name = 'TOKEN_NAME'
and isnull(value,'') = '

I have never run a script in Oracle but I know from the internet that Oracle doesn't have isnull(it is NVL) or empty string.

Is there any way I can create just one script for both(like using COALESCE- which I don't really know how to use)?If not what would be the equivalent script in Oracle?

Solution

  • A standard way to write the logic is:

    update my_view
    set value = 'Approved'
    where token_name = 'TOKEN_NAME' and
          (value is null or value = '');

    This is standard SQL and should work in any database.

    On the other hand, updating views is not necessarily supported in all databases.