44

I am new to Oracle DB and I am using Oracle SQL Developer (Ver 3.0.02) to query the DB. I wanted to explicitly set one column to null?

How do I do that in the SQL Developer GUI?

Previously in MSSQL, clicking CTRL+0 will explicitly set the value to null. How about Oracle SQL Developer? Thanks

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
Mark Estrada
  • 9,013
  • 37
  • 119
  • 186
  • I'm not sure I understand the question. Are you trying to add a column to a result set that is always NULL? Update a column in a table so that it is always NULL? Allow NULL values in a particular column? Something else? – Justin Cave Aug 22 '11 at 03:35
  • @Justin I want to explicitly set the column to null. Say I click a column and then sets its value to null explicitly. I am actually looking for a shortcut key for this. – Mark Estrada Aug 22 '11 at 03:43
  • I'm still not sure I understand. Is your intention to update a column in a table so that the value in every row is NULL? To modify the constraints on a table to allow NULL values in a column? Something else? – Justin Cave Aug 22 '11 at 04:44

4 Answers4

42

You'll have to write the SQL DML yourself explicitly. i.e.

UPDATE <table>
   SET <column> = NULL;

Once it has completed you'll need to commit your updates

commit;

If you only want to set certain records to NULL use a WHERE clause in your UPDATE statement.

As your original question is pretty vague I hope this covers what you want.

Ollie
  • 17,058
  • 7
  • 48
  • 59
21

If you want to use the GUI... click/double-click the table and select the Data tab. Click in the column value you want to set to (null). Select the value and delete it. Hit the commit button (green check-mark button). It should now be null.

enter image description here

More info here:

How to use the SQL Worksheet in SQL Developer to Insert, Update and Delete Data

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
13

Use Shift+Del.

More info: Shift+Del combination key set a field to null when you filled a field by a value and you changed your decision and you want to make it null. It is useful and I amazed from the other answers that give strange solutions.

Mostafa Vatanpour
  • 1,328
  • 13
  • 18
4

It is clear that most people who haven't used SQL Server Enterprise Manager don't understand the question (i.e. Justin Cave).

I came upon this post when I wanted to know the same thing.

Using SQL Server, when you are editing your data through the MS SQL Server GUI Tools, you can use a KEYBOARD SHORTCUT to insert a NULL rather than having just an EMPTY CELL, as they aren't the same thing. An empty cell can have a space in it, rather than being NULL, even if it is technically empty. The difference is when you intentionally WANT to put a NULL in a cell rather than a SPACE or to empty it and NOT using a SQL statement to do so.

So, the question really is, how do I put a NULL value in the cell INSTEAD of a space to empty the cell?

I think the answer is, that the way the Oracle Developer GUI works, is as Laniel indicated above, And THAT should be marked as the answer to this question.

Oracle Developer seems to default to NULL when you empty a cell the way the op is describing it.

Additionally, you can force Oracle Developer to change how your null cells look by changing the color of the background color to further demonstrate when a cell holds a null:

Tools->Preferences->Advanced->Display Null Using Background Color

or even the VALUE it shows when it's null:

Tools->Preferences->Advanced->Display Null Value As

Hope that helps in your transition.

Robert Bartlett
  • 121
  • 1
  • 6