2

I have seen that there is a NVL function for PL/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?

Improve this question

5 Answers 5

Reset to default
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.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

1

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

Improve this answer

Comments

0

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?

Improve this answer

Comments

0

The fact you are using an EXECUTE SQL statement, says that you are using Oracles Pro*C in a C or C++ program.

If the preprocessor is doing some translation on the NULL, then you are missing a piece of the compile procedure.

Prior to compilation, you need to run the source code though the Pro*C preprocessor. It translates the EXEC SQL statements, into a series of structures and function calls, that the c/c++ compiler can compile.

This link should help with some of the concepts.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.