P/SQL - setting null value

Question

Hi,

I have seen that there is a NVL function for P/SQL that substitutes a value when null is encountered. But what if I want to set a field to NULL, e.g. EXEC SQL UPDATE mytable SET myfield=NULL WHERE otherValue=1;

When I run this with C++ on HPUX, 0L is used for null while on Linux the statement fails with "illegal value".

Is there a generic Oracle null value/method I can use?

Thanks.

Answer 1

NULL is the correct value in Oracle. I have no experience in calling Oracle from C++ so I don't know what the cause of your issue is, but it must be an issue on the client side. If you were to run that statement through Oracle's SQLPlus it should do what you want.

Answer 2

Yes - the problem is the way NULL is expanded in C++. For informix there is rsetnull that sets the varialble to the appropriate value. Is there anything like this for oracle?

Answer 3

NULL should work as-is, but you could try with an indicator variable as explained here: http://download.oracle.com/docs/cd/B10500_01/appdev.920/a97269/pc_06sql.htm#424

Answer 4

Oh, now I realize what must be happening. The C++ system headers have #define NULL 0L (in HPUX) and so the hardcoded NULL is being replaced with that value by the preprocessor.

Several ideas:

• Simply change the case of NULL in the SQL statement. Oracle doesn't care about the case, and if it doesn't match the defined version exactly it won't get replaced. Of course they might have defined NULL, Null, null, etc. all to the same value.
• Find a way to disable the definition of NULL around that line of code. I assume there's a way to do this but I don't really know much about preprocessor directives
• Try using an empty string instead of the literal NULL. Oracle should convert it to a NULL.
• Store the text of your statement in a configuration file and read it into a string. This way it won't actually be in the code so the preprocessor won't modify it.
• Write a function in Oracle that simply returns NULL, and use that function in your statement instead of the NULL literal.

Answer 5

Solved it by using lowercase null as stated by several.

Thanks.